5 Replies Latest reply on Oct 21, 2011 12:24 PM by philmodjunk

    Help with Calculating the sum of a number over a series of records.

    paulwelden

      Title

      Help with Calculating the sum of a number over a series of records.

      Post

      Hi guys im semi new to fileMaker and im stuck. What im doing is Trying to add up a totat over a series of files in the same table. There is a price total at the end of each file, and i need to add up all the price totals to give me a Grand total that will be displayed on another file\table later. 

      So i guess what would the calculation look like to add numbers from multipul records. thanks So much paul.

        • 1. Re: Help with Calculating the sum of a number over a series of records.
          philmodjunk

          I think you mean "fields" rather than "files" here. Wink

          You can open Manage | Database | Fields and add a new field of type Summary. You can specify that this field compute the "total of" the field you want to do this with.

          Now this field will compute the total for the current found set of records.

          It can also be used inside a sub summary layout part to compute sub totals of records grouped by sorting on a common value.

          From a layout based on a related table, this same field can compute a total of all related records or even just the records shown in a filtered portal.

          • 2. Re: Help with Calculating the sum of a number over a series of records.
            paulwelden

            You are correct i did mean "field." 

            I did get the summary to calculate the correct total for all the fields. Now is it possible to Show that total on "another" table that is not related?

            When i try to connect the fields in the Manage database relationship window the Summary fields with the totals are Grayed out!! Is there a way to just pull that data from the summary to be shown elsewhere in the databe tables?

            • 3. Re: Help with Calculating the sum of a number over a series of records.
              philmodjunk

              It needs to be a related file, but you can't use a summary field as the match field in a relationship. You'd need to use a different field to define the relationship. Once you have, you can add the summary field from the table where it was defined to the layout based on this other table that is now linked in a relationship.

              The type of relationship you use depends on what you are trying to accomplish on this layout based on a different table.

              • 4. Re: Help with Calculating the sum of a number over a series of records.
                paulwelden

                i tryed that once and it wouldnt display the summary field the box is empty. are there different types of relationships? i was just drawing a line from 1 table to the other. does it matter what fields i draw the lines to? i dont think it should.

                • 5. Re: Help with Calculating the sum of a number over a series of records.
                  philmodjunk

                  It matters. More importantly, the values in each field matter. A relationship specifies which record in one table "match" to records in another. The data type of the two fields can also be significant. If, in the current record, you have 1 stored in a number field matched to a number field in the other table, it will only link to records in that other field that also store a 1.

                  In addition, if you double click the relationship line, you'll find that you have several different operators you can specify instead of the default = operator. If you selct X, for example, any record on one side of the relationship will match to all records on the other..