1 Reply Latest reply on Mar 26, 2013 4:38 PM by philmodjunk

    Unable to total summary field for referenced database

    sublimelobc

      Title

      Unable to total summary field for referenced database

      Post

           Is it possible to get a summary total from a referenced database? For example, in the screenshot the field "::SOLD DA.." is a field from my database with my records for 2010.  The current database is the current year records, but I am trying to list records and make a summary total by item for previous years to figure out how much inventory to send to the current year trade shows. 

           for the item "KIMONO" it shows "Total: 15.00"  but only because the record following KIMONO shows 15.  If you look further down, the item "KING TUT" shows no total, because the first record after the summary is empty.  

           My summary works great for the fields from the active database, for example "SENT DALLAS 2011", and "SENT DALLAS 2013".   

           Is it not possible to get a total summary for records from a referenced database? 

           The reason I'm trying to do it this way is to avoid having so many calculation fields in my database. Each year has 800 calculation fields for analyzing information, if I don't use separate databases for each year it makes it very hard to filter through all of the calculation fields if I ever have to add something or change something. 

           I wish filemaker allowed me to create and organize my calculation fields into folders, that would be one solution.  My second attempt to create a summary total for each year is to make a new calculation field that brings in the information from each prior year's database, but this just adds to my already out of control amount of calculation fields. 

           Any ideas? 

      Screen_Shot_2013-03-26_at_4.06.50_PM.png

        • 1. Re: Unable to total summary field for referenced database
          philmodjunk

               A summary field from a related table will compute a summary value for the set of related records--which is likely to be a different total from the found set based result you get when you refer to a summary field defined in the table on which your current layout is based.

               To get the correct total, you'd either need to switch to list view for this and use a one row filtered portal or use a relationship to refer to the summary field that matches to the correct set of related records.

               If you have Filemaker 12, you might also use ExecuteSQL to compute this value.