There are at least 3 ways to accomplish what you're after: The easy way that forces you to change some of what you have, a slow-performing way that preserves what you have, and a faster-performing way that preserves what you have, but requires you to learn some SQL.
The easy way would be to base your report on your Financials table. Use subsummary parts, sorted by the key to the Main table, without Body parts, to produce the totals by group. At the bottom, simply put your summary field, and you're done. The only hard part is changing your report so it's based on the Financials table instead of the Main table.
The slow-performing way to do it from the Main table involves the use of calculation fields and the Sum function. In this case, you put a calculation in your Main table that reads, "Sum ( financials::amount )" and a Summary field of that. This will give you your totals in the Main table. Problem is, as your data load grows, it's going to be deadly slow.
The third involves using an ExecuteSQL function to pull your data. You can use the SQL SUM function to pull your totals and display them from pretty much anyplace you want. It'll be substantially faster than option 2 (which I don't really recommend), but you'll need to learn a little SQL to make it work.
Let us know which one sounds good, and I'll go into more detail if you need it.
Good Morning Mike,
Thank you VERY much for your response. I actually created Option 1, but my users did not like that particuliar view and insisted on a view similiar to my attachement.
For Option 2, I tried that, but the Summary field of the Sum, still only shows the sum of what ever related record I'm on, and not the sum of all sums. This is the solution I have been working on, but to no avail. I think at least in this stage, this solution would work temporarily. Data capacity would not be excessive for a year or so.
Option 3, this option seems the best. Using this method, would it show the summary of all the groupings in the list dynamically ?? I would like to solve this issue the correct way for the long term, and it sounds like this is the best way
What exactly was the problem with Option 1 (subsummary parts)? I've attached an example. (See the "Report Copy" layout.)
In order to use ExecuteSQL, what you'd need to do is write your queries for each Main record to sum up the amounts, then another query for your overall sum. Here's an example for your individual records:
ExecuteSQL ( "SELECT SUM ( financials.amount ) FROM financials WHERE financials.\"_kf Main ID\" = ?" ; " " ; " " ; _kf Main ID )
Make that a field in your Main table.
Then, for your overall total, you can use:
ExecuteSQL ( "SELECT SUM ( financials.amount ) FROM financials ; " " ; " " )
You can either make that a global calculation in the Main table, or use a script to insert it into a merge variable when you go to that layout.
Sum Issue.fmp12.zip 28.8 K
I would like to solve this issue the correct way for the long term
That would be option #1 above.