1 of 1 people found this helpful
You can use calculations in the Parent table (Customer) to return Sums and Counts, such as
Count (Line Items::foreignKeyField)
to return counts of records in related tables.
You have to be a bit creative with placing time ranges on the relationships to get summaries of amounts within a period, but the methodology is the same using calculation fields and the Count or Sum functions in FM's calculation engine.
Ok that helps. Could I count up the total dollar figure of all the line items in my grandchild table from the customer table?
Yes, using a calc field in the Customer table:
Sum (Line Items::TotalDollarField)
That one won't break it down by Room record, but will give a single value from the target related table.
Nice, so I guess that just leaves me to figure out how I can be creative with my filtering of dates as you mentioned. Would I be looking at using something like an IF block to filter out results or is there some way to omit records being included depending on a date field within the record?
You could use global fields in the parent record as PART of the actual relationship definitions to the grandchild record's date, and then the SUM calculation field will actually update as you change the globals.
If you use filtering at a portal level, where it's not really part of the relationship definition, the calc'd SUM function won't update based on filters not built in to the relationship definition.
Add relationship conditions for RecordDate ≥ globalStartDate
and RecordDate ≤ globalEndDate
along with the basic key fields.
You may need to setup a special relationship just for this calculation.
You could also use a Get SQL calc result for the Sum of records with the correct foreignKey and the date ≤ and date ≥ (as above) without having to set up a special relationship, if you're willing to tackle the SQL option, which avoids having to modify the relationship graph at all.
Thanks I think this should get me started.