1 Reply Latest reply on Mar 4, 2013 8:30 AM by philmodjunk

    Summary of summary fields between tables question (FMP 10 Advanced)



      Summary of summary fields between tables question (FMP 10 Advanced)


           I'm not even sure how to phrase this question without sounding confusing, but I'll try.

           There are 2 tables: Customers and Services. They are linked by the primary key for the Customers. This is not a many-to-many relationship because each Service belongs to just one Customer. It's just a plain old one-to-many relationship.

           Inside each Customer record there is a portal showing each Service used by the customer. That's fine.

           Each Service record has a cost (how much it costs us to run the service). I added a summary field to Services so if I search on a  customer I can see the total cost of that customer. Then looking back at the Customer record I can see how much the Customer is paying us each month and compare. The summary shows the total cost of any found set inside Services. That's fine.

           Then I tried an experiment. Inside the Customers layout I added a new field - the summary field from Services. Automagically the value shown on the Customer record happens to be the total cost of all the Services for that Customer. How cool is that? I didn't expect that, and don't know why that happens, but it is cool. Basically it seems to "know" to show the summary of the found set for that Customer. The same value as if I did a search for that customer inside the Services table. I would like to understand more why it worked that way, but that's not my main question for now.

           My main question is this. Similarly, in the Customer table I have a summary field for monthly revenue paid by a particular customer. And if I do a search, the summary field shows the revenue for the found set.

           But the field for cost always just shows the cost for the Customer record I happen to be looking at. What I would also like is the total cost of the found set of customers. In other words, a "summary of summary fields" of the cost field showing on the Customer records, which is itself a summary field from the Services table.

           Do you know what I mean? Is there a way to do this?



        • 1. Re: Summary of summary fields between tables question (FMP 10 Advanced)

               Define a calculation field in customers that returns the total from the services table. You now have a field in the customers table that your summary field can refer to in order to compute a total or other aggregate value.

               Option 1:

               The sum function can compute the total of a field n the related table.

               Option 2:

               Your calcuation field can simply be a reference to the summary field in the related table. If that field is named services::sTotalCost, then the expression for your calculation field can be:


               Either method computes the same total and makes that total accessible for a summary total in your customers table.


                    I would like to understand more why it worked that way,

               A "non Running" Summary field produces an aggregate value (a value from more than one field in one record). The value returned is determined by the context in which it is used/displayed:

          Summary field is referenced on a layout based on the table in which it was defined:

          A group within a FoundSet

               If you place the summary field in a subsummary part that specifies the "break" field that grouped the records when the found set was sorted, you get a subtotal--the total for that group.

               In a calculation, you can use the getSummary function to access the same group based sub total.

          All the records in a FoundSet

               If you put that summary field in a layout part other than the sub summary part, you get the total for all the records in the current found set.

               If you refer to a summary field in a calculation field defined in the same table as the summary field, it will also return a total for the current found set. (Which is why we have the GetSummary function to get sub totals in calculations.)

          Summary field is referenced on a layout based on a table related to the table in which it was defined:

          Not in a Filtered Portal

               If you place the summary field on a layout based on a related table or refer to it in a calculation defined in a related table, the relationship controls the value that is computed. It will be based on all the records in the summary field's table that are related to the current record in this table.

               Think of it this way, if you put a portal on this layout to the summary field's table, you'd see all the records in this portal that are used to compute the summary field's value in this context.

          In a Filtered Portal (FileMaker 11 and newer only)

               If you place that summary field inside a portal with a filter, you no longer get a value based on all the related records. Instead, you see a value based on all related records for which the filter expression evaluates as True.

               This is a special case use of a summary field that is often implemented by putting a single row copy of a filtered portal below it with the summary field inside so that the user sees a value based on just the records visible in the larger portal.

               This is a "Display Only" trick as you cannot refer to the value of this field in a calculation and get the same value shown on the layout--you get the result described in "Not in a Filtered Portal" above.

               Note that this does not just apply to "total" summary fields, Average, Count, Maximum, standard deviation, etc all follow these same rules.