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

    efficient count calculations

    ericjlindholm

      Title

      efficient count calculations

      Post

           I have a table with master inventory items with a record "Microphone A".   There is a portal to a table of sub inventory Items that lists the serial numbers and barcodes of all of the Microphone As I own.  I have a status field in the sub items table.  I want to display a count of sub items with a specific status such as "Active".  What is the most efficient way to do this?  Thank you

        • 1. Re: efficient count calculations
          philmodjunk

               There is no one "best way".

               The Sum function can display a total of items in the portal. If you need a selective total (only "active" items), you can define a calculation field with an If function that is empty when the value in status is not "active" and returns the value to be summed if it is. Then Sum can sum the calculation field to provide such a selective total.

               This is probably your best option if you will be editing portal fields that then change the total returned for this sum.

               But a summary field that summarizes the same calculation field, defined in the portal's table, could also be placed on your layout to show the total.

               Or you can get rid of the If calculation and use a one row filtered portal that filters for "active" records to display the total active record.

               And in FileMaker 12/13 you can use ExecuteSQL to compute this total using a WHERE clause to filter the total records down to just those that are active.