Aggregate data on a set of 500 records really shouldn't have performance issues, but when that number grows to be a much larger figure, a "summary table" that stores aggregate values as simple number fields where one record in the summary table represents a group of records in the original table can make for much faster reporting--First did that with FileMaker 3 or was it 4....
Note that keeping such data correctly up to date when data in the original records changes can be a real headache, but can be done. If your data is pretty static--and sales data usually is, it's not so bad.
Here's a basic outline of the method I set up to summarize the line items off of purchase orders into records of a summary table. The business where I created this method still uses it today:
Add a field to the original table that you can set to a value to "mark" a record as having been used to generate a summary table record. Then set up your script like this:
Find all records not marked as "summarized"
Exit loop if no records found
Find all records of the same "group" as the current record. (In our case these were line items for the same material)
Create a new record in the summary table and use set field to set the number fields to the value of summary fields in the original table that compute the needed aggregate values (totals, averages...)
Use Replace Field Contents to "mark" these records as Summarized
Note that this script ran late at night while the business was closed so there have never been any record locking issues with the replace field contents step.
I have 500K rows of sales
500,000 is a bit more beefy than 500.
The method described is correct in terms of storing summarized data.
I would highly recommend that you move to FileMaker server as soon as possible. Doing so opens up the capability of using "perform script on server", which will allow you to offload the "refresh" of the aggregate data to the server while the client continues on.
Your data looks pretty vanilla though, aside from the sort operation for 500k records, you may want to revisit the indexing, and the difference between stored and unstored calculation fields.
you may also get value of potentially converting some calculation fields to auto-enter number/text fields to gain performance.
Researching in the forums can also lead you on to developing for performance. Try this thread:
Thanks Mike, I did miss that "k", not that it affects my response as I assumed that this was needed for very large record counts. In the solution where I originally set up this approach, they routinely pull up cross tab reports showing monthly subtotals and averages of a material or group of materials purchased comparing these amounts over a 5 year span. The business generates a bit under a thousand records a day in the original table and operates 6 days a week. The summary table condenses the day's purchases down to less than 20 records so the efficiency savings here is quite large.
2 of 2 people found this helpful
What I can suggest to try is the following:
1) define 2 summary fields, Total( Dollar Sales ) and Total ( Unit sales ) in your data table.
2) create a table called Report. Add gStartDate, gEndDate etc. to it.
3) create a global field, gKey, in the Report table, and a TO called Totals, which is based upon your data table.
4) create a relationship between gKey in Report and UUID (primary key) in the Totals table.
5) in a script, fill gKey via SQL, with the ID's of records from the data table which meet your search criteria.
6) Look at the summary fields from 1) as seen via the relationship Totals that you defined in 4). They are your totals.
7) Put all the totals you care about in $$Variables and create your reports in the Report table, using merge fielding of the $$vars.
It works quite decently for me when reporting on big tables.
Thanks for the step by step. It's helping me work through the solution. If you don't mind I have a couple of questions.
For the SQL script, I'm filling gKey like a list value or single row per record like a virtual list? Maybe a different way of asking is, will multiple UUID's be contained in a single "cell" that creates a form of aggregation. I'm new to this and trying to understand.
Could you provide a little more detail to points 6 and 7?