### Title

Summaries and Calculations based on Non-Current Found Records

### Post

Hello,

I have a layout that has an area in the grand trailing summary that is summing up the values entered into the layout for a few specific fields.

A. For 6 fields - each field is counting the occurrence of a unique value (category type) that shows up in a "category" field. (The same "category" field has an option to display 6 different values or categories)

B. There are also 6 other fields that each calculate the sum entered for the cost associated to the category field mentioned above. So if Category type A is entered in twice, at a price of $1,000 and $2,000. The total in the grand trailing summary would be $3,000.

Right now, these calculations - both summaries and sums - are only counting the values in a current find.

The problem is that I send out a daily report just for that day's "category" occurences and associated costs. So everyday I do a find for the date, it brings up the values, and that is the only data in the body that goes in the report.

But in the grand trailing summary, I need to report the "To Date" category occurrences and costs. Therefore counting all of the records ever entered, even though I am not currently showing them in body.

Is there a formula that isn't based on a current find?

Sorry for the long winded explanation. Hopefully it made sense to someone. Thanks for your help in advance.

I'm on FMP 11.

It's not fully clear exactly what you are doing here, but to provide a general answer that may give you the info you need to make this work...

You can most easily compute totals for groups of records in two ways:

1) the current found set--this is what you have working now.

2) A set of related records--which can be all the records in the table and can even include the same records from the same data source table as those in your current found set. A "self join" like this is one possibility:

YourTable::anyfield x YourTable 2::anyfield

You use the button with two green plus signs to duplicate the table occurrene box in Manage | Database | Relationshps to create "yourTable 2" and you then drag from any field in YourTable to any field in YourTable 2, then double click the relationship line where you change the = to x to get a cartesian join operator.

With that set up, aggregrate functions like sum, count, average can be set up as Sum ( YourTable 2::Numberfield ) to get totals for the set of related records--every record in your table in this case. You can also refer to summary fields defined in your table, but from the YourTable 2 table occurrence instead of YourTable to get a total for the complete set of records in the table.

And there are other relationships and/or filtered portals you can set up to get sub totals for all records in your table for a given category.