Please can you advise, I have a subsummary report sorted by Work order number.
I am using a portal to summarise the total of the costs, yet the sub total box shows only the last cost, and not a total.
You can create a calculation field that Sum()s the cost of the related records – Sum ( Instruction Tasks::Combined Cost ) - or create a regular number field and use a script with the same formula:
Set Field [ WorkOrder::totalInstructionCombinedCosts ; Sum ( Instruction Tasks::Combined Cost ) ]
(if I got your tables right; but the principle remains the same, regardless: use Sum() to get a total of a field in all related records.)
The first approach needs less maintenance, but the result must be re-calculated every time you use/display that field. The second approach gives you a field that can be stored and will improve performance.
Imagine a list of work orders with a grand total field that (among others) uses the calc field for the work order total (and therefore must itself be unstored); while you're scrolling down the list, you can watch FileMaker calculate the correct figure(s) on the fly ... not so with a stored field.
Please post a screen shot of your layout showing the summary part and the summary field (total) in that part.
Thank you for replying, please see attachment.
What is the field in the body part named?
What is the field in the sub-summary part named? Is it a summary field (total)?
(You have showed sample data in the layout mode and not the actual field names.)
1. You have a sub-summary part, but is that field in there really a summary field?
2. Where is the portal you mentioned, and why would you need one?
Hello Beverly ...
Good Morning, erolst!
The screen shot does not show "portal" (but could be!), eh?
If it is a portal then the sub-summary part is not the correct usage.
beverly wrote:Good Morning, erolst!
Not exactly ... it is already 2PM here. 1400, as we say
Let's hear what the OP can say in their defense re portal
Apologies for mentioning the portal, it was present as i wrote the post, i removed it to try something else, but I neglected to edit my post.
Hopefully the new attachments will shed some more light.
See the double colon ( :: ) in the field names? That indicates that these are related fields (remnants from your portal experiments?).
Make sure you use fields from the same table occurrence (TO) as the one that the layout is based on. You can see which one that is by looking next to the Layout popup in the small bar below the tool bar, or by opening the Layout Setup dialog.
Double-click the field object, and from the popup-menu in the Specify Field dialog that follows, select the correct TO.
Thank you, that works. However I can't do that as it lists everything in that table. I need to create a sub total in this layout to reference those costs. Am I able to copy them across instead?
Well, in that case you need to either find the sub-set of records to total, get the value, go to wherever you need that result ... or create a calc field that totals those records as related records ... or use a regular field with a script ... or ...
Better explain the big picture, because that question could be answered in "oh so many ways"
Basically its a work order system.
The "Schedule of rates" table has all the Items and related costings.
I have a separate table that lists the tasks to each different work order number, called "Instruction Tasks", would i need to "copy" over the costs into "Instruction Tasks" on a per instruction basis in order to calculate the total.
The summary (total) field in the same table, as the data it summarizes, is based on:
location, location, location
A total in the footer will be all found records, for example. In the body will be just that record.
What field is used to make the sub-summary part and are you sorting on that ?
Is the field entirely inside the correct layout part?
Sent from miPhone
Thank you very much erolst.
Retrieving data ...