2 Replies Latest reply on May 26, 2014 2:21 PM by mleering

    SumIf function?



      SumIf function?


           Hello there,

           I am trying to write a Sum If formula so that my inventory table reflect how many items of each products I have depending on the status (Ordered, In Transit, In stock etc). The calculation is based on a Purchase Order Line Items table where

           Inventory>-----Products----<Purchase Order Line Items

           Basically I want the calculation to sum the total submitted quantity field of the Purchase Order Line Items tables where the record shows a "Submitted" Inventory Status.

           I have tried the calculation " Evaluate ( Case (Purchase Order Line Items::Inventory Status) = "submitted" ; Sum (Purchase Order Line Items:Total Quantity Submitted))

           But it does not seem to sum every record where the first condition is true, it seems to only return the value of the first record found where the condition is true.

           I can't find the right formula.
           Screen shot attached.

           Thanks :)


        • 1. Re: SumIf function?

               Sum cannot sum related records selectively. It always computes a sum of the specified field for all related records. One option is to set up several relationships that each match only to related records that all have a specified "status" value. This requires using multiple occurrences of the related table and setting up match fields in the parent table that return a constant status.

               Another option--which could not be used in table view, it would require list view, is to set up a series of one row filtered portals with a portal filter expression that specifies a different value in the status field for each portal. A summary field defined in the portal's table can then display a total based on just the related records with the specified status.

               A third option only possible with FileMaker 12 or newer is to set up an SQL query inside the new ExecuteSQL function to compute these selective totals.

          • 2. Re: SumIf function?
            All of Phil's suggestions are great. The way I usually handle this type of scenario however involves creating new calculation fields. One for each condition you might want to summarise. Once they're created, you can probably use the sum function on them through your existing relationships.