
1. Re: Get a summary calculation based on certain criteria
philmodjunk Sep 26, 2012 11:23 AM (in response to tracylynn1212)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 Sep 26, 2012 3:53 PM (in response to 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 Sep 26, 2012 3:56 PM (in response to tracylynn1212)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 Sep 26, 2012 4:07 PM (in response to 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 Sep 26, 2012 4:20 PM (in response to tracylynn1212)That worked! Thank you sooooooo much for your help on this!
:o)