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

    Summaries and Calculations based on Non-Current Found Records



      Summaries and Calculations based on Non-Current Found Records



      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.