4 Replies Latest reply on Jul 19, 2013 6:55 PM by ChristopherTan

    Summarizing Fields for Report slow.

    ChristopherTan

      Title

      Summarizing Fields for Report slow.

      Post

           I have a solution for my business that does several types of sales. I now find that when I go to the report,  when I go to the  layout - yearly/monthly report the sort takes a few seconds but the summarizing of fields takes a few minutes. Is there a way of speeding it up?

           I recall somebody suggested that I make a separate table (?)  for each month, and then summarize that to speed things up. This makes sense as there's less records and fields to summarize.

           Can I have suggestions as to the preferred way to do this? To get the Get monthly Totals. And how would I get it to automatically generate a new record for each month and totalize the month?

           Been pondering for this for sometime. I'm not a full time Filemaker programmer, so I just program my Filemaker solution when I need to add/change things, so my terminology might be atrocious.

        • 1. Re: Summarizing Fields for Report slow.
          philmodjunk

               A separate table for each month? !!! Ouch! that doesn't sound like the best of ideas as you'd loose a lot of reporting flexibility with the 12 separate tables. Approximately how many records in your report are being summarized? Several minutes would be typical of very large numbers of records such as a found set with more than several 100 thousand records. If your found sets are much smaller than that, there may be an issue with how you have structured your data.

               For getting much faster response times with summarized data, using a script to generate already summarized data in a different table is one way to do that, but you need just one such table, not one for each month. The main issue you have to examine carefully is whether the data from which your script is computing summary values is subject to change after the data has been so summarized. If so, you have to have yet another script in place to generate the needed summary values.

               Here's an example of how we do it in broad outline: Our business redeems scrap and used beverage containers from the general public. Thus, we generate between 500 to 1500 purchase orders a day with a minimum of 4 line item records for each PO. After the business closes for the day, a script finds all the days line item records, sorts to group them by type of material purchased and then loops through them, creating one record in a summary table with the date, material and several sub totals for that one type of material. This generally creates less than a dozen new records in the summary table from those 1000's of line items. We can then pull up a number of different summary reports from layouts based on this summary table--including one that compares monthly totals and averages over a 5 year time span, very quickly.

          • 2. Re: Summarizing Fields for Report slow.
            ChristopherTan

                 Ooops? I didn't mean that. I meant a separate table for the summary and a new record for each month. My FM solution has over 100,000 records that is spread over 3 files. Maybe that adds to the summarizing time?

                 The data I'm summarizing in theory should not be changed. but there is the odd occasion that we might have to go back a a few weeks or a few months to amend one record. With regard to this - " The main issue you have to examine carefully is whether the data from which your script is computing summary values is subject to change after the data has been so summarized. If so, you have to have yet another script in place to generate the needed summary values." Could you explain in more detail the last sentence?

            I think I get your broad outline, Thanks!. So you would get a script to run when closing the solution at the end of the day. This script would create a new record in the summary table with the days summarized data. I was cracking my head how to do it monthly (or do it manually at the completion of the month) but this sounds easier. Only issue with this is how can I get the system to not create a new record if the solution is shutdown and restarted on the same day due to technicalities? Would I tell FM the key field date field should be unique and not to create a new record but just re update (refresh?) the existing record? (Pardon my poor technical terms)

            • 3. Re: Summarizing Fields for Report slow.
              philmodjunk

                   We don't run the script on file close. We leave the file up and a robot file opened by a Windows Scheduled Tasks performs the script to summarize the data after business hours once a day. The script also uses Import Records steps in an "archive file" to export the data and thus needs to be run from a robot file so that it is performed in "client context". If we did not also need to export data, we'd just use server's scheduler to summarize the data.

                   

              Could you explain in more detail the last sentence?

              Because this is all in "broad outline", there's not much more that I can say as I don't know the specific details of the design of your database and what kind of changes you might need to go back and do. In broad outline, you'd need to be able to perform a script that repeats the process of your main summarize script--it might even be the same script so that the affected set of summary records generated when you previously summarized the data can be either updated or replaced so that your summary data accurately reflects the change made.

              In our system, we can bring up the set of Purchase Orders for a given day on a layout where one of the buttons is labeled "resummarize". Due to other changes that take place at the same time this button click performs a script that alters a status field in each of the records and then calls the standard summarize script to update the summary table with recalculated totals. It's a bit "brute force" as it updates a number of records that don't need updating in order to update the few records that do, but it does the job for something we rarely need to do.

              • 4. Re: Summarizing Fields for Report slow.
                ChristopherTan

                     OK, I get the gist of it. Thanks for your assistance!