Calculating a Grand Total

[Tony Moss]

Help! This should be so straightforward, but I’m getting nowhere. Here are details of my TEST database:-

I’ve a Form called “Item” with three number fields (“One”, “Two” and “Three”) and a Calculation field (“Total”) with a simple “SUM(One, Two, Three)” formula. Four records, all working well.

I want a single field to show the “Grand Total” – the sum of the four “Total” fields. This is in anticipation of future inclusion in a separate Form such as an Invoice or Receipt, etc..

I’ve added a second Form called “Summary” with a one-to-many “Link to Form” field linked to the Item Form, and created a second field – a Calculation field – with the formula “TOTAL( Link to Item::Total)”. This formula was inserted automatically.

However, this “Grand Total” field in the single record in the Summary Form shows nothing but “0” (zero).

I can’t see anything wrong with the logic of the above – though there obviously is, so:

a) what am I doing wrong and/or
b) what am I not doing?

I’m feeling like a right numpty not being able to master such a basic task, so any help greatly appreciated!

Thanks in anticipation,
Tony

[Brendan]

Hi Tony,

Hmm… That all sounds fine to me. Would you be able to email me your form template so I can look at it exactly to see what you’ve done?

Also, you should probably use semi-colons instead of commas within the function parameters. It’s more compatible with European number systems that way. You also can use + instead of SUM(). But that’s up to you. The result should be the same.

Thanks,

Brendan

[Tony Moss]

Dear Brendan,

Thanks, as always, for your immediate reply. Nice to hear that I may not have lost my touch!

Attached are the two Form Templates (I hope these are the files you need).

It’s probably a really trivial and elementary mistake on my part, but it’s all part of the learning curve.

Thanks,

Tony

Attachments:

You must be logged in to view attached files.

[Brendan]

Hi Tony,

Your form seems to be working. See the attached image.

So I’m not sure why it’ snot working for you.

Attachments:

You must be logged in to view attached files.

[Tony Moss]

Hi Brendan,

Thanks for your screenshot – really useful, since it’s enabled me to make a little progress.

I’ve found that the Grand Total calculation does work for me – and it doesn’t!

It only works for data entered into the “Link to Item” table on my Summary Form (as I suspect it did for you, looking at the record IDs). Any records previously or subsequently added/amended to the linked Item Form are not included within this table!

My concept is to have a single, user-friendly, Form/layout for data entry (my “Item” Form) with an ergonomic layout, notes and instructions etc., and a separate Form for calculating the Grand Total (and maybe including additional data from other Forms) and displaying this in a sensible layout such as a letter or Receipt. (Now I’ve got a Grand Total figure to work with, I’ve tried this with a new layout on the Summary Form and it works beautifully.)

As things stand, it seems that the only way to get (in this case the Grand Total) is to use the basic and “unfriendly” table on the Summary Form to collect all the data. Unless, that is, I can get a layout on the Summary Form which includes the required fields from the Item Form – but I can’t! Or can I?

Any further advice gratefully received, and thanks once again.

Tony

[Brendan]

To have Tap Forms recalculate all the values for all records, just click the refresh button at the bottom of the records list view. That should do it.

I’m not quite sure what you mean by “unfriendly”. Are you saying that the tabular Link to Form view is not easy to use? If there’s any improvements you can think of for that control, please let me know. I’m always looking for ways to make things better.

Thanks!

Brendan

[Tony Moss]

Hi again,

I’ve tried the “Refresh” button periodically throughout this exercise, on both Forms, but have to report it has no effect whatsoever. New records added to the Item Form do not appear in the Summary Form list after pressing.

I have found a way to include any previous and subsequently-added records from the Item Form. However, this has to be done manually with the use of the “Select existing linked records” button. I’ve attached five screenshots showing the effects. At the very least the Refresh button should do all this, but ideally the Summary Form should update automatically.

As for my use of the word “unfriendly”, it came from my history of some 20 years teaching in high schools (from waaaay before computers made an appearance in education), followed by a further 20-odd years supporting admin staff in all the County’s 347 schools with the use of a Management Information System for students and staff records. Then, on retirement, I invested in FileMaker Pro and designed a stock and customer record database for my sister’s shop (selling drums and everything that goes with them).

My primary focus is the user – and the interface should always be attractive, clear, easy to use and easy to understand as to what information is required. While these days many users will be familiar with spreadsheet -style tables, there will be some who are not. The aim is for any new user (computer literate or not) of a simple system to be able to use it virtually immediately, with little or no explanation required. (e.g. Screenshot One.)

Hope this helps, and apologies for taking up so much of your time,

Tony

Attachments:

You must be logged in to view attached files.

[Brendan]

Hi Tony,

Ah ok. I understand the issue now. Yes, the refresh button will not automatically link your records together. If you want automatic linking of records, then you need to use the Join Link Type option on the Link to Form field. Then you need to have matching fields in your Summary and Item forms that have the same values. Then Tap Forms can auto-link the records together. If you’re using a regular one-to-many or many-to-many, then the linking is done manually.

So my apologies for the confusion with suggesting the refresh button would help. I thought it was just that the Grand Total wasn’t updating properly when you added linked records.

Thanks,

Brendan

[Tony Moss]

Thanks Brendan, that does it.

Since I only want one record in the Summary Form I’ve added a number field containing “1” and found a way of automatically adding an identical field, with contents, on each new record in the Item Form when created. This allows them to be immediately visible in the Summary table, though it’s still necessary to click the Refresh button to update the Grand Total.

Thanks once again – outstanding support.

Tony

[Author]

Posts