1 Reply Latest reply on Nov 13, 2013 3:37 PM by philmodjunk

    Grouping, Summing up



      Grouping, Summing up


           Hi All,


           I just started using FileMaker (working with File Maker Pro 12 Advanced), and I need to create something similar with the example below:

           I have a table (like the one below):

                          Country                     State                     City                     Car                     Bus                     Train
                          USA                     TX                     Houston                     4000000                     2000000                     1500000
                          USA                     TX                     Austin                     3000000                     1300000                     800000
                          USA                     TX                     Dallas                     6000000                     3000000                     1700000
                          USA                     TX                     San Antonio                     3500000                     1900000                     1200000
                          USA                     NY                     New York                     8000000                     4000000                     15000000
                          USA                     NY                     Albany                     900000                     300000                     200000
                          USA                     NY                     Buffalo                     700000                     100000                     80000
                          USA                     NY                     Long Island                     4000000                     1000000                     4000000

           From that table I need to Sum up "Cars" for TX (Houston+Austin, etc), and "Cars" for NY(New York+Albany, etc), to get a summary with the total number for each state like below:

                          Country                     State                     Car
                          USA                     TX                     16500000
                          USA                     NY                     13600000

           Can someone please help - what would be the right approach ?

           Appreciate any help !!


        • 1. Re: Grouping, Summing up

               In some cases, setting up your table so that you have three records, one for trains, one for Buses, one for cars, where you have one can be more flexible for some kinds of reporting purposes, but to give a very specific answer to your question,

               If you set up a list view layout, you can remove the body layout part and replace it with a sub summary layout part, "when sorted by" State. You can define a summary field that computes the total of the Car field and place it in this sub summary layout part.

               Then sort your records by the State field to group them by state and you'll get a total for Car for each state, one row of figures for each value in the state field.