4 Replies Latest reply on Jan 7, 2014 9:12 AM by philmodjunk

    A Grand Total Field !

    jebber6685

      Title

      A Grand Total Field !

      Post

           I want to create a field that will return the total sum of another field independent of the found set. In other words, I want a summation field that does not change per the number of records in a found set but is rather file dependent. 

           So this field will return the summation of another field for the entire file and not just the found set. 

           How do you do this? I cannot seem to figure it out.

           I thought "running total" did this but it does not.

            

           Any help here would be greatly  appreciated.
           I have FMPro Advanced 12.0 v4
           OS - Windows Vista on desktop, Windows 7 on laptop

        • 1. Re: A Grand Total Field !
          raybaudi

               You'll need to create a CartesianSelfJoin relationship ( simply a relationship with a new Table Occurrence of the same table between two never empty fields like IDs using the X symbol instead of the = symbol ) and than use the Sum ( ) function:

               Sum ( CartesianSelfJoin::YourField )

          • 2. Re: A Grand Total Field !
            philmodjunk

                 CartesianSelfJoin::SummaryField

                 can also return the same total. It often makes no real difference which is used as both produces the same result. If you are editing values on the layout that then affect this grand total, use the sum function as it updates more smoothly. If you already have and need a summary field that totals this field, then this becomes a way to get that same total without having to add another calculation field to your table.

            • 3. Re: A Grand Total Field !

                   this is exactly what I have been looking for....thank you!!

                   I have multiple categories that I need grand totals for in the one page.

                   Is there anyway of have multiple relationships without have an additional table occurrence for every category?

                    

                   thanks

                    

              • 4. Re: A Grand Total Field !
                philmodjunk
                     

                          Is there anyway of have multiple relationships without have an additional table occurrence for every category?

                     Well multiple occurrences are possible specifically so that you CAN have multiple relationships.

                     But if you are using FileMaker 12 or newer, you can also get such totals using SQL queries inside the ExecuteSQL function.

                     And filtered portals can be used with summary fields from the related table to produce category specific sub totals.

                     And in a list view layout, one summary filed inside a Sub summary layout part can show different sub totals for each category if the fields being summarized are part of the current found set and you sort the records by the category field. (And such a report does not have to list the individual records, you can get one row for each category with a total.)