Judging from your screenshot, you need a calc field like
GetSummary ( totalNet ; flat ) / GetSummary ( totalNet ; totalNet ) * 100
where flat is the break field of your sub-summary part
I've tried that but GetSummary is a calculation and won't display in the grand total sum
I realise I didn't understand your goal. Maybe explain it again - and guess a screenshot with markers might help., and the value you are expecting to see.
Speaking of screenshots: you can embed images inline, rather than as attachments. That makes it usually easier to see them and read the text simultaneously.
I'm guessing that you need to "atomize" your percentage value. For example, each line item (the stuff in the Body part) would have a calculation like Dollars / GetSummary(SumDollars; SumDollars). Then, you'd create a summary field that is the total of these "atoms". This summary should evaluate properly in both sub and grand summaries.
a bit more ...
based on the model above, the grand summary of the atomized percentages should be 1 (or 100%).
Here's the embedded image of my attached sample FileMaker database.
David, the grand summary answer in my example is the percentage gain/loss from all purchases, ,cost, sale and income divided by purchase. In this example that would be (-300'000 -20'000+255'000+40'000)/-300'000*-1=-25'000/-300'000*-1=-8.3%
Also, the sub summary part is shown when sorted by flat (i.e. A1 or B2)
I think the answer lies in creating a field in the body that is a Getsummary of total Accounting divided by "something".
Thanks for any help.
How did get at the individual summarised figures in the "bottom line"?
Do you have fields like
cSale = Case ( type = "Sale" ; amount )
sSale = Total of: cSale
(Repeat for the three other types)
I do this by creating a calculation field that does your summaries and I usually do this with SQL. To do this, you need to know the found set of records and you can do this with a Summary Field that Lists all of the primary keys of the found set. From there you use the SQL to get the summary. It might be something like this:
Create Field "__pKey_s" that is Summary if the primary key field, say "__pKey" that lists those values.
Then do a calculation summarizing the found set:
Let ( [
F1 = TableName::__pKey_s ;
F2 = Substitute ( F1 ; ¶ ; ", " ) ;
F3 = "SELECT
SUM ( Purchase )
\"__pKey\" IN ( " & F2 & " )" ;
F4 = If ( Not IsEmpty ( F1 ) ; ExecuteSQL ( F3 ; ¶ ; ¶ ) ) ;
F5 = GetAsNumber ( F4 )
] ; F5 )
Note that F2 assumes the primary key field is a number field. If it is a text field (say UUID), then you have to have single quotes around each result such as:
F2 = "'" & Substitute ( F1 ; ¶ ; "', '" ) & "'" ;
After you have created a calculation field that does each of these summaries, then you create another calculation to do the equation you needed of the summary fields.
Awesome, thank you so much!
I come to the 'sad' conclusion that I'll have to teach myself SQL after all!
...or I'll contract you!
You've made my day!
SQL is a great tool. It can be very complicated, but it doesn't have to start that way. Just learning some basic SELECT statements will get you a long ways.
TMS would be happy to contract with you. We are located in Texas. But the odds are that you are not in Texas too. Finding a consultant that is close to you and able to meet with you is often a better business relationship. You can always go to FileMaker Consultants, Data Consultants, Database Consultants and search for a developer near to you. But if you don't find one you like, we would be glad to help out with your development work remotely.
Thanks for the hint. I guess it'll be much easier to work with someone in driving distance.
I've updated my 'real' file with your code and everything works great, with the exception that the SumNetPct_c field now has to average 3'000 records, which makes it almost impossible to use (I've aborted after a 5' wait).
Is there any trick that you could think of to speed up this process? The field needs to be recalculated for every group I search for or for all records (and the records will double in the next weeks).
Thanks for all your help!
PS: I have a MacPro (late 2013) with 16GB Ram, so I don't think it's the machine.