I hope you have a summary field in your Sales Orders table which is type summary and Total of 'Amount'. Then duplicate your Sales Order table occurrence in your graph, calling it something like 'All Orders'. Attach it to your Customers table occurrence using the 'X' operator. This is called Cartesian Product and it means that all records are related. It does not matter what you choose to join them - it can be global, calculation ... anything except summary or container. The fields selected do not need values at all and they do not need to be same data type. In fact, once you create a cartesian, you can delete the fields out if you so choose and it will still work.
Now place this summary field from All Orders directly onto your Customer layout
Yes this worked! Thanks!
One concern, however....Since a cartesion product has TABLE1 x TABLE2 records (no join condition to narrow), won't this solution break down when I have, say a few thousand records in each table? That would be 1000 x 1000 = 1,000,000 in the cartesian product.
Perhaps FMP is smart about that too and keeps this problem from happening?
Appreciate your clarification on this. :)
Cartesians are safe to use. It only calculates based upon the current record to all Sales Orders. All relationships are from POV of current record only. This is true for all calculations. So in your example, it is totalling all sales orders (1,000) and displaying on the one record only.
Cartesians are used quite extensively in solutions.
Added: When I say 'all customers will relate to all sales orders' it still means one at a time on the customer side but it means that all customers will see the same 'all' sales orders.
Cool. Being new to FMP, and used to working with Oracle & MySQL, Cartesians are a big no-no as I'm sure you know.
I'm glad FMP manages this for me.
Thanks for your follow up. :)