4 Replies Latest reply on Dec 16, 2008 2:27 PM by Pafin

    Summing the value of a field

    Pafin

      Title

      Summing the value of a field

      Post

      Hello !

      I've have a database of orders with many fields in FM 8.5 :

      • customerID (001, 002, 003, 004, ...)
      • number of order (F2008.0001, F2008.0002, ...)
      • amount ($45, $65, $38, ...)

       

      Each customer can have many orders (if they're happy with the products ;-) ); each order is a new record.

       

      I've to do the sum of the amounts for each customer.

      It's possible to do it creating a field called GlobalAmount (statistic, SUM of amount, not progressive);

      than, I've created a field CustomerSum (calculation, RECAPITULATIV (GlobalAmount; customerID). but this works only if the records are sorted by customerID, wich is not always possible when you ask for the CustomerGlobalAmount in an other table of the database !

      How can I do it better ?

       

      Thank You !

       

      Pafin

       

        • 1. Re: Summing the value of a field
          TSGal

          Pafin:

           

          Thank you for your post.

           

          If you have the amounts in a related table, you can use the Sum() function to total those amounts.

           

          Create a calculation field, and for the formula, enter:

           

          Sum (<related table name > :: <related field name> )

           

          For example, if you are in a CUSTOMERS file, and you are trying to total the "Amount" field in the ORDERS table, the formula would be:

           

          Sum (ORDERS::Amount)

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: Summing the value of a field
            Pafin
              

            Thank you for the idea...

            But in fact, I'm trying to keep it in the same table ORDER (because the orders are just examples, in fact this is a fidelity program with points...)

            An idea ?

            Thank you...

             

            Pafin

            • 3. Re: Summing the value of a field
              TSGal

              Pafin:

               

              A Summary field allows you to total, average, count, etc.

               

              When you define a field, select the field type as "Summary".  Since you want to total an "Amount" field, set the field to Total the Amount field.

               

              If you have this field on the layout, you can display a total for all records, or you can find a subset of records and display that total.

               

              TSGal

              FileMaker, Inc. 

              • 4. Re: Summing the value of a field
                Pafin
                  

                STOP ! You gave me the solution...

                 

                I've just made a relation on itself...

                 

                ClientID is related to ClientID in the same table, with the name AUTO ; than, a new field SUM (AUTO::ClientID) !
                And it's ok !

                ;-)

                 

                Thank you !!

                Good night ! (11:27 PM !)