1 Reply Latest reply on Jan 8, 2014 9:06 AM by philmodjunk

    Summary Calculation help

    ChadGist

      Title

      Summary Calculation help

      Post

           Hello, I am trying to create a summary field that calculates the sum of another field, but constrains the data to a particular found set based off a match with another field. 

           For example, I want to calculate a new total from the ProductCostSummary, but limit that calculation to products whose FactoryField value = "Factory22"

           I know this is probably really simple, but I am just getting started with learning how to use calculations. Thanks for the help!

            

        • 1. Re: Summary Calculation help
          philmodjunk
               

                    but constrains the data to a particular found set based off a match with another field.

               Picky detail: that's not actually a found set of records. I refer to it as a "related set" of records.

               There are multiple options for what you want and which to use depends on the design of your database and your report layout.

               To get a total from a related set of records, you can use:

               1) Sum (RelatedTable::FIeld ) to get the total of Field from the set of all related records. This calculation has to be evaluated from the context of a table that links to these records in a relationship.

               2) Define a summary field in RelatedTable that computes the total of Field. If you refer to this summary field from the context of a different related table (such as putting it on the layout based on the other table), it will show the same sub total as 1.

               3) You can also use a more general relationship and put the summary field from 2 inside a one row filtered portal. This "display only" total will show the total of the related records that pass the filter expression. Thus you can use one relationship but with a series of different filtered portals to get different subtotals of your related records. These totals, however, cannot be accessed by scripts or other calculations so this sometimes cannot be used.

               4) In FileMaker 12 or newer, the ExecuteSQL function can return such sub totals. See: FMP 12 Tip: Summary Recaps (Portal Subtotals)

               And if you truly want to get subtotals from a found set of records on your layout, you can put a summary field inside a sub summary layout part: Creating Filemaker Pro summary reports--Tutorial