2 Replies Latest reply on Jan 16, 2014 11:03 AM by Stephen Huston

    Calculating Total Sales per Customer

    molokna

      Hi

       

      We use Filemaker to keep track of orders, sales, etc. We have many repeat customers. Each order is a seperate order form and invoince. I would like to learn how to calculate the total sales from a particular customer.

       

      ex.

       

      Joe has placed 30 orders from 2012 - 2013. I want to learn the total sales $ of all of Joe's sales.

       

       

       

      Can anyone assist?

        • 1. Re: Calculating Total Sales per Customer
          erolst

          molokna wrote:

           

          I would like to learn how to calculate the total sales from a particular customer.

           

          Can anyone assist?

           

          Certainly, but it would be helpful to know your setup. What tables do you have, e.g. Customers, Orders/Invoices, LineItems, Items etc.?

           

          EDIT: Didn't read your edit … Stephan made a number of good suggestions; in the end the “best” method depends on your setup and workflow.

          • 2. Re: Calculating Total Sales per Customer
            Stephen Huston

            Using the Aggregate functions, I suggest something like:

            • Sum ( InvoiceForCustomer:preTaxTotal )

            This can be a calculation field in the Customer table, summarizing a field in the related Invoice table via a relationship named InvoiceForCustomer, using a pre-tax invoice total field.

             

            You can use other names for these fields or relationships.

             

            You could also use a script trigger onRecordLoad for the customer record to set that value to a global variable ($$salesTotal) on the layout. If properly setup, such a script trigger and variable would avoid the need for the field at all, unless you want to report this value out for a list or group of customers, in which case the field would be better.

             

            You could also use a script trigger to update a non-calc field in the customer record everytime an invoice is committed, which avoids creating an unstored value across the relationship, making reporting much faster down the road.