If you look up aggregate functions like Sum and Average, you'll find that they do not work in the context that you are using. They calculate an aggregate value for:
Multiple values in a list as the parameter: Sum ( 1 ; 2 ; 3 ) ----> 6
Multiple values in the repetitions of a single repeating field: Sum ( repeatingField )
Multiple values from a related table: Sum ( RelatedTable::Field )
I suggest using a summary field to compute this value instead. it will return an aggregate value based on the current found set and that seems to match the scenario that you describe here.
2 of 2 people found this helpful
The usage of the ExecuteSQL() for some aggregates may help if you need to get at data without the need for context. You cannot calculate "sum(thisTable::thisField)", but you can "sum(relatedTable::thatField)", so with ExecuteSQL(), it does not need to be a relationship.
The caveat is that ExecuteSQL() does not work on the "found set" of records (remember? no context). Whereas Summary fields do work on the "found set" of records and the function:
GetSummary() function can be leveraged to set a variable or field:
Thank you - although the suggestion of using Summary Fields does work - I would rather used the ExecuteSQL() approach.
1 of 1 people found this helpful
Due to the fact that FileMaker's DB engine doesn't directly use SQL, ExecuteSQL can be slow so test your solution with a set of records that accurately reflects what your DB will actually have when these values are computed.
Sent from my iPhone