1 Reply Latest reply on Apr 30, 2012 2:16 PM by philmodjunk

# Summaries and Calculations based on Non-Current Found Records

### 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.

• ###### 1. Re: Summaries and Calculations based on Non-Current Found Records

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.