5 Replies Latest reply on Sep 26, 2012 4:20 PM by tracylynn1212

    Get a summary calculation based on certain criteria

    tracylynn1212

      Title

      Get a summary calculation based on certain criteria

      Post

           Hello,

           I am stuck on something. I have a layout that has general customer information. On the layout is a portal (sourced to a different related table that pulls up items to put in the portal). Below is a basic example of entries that could be made on the portal (pretend each word is a field entry and each line is a portal line entry):

           Product A     Series 2     $5.00

           Product A     Series 8     $10.00

           Product B     Series 6     $7.00

           Product A     Series 3     $3.00

           I have a calculation set already to get the total of the portal dollar amount (in this case it would be $25.00).

           What I am trying to get is a summary of only certain items in the portal. For example, I want to know the total dollar amount for all Product A entries in the portal (so in this case it would be $18.00).

           I have a separate calculation field for all three of my product types (ex: Product A, Product B, and Product C). What I have set isn't working though.

           For instance, my Product A field is set with a calculation of: If (Product::Product Type = "Product A"; Sum (Product::Amount))

           All it returns is the value of the first portal line item for Product A ($5.00), not the total of all portal line items for Product A ($18.00).

           Any thoughts on where I might be going wrong? I keep tweaking at it but seem to get farther away from my goal.


           Thank you!

            

            

        • 1. Re: Get a summary calculation based on certain criteria
          philmodjunk

               There are several problems with what you are doing.

               First, it sounds like If (Product::Product Type = "Product A"; Sum (Product::Amount)) is defined in the Product table instead of your layout's table. If so, then Sum ( Product::Amount ) is only summing the value of the current record. Sum ($5.00) is $5.00 so that's why it looks like this is the case to me.

          Sum requires that it receive a list of values to sum so it needs to refer to either a related set of records, a list of fields explicitly listed in the Sum expression or the repetitions of a repeating field.

          Second, Sum cannot sum values selectively. Your IF function, even if defined in your layout's table, will either Sum all related records in Product or none of them based on whether the first part of the If function is true or false and that part of the function only references the first related record in Product (once this is defined in the layout's table instead of the portal's).

          In order for Sum to compute totals selectively, the list of values it sums has to be limited BEFORE they reach the sum function. This can be done by either defining calculation fields in the Portal's table such as: If (Product::Product Type = "Product A"; Product::Amount ) and then a sum function sums this calculation field instead of the amount field, or different relationship is defined that only matches to the Product A records. Both options require quite a bit of design work to make them happen for all possible values of Product Type, either you have to define a calculationf field for each Product Type or you have to set up a relationship for each product type.

               If you are only needing to display the totals, not use them in further calcualtions, there are ways with Filemaker 11 or newer to use one row filtered portals and a single summary field to compute the same totals.

               And sometimes a "summary table" can be set up so that you can list each product type once on a different row of the portal with the needed subtotal next to it. This one can take a fair amount of work to set up as well, but may be a good option for Filemaker 11 and older systems.

               But if you are using Filemaker 12, you can avoid the whole mess by using ExecuteSQL instead: FMP 12 Tip: Summary Recaps (Portal Subtotals)

          • 2. Re: Get a summary calculation based on certain criteria
            tracylynn1212

                 Hi Phil,

                 Thank you for your response. I had to spend another couple of hours fixing the mess I did last night just to get back where I originally started, so now..back to square one.

                 

            In order for Sum to compute totals selectively, the list of values it sums has to be limited BEFORE they reach the sum function. This can be done by either defining calculation fields in the Portal's table such as: If (Product::Product Type = "Product A"; Product::Amount ) and then a sum function sums this calculation field instead of the amount field, or different relationship is defined that only matches to the Product A records. Both options require quite a bit of design work to make them happen for all possible values of Product Type, either you have to define a calculationf field for each Product Type or you have to set up a relationship for each product type.

                 I am trying the suggestion of defining a calculation field for each Product Type. I only have three products (Product A, Product B, and Product C) so creating a calculation field for each shouldn't be too exhausting.

                 My Product Portal (sourced to my Product table) is on my Customer Layout (sourced to my Customer table) which both tables have an "=" relationship to eachother via an ID.

                 The Product Portal contains the fields "Product Type" (ie: select Product A, B, or C) and an amount field. It contains other fields as well but aren't relevant to what I'm doing in this case.

                 I added a Calculation field in the Product table called "Product A Amount Calculation" with definition of: If (Product::Product Type = "Product A"; Product::Amount). If I put this field on my layout, only the first Product A amount shows up in the calculation field even though there are several Product A line items in that record's portal. Also, where would I put the Sum field that sums this calculation field as you mentioned? What would the definition be for that field?

                  

                 :o)

            • 3. Re: Get a summary calculation based on certain criteria
              philmodjunk

                   You only got part way there. ;-)

                   The individual calculation fields you just created have to be summed by sum calculations defined in the Customer table.

                   When you put a field from a related table on a layout without putting it inside a portal, it refers to the "first" related record. If that first related record is not Product Type A, it will be empty, which is just what you set it up to do, but not what you wanted it to do. ;-)

                   If you define three calculations in customer: cTotalProdA, cTotalProdB, and cTotalProdC

                   defined as Sum ( Products::Product A Amount Calculation ), Sum ( Products::Product A Amount Calculation ), and Sum ( Products::Product C Amount Calculation ) respectively,

                   You can then put cTotalProdA, cTotalProdB, and cTotalProdC on your customer layout to see the totals for each product type for the current customer.

              • 4. Re: Get a summary calculation based on certain criteria
                tracylynn1212

                     WOOOOOOOOWWW....I think it's working!! I just tried one field for now and got excited! Ha ha. Let me try the rest while my fingers are crossed and I will let you know.

                     :o)

                • 5. Re: Get a summary calculation based on certain criteria
                  tracylynn1212

                       That worked! Thank you sooooooo much for your help on this!

                       :o)