2 Replies Latest reply on Oct 30, 2013 7:03 PM by PeterMontague

    Get summary field to not calculate everything every time

    PeterMontague

      Title

      Get summary field to not calculate everything every time

      Post

           I have a sales report which uses a summary field to add up sales totals. I sort in monthly trailing groups and I can compare sales from month to month. What I like most about this feature is that I can see sales update the current monthly total and I can project if we are on target. I can produce charts from this too and it looks great on the iPhone. I've only got six months data on my database and it takes nearly a minute to summarise the totals. I am soon going to migrate the sales records from the last ten years into the database. Imagine how long that will take to summarise in our hosted database over 3G. The only month in which new sales occur is the current month.

           So I was wondering is it possible to make my sales report summarise only the current month and the last month's (sometimes sales are entered late) data and at same time see the data from all previous months?

        • 1. Re: Get summary field to not calculate everything every time
          philmodjunk

               We do a 5 year comparsion chart here where I work from data entered into a line items table. We are open 6 days a week, have from 500 to 1000 customers a day with a minimum of 4 line item records for each customer. That adds up to a very large number of records being summarized.

               My solution to producing that report in a timely fashion, was to not produce the report directly from the line items table. Instead, every evening, a script loops through the day's records and summarizes the data into what is usually less than a dozen records, data that is then stored in a "summary" table. Each record represents one type of line item purchased on a given date, with fields for quantity and price. By generating the report from this much smaller number of total records, I get a report that pops up very quickly even though I am looking at 5 years of monthly totals and averages.

          • 2. Re: Get summary field to not calculate everything every time
            PeterMontague

                 What would be a good script trigger for this? I was thinking of getting the script to check a box when it is done and I could then run the script every time the database is opened to calculate the unchecked records.

                 What should be done if I make a miscalculation in one of the sales? E.g. if a sale had a shipping cost of $5 and then I find out that the shipping cost only $3.