3 Replies Latest reply on Oct 27, 2011 11:02 AM by philmodjunk

    Grouping

    KeithMoran

      Title

      Grouping & summing

      Post

      Hello,

       

      [I'm new to filemaker but have some sql background.]

       

      I am hoping to group entries by a specific field and display a total for all of those fields.

       

      ex)

      amount  - contract

      $5     -   A

      $6     -   B

      $3      -  A

      $7     -   B

       

      desired output

       (total) amount     -       contract

      $8                    -       A

      $13                  -       B

       

      I'm certain this is very straightforward, however I'm having difficulty determining the quickest / easiest means to accomplish it.

       

      Thanks in advance for any advice / assistance

      K-

        • 1. Re: Grouping & summing
          philmodjunk

          It is pretty straight forward but not the most intuitive....

          Start with a new layout based on the above table and with list view specified.

          In layout mode, double click the body part and change it into a sub summary part. Specify the contract field as the "when sorted by" field.

          Put a Summary field that computes the "Total of" Amount and your Contract field inside this sub summary part.

          Return to browse mode and sort your records by Contract. (If you don't sort your records by the "sorted by field" this type of layout will appear empty.)

          In FileMaker 10 and later, you can see the results in browse mode. In earlier versions, you have to preview or print the layout to see this report.

          • 2. Re: Grouping & summing
            KeithMoran

            Thanks PhilModJunk,

             

            Is it true that I must always use that field (ie- Contract) as the primary sort field?  Say I wanted to sort by another field (ie-date) and still have it sum the total (of contracts) is that possible?  

            • 3. Re: Grouping & summing
              philmodjunk

              Using this specific layout, you must always sort by Contract to group the records by the common value in that field.

              Given that you have multiple records for each contract, how would you sort them by a date field? is this a value that will be the same for all records for the same contract?

              IF so, you could specify a sort order that sorts by date and then also sorts by Contract.