4 Replies Latest reply on Nov 7, 2011 9:15 AM by disabled_morkus

    How to SUM all amounts on child table?

      Title

      How to SUM all amounts on child table?

      Post

      Hello,

      I have a portal where I relate a customer table to orders.

      I want to be able to show on the customer layout (where you see related orders on the portal below) the total of ALL orders in the orders table.

      What's happening, however, regardless of how I try this (calculation, summary, etc.) is that on the customer form, my "total" only shows the total for the current customer, NOT for all customers.

      I tried creating a separate table occurrence, but I get the error then (on the customer layout) that this table isn't related to the current layout (or words to that effect).

      So, how do I tell FMP that I want the total of all orders, not just the ones for the currently displaying customer?

      Do I need a completely separate layout or something like that?

      Thanks in advance.

      -m

        • 1. Re: How to SUM all amounts on child table?
          LaRetta_1

          Hi Mork,

          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

          • 2. Re: How to SUM all amounts on child table?

            Hi LaRetta,

            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. :)

            - m

            • 3. Re: How to SUM all amounts on child table?
              LaRetta_1

              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.

              • 4. Re: How to SUM all amounts on child table?

                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. :)

                 

                - m