3 Replies Latest reply on Feb 17, 2017 5:35 PM by philmodjunk

    Summary Fields Causing grief

    BHallman_34

      Having an issue with the time it takes to open layouts and add data to table.  Here is the current setup:

      Table 1::Production Data   This table has information related to our printing process where I summarize time, material, waste by various sub groups and grand totals.  Everything works perfectly here, but the calculation fields are causing some issues when getting into the layout and entering data into the layout.  My issue is that I have roughly 3000 records in the table in a short time span and the summary/grand total fields seem to recalculate in particular when entering the layout/table for the first time.  I will have many thousands of records by years end.  It literally takes around 2 minutes on initial entry, and the same recalculation can occur when one enters/changes a record.  Data is entered into the table via iPad from various parts of the production process and this recalculation seems to be popping up slowing the process down.

       

      The summary fields are used to create production reports for press, shift and job by week, day, year, year2date and any financial period in the current fiscal year. 

       

      #1  Is there a means of controlling when summary fields calculate?  I cannot find one...

       

      #2  I have thought about creating two tables:  Table 1 :: Production data and Table  2:: Production data Report, using script to export the found set from the table 1 to table 2.  I would move the summary fields from table 1 into table 2.  This would allow me to keep the summary fields from calculating until I had the specific set of data required for reporting in table 2.  Thinking this should eliminate the calculations in table 1 and eliminate the time required to enter data. 

       

      Process Thoughts:

      Table 1 Layout

      export found set into table 2

      perform reports (calculations have minimal data to summarize)

      delete all records from table 2

      return to table 1 layout

       

      Is there a simple way to export data from one table to another in the same file?  Currently I would export table 1 data to desktop and import into table 2??  Is there a way to just go from table 1 directly to table 2? 

        • 1. Re: Summary Fields Causing grief
          philmodjunk

          What are your summary fields calcuating?

           

          the simplest fix is to do data entry on layout where you do not have any summaries fields. Limiting your found set to a smaller number of records would also help.

          And summary fields are not the only way to compute such aggregate values so a different approach is needed if you need to see an up to date total while doing data entry.

          • 2. Re: Summary Fields Causing grief
            BHallman_34

            The data entry is done on a layout not having the summary fields, but I still get the delay of recalculating from time to time.  The layout in question aggregates all those entries so Production can analyze.  This layout has numerous summary fields (both sub summary and grand total) and experiences the calculation delays upon entry each time.  This happens before the layout is ever entered.

             

            What other approach would you suggest?  How does one summarize data without using summary fields?

            • 3. Re: Summary Fields Causing grief
              philmodjunk

              "The data entry is done on a layout not having the summary fields, but I still get the delay of recalculating from time to time. "

               

              That should not happen. Both summary fields and unstored calculation fields only evaluate when referenced. Perhaps you have another window open to a layout that does have summary fields?

               

              To access a summary report layout, enter Find Mode first, then change layouts and perform the find. You will still get a delay after the find, but that delay will be while the fields update over just the records in your found set--hopefully not all the records in your table.

               

              There are many ways to compute aggregate values. Some--such as aggregate functions and executeSQL  may not be any faster. But other methods compute an aggregate value and store it in a number field.

               

              Two examples:

              historical data might be summarized or "condensed" into records of a summary table. A script periodically loops thru your original table, computes values and then stores them into records in the summary table. You then set up your reports to use the summary table rather than the original data. I have one solution where a script each night summarizes up to a 1000 line item records from the day's receipts into about 20 summary records.

               

              A script can update a stored value--possibly in a related record, each time data is modified. This "transaction based" management method has to be designed with care but avoids waiting on FileMaker to calculate all values from all individual records with every report.