7 Replies Latest reply on Feb 9, 2017 5:55 PM by user19752

    Running total for common field

    ingwe

      Hello. First of all I am a beginner to filemaker. I hope you guys can help me. I am trying to create a field that will keep a running total on the amount of another field. For example:

       

      Database with 1000 records. 200 records are type A, 200 are type B etc.....

       

      Product name: X  (name field)

      Product type:  A    (name field)

      Product price:10 (variable)   (number field)

      Running total:  (would keep a running total of the amount all product A types are worth)

       

      Product name: X  (name field)

      Product type:  B    (name field)

      Product price:10 (variable)   (number field)

      Running total:  (would keep a running total of the amount all product B types are worth)

       

      I hope I have explained this well enough. Could anyone give me a calculation (or something else) to make running total work? Thank you

        • 1. Re: Running total for common field
          philmodjunk

          Do you want to see just one row for type B and one row for Type A or do you need to list all the individual records with a total for each type?

          • 2. Re: Running total for common field
            user19752

            I guess you want something like

            TypePriceRunningTotal
            A1010
            A1020
            B1111
            B1021
            A1232
            B1233
            • 3. Re: Running total for common field
              ingwe

              Thank you for your prompt nreplies. Maybe using the term running total was wrong. I would like for all records that are type A to show the total worth of all As. All records that are type B to show the total worth of all type Bs. Cs, Ds etc.....

               

              so it would be

               

              Type    Price    Total

                A         10         32

                A         12         32

                A         11         32

                B         10         25

                B         15         25

                C         10         30

                C         10         30

                C         10         30

               

              I hope I explained it better this time.

              • 4. Re: Running total for common field
                philmodjunk

                Define a self join relationship (uses a second occurrence of the same table) that matches by type. Then the Sum Function can total Price From the related table occurrence. Note that this sums all records of a given type in the table--which might not work for you.

                 

                And unstored calculation field using ExecuteSQL might be also used.

                • 5. Re: Running total for common field
                  ingwe

                  I am just starting to use filemaker. Maybe you could give me more detailed instructions or a sample database where I can see what was done? Thank you kindly. I use fmp14

                  • 6. Re: Running total for common field
                    keywords

                    See if the attached gives you the idea. The key steps are:

                    1.     Set up the relationship using Type field

                    2.     Make the layout based on the related TO (table occurrence)

                    3.     Set up the running total based on Type sort

                    4.     Sort the file

                    • 7. Re: Running total for common field
                      user19752

                      For your result that sorted by type, you can use old style summary field.

                      1. make a summary field

                           total of price (don't need "running total" option. and this field is not need in layout)

                      2. make a calculation field

                           GetSummary ( totalOfPrice ; type )

                      3. sort records by type.

                       

                      This sums on found set.