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

    Summing the value of a field



      Summing the value of a field


      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 !




        • 1. Re: Summing the value of a field



          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)



          FileMaker, Inc. 

          • 2. Re: Summing the value of a field

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



            • 3. Re: Summing the value of a field



              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.



              FileMaker, Inc. 

              • 4. Re: Summing the value of a field

                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 !)