Like a Boss: Summarizing Data in a Summary Table

In a recent post on Charting, I used a FileMaker summary table as my data source. I clarified in that post my summary table stores a summary of the sales for a particular product for a particular month of a particular year. The use of a summary table in a custom app is worth exploring. I think it is a “Like a Boss” level technique, something you should consider that isn’t always part of the regular discussion about FileMaker.

Summarizing a FileMaker Summary Table

A summary table is simply a storage area for a summary of a closed set of data. In my example, the closed set is a month’s worth of total sales for each product. It is set up pretty simply. It is a narrow table containing:

• A field for each attribute that defines a unique combination. My table contained: Year, Month, Products.
• All the summary-type fields you need. My table only included Sales. But I could also include such fields as Trend or “Date_MostSales”.

Advantages

A summary table in our custom apps makes a lot of sense for the following reasons:

Unchanging data

It is unlikely that a particular time period’s, say June of 2012, data will change, so why not store it? Of course last month’s data might change a bit as final sales are tallied, but changes of data can be solved through business rules (all sales finish on the last day of the month) or through scripting that updates the created summary record.

No Calculated or Summary fields

I don’t need to build a report layout, add a summary field, then find June, 2012 Apple sales and examine the summary field. I have one record to look at in the summary table.

Static and Stored

The data is stored. The total sales for Apples for June of 2012 is stored and doesn’t need any additional calculated fields or summary fields to access the total.

Less Records

By definition a summary record is an encapsulation of many other records. If there were 14,000 sales across the products Apples, Peaches, and Bananas, in June of 2012, I can reduce those to three records. One for Apples, June 2012. Another for Peaches, June, 2012. Finally one for Bananas in June of 2012. Any additional finding (all of 2012, for example) can be done on this summary and sorted and charted or reported on much faster.

Initial Scripting

In order for this to work we have to set up a scripting process to run at regular intervals to do the summarization. Business rules would determine when and how often (weekly, monthly, yearly) the process would run. It would be a simple matter, then to find the data to be summarized, create the summary data and set it to a new record in the summary table. I’d create a JSON object to hold all the data I need to summarize and then create the records in one shot.

Something like:

{
"month":"February",
"year":2018,
"products":[
{
"type":"Apples",
"totalSales":43
},
{
"type":"Pears",
"totalSales":24
},
{
"type":"Bananas",
"totalSales":0
},
{
"type":"Peaches",
"totalSales":23
}
]
}

Since we’re creating many records that summarizes many records in this process, we have to consider whether or not the process should be transaction. I would recommend a transactional approach: allow this summary to be created for all product types or none of them.

Using Summary Data

The summary data table can be used in many ways.

1. Charting the total sales for a particular product over time using the ‘related records’ data source.
2. Performing a query for some chart or virtual list or other use case on this table will be much faster. I have fewer records to summarize in the query
3. Printable reports. I can make a simple report from this table without the need for many levels of sub summary parts and summary fields.
4. Create an entire dashboard of charts and lists.

These are just a few.

Updating the FileMaker Summary Table

The whole point of a FileMaker summary table is to store a summary of a set of data. As I said above, it is unlikely the data will change. But if business rules allow it to happen, then we would need to build a process to update the changed information. In the above summary, I’ve captured the total sales for February 2018. This process happened at 12:00 AM on March 1.  But on March 3, five pears were returned for some reason. Therefore, it is important to update the record in the summary table.

This is not that difficult to think about and to solve. Here are some thoughts I have about it.

• The script that processes the return could detect that this sale happened in a month that was previously summarized, go to that summary record, and update it appropriately.
• Each evening a process could find records modified in a time period past the current one and update their corresponding summary records

There’s really not a whole lot to this idea, but it is something of which to consider.

A FileMaker Summary Table is a useful technique and should be considered for data-intensive custom apps. If your use case requires summarizing a lot of data, this technique is a good and simple idea. There’s some considerations to ponder, but none of them are out of our skill sets.