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?