Powerful Pivot Table Presetting

Introduction

When it comes to reporting in FileMaker, there are a few options available to developers

• Visual reports, using sub-summary parts, table view etc
• Exporting data
• Charting (ugh!)
• Tableau Connector

While it may seem like there is a plethora of options, with the exception of Tableau all these other methods require the developer to have a fair amount of input in producing a desired report output for the end user. FileMaker does not have very good end-user level tools available to help construct your own reports and interrogate your own data.

Sure, you can open up table view to your users and let them add sub-summary parts and summary fields, but do you really want them to do this? Do you want them to add new fields? Do you want to be restricted to making sure your field names are nice and friendly in order for export fields to make sense?

No? Neither do I.  There must be a better solution. One where we can give the user far greater control over constructing their own meaningful reports and obtaining useful information about the data in their solutions.  

This is where Pivot Tables come in.

What is a Pivot Table?

Microsoft gives the following succinct definition of a pivot table:

A PivotTable is a powerful tool to calculate, summarize, and analyze data that lets you see comparisons, patterns, and trends in your data.

To me, a pivot table is very much like a chart, but rather than a visual summarization of an X and Y axis, they are instead a table-based summarization of a row and column.

Consider this example...

Let's say our solution has invoices.  Invoices are for customers, and are for selling products.

What kind of useful information might someone want to learn from their invoices that could help make better business decisions?

Well, one such thing could be how much each customer has been invoiced for each type of product. This could help the business know which products are popular with which customers.

In FileMaker, we could provide this information as a sub-summary report. However these reports are restricted to vertical display. Layout parts are vertically stacked and so while we could produce these using simple sub-summary parts and summary fields, we might end up with pages of data to display this information.

Pivot tables on the other hand are just that - tables - and display this same information in a tabular format using rows and columns:

The information presented above is much cleaner to read.

Using PivotTable.js 

So we're going to use a pivot table in FileMaker, exciting times! For this we choose PivotTable.js because it is open source, highly flexible and easy to use (and has been previously integrated as we'll see below).

PivotTable.js is loaded with cool features that you get right out of the box, some which come to mind are:

• Multiple fields defined for rows and columns, think of this as akin to live sub-summary part groupings
• Filtering of rows and columns to specific values
• Sorting rows and columns
• Over a dozen ways to visualise your data, from tables, heatmaps, charts and exports!
• Multiple aggregators (like summary fields) - show counts, sums, averages, std deviations, max/min and more!

There are so many more cool things but these should give you a good idea of what to expect.

How NOT to Reinvent the Wheel!

Now, we're not the first to talk about PivotTable.js by any stretch of the imagination, this has been well covered before, with great articles and demo files which we ourselves have utilised for this article. 

The main one is a great article from that javascript wizard Jeremy Brown. You can check it out here.  In it he goes into more detail about the integration of PivotTable.js and provides a really cool demo file too.  We encourage you to check this out, as it will help you get up to speed with integration - something we aren't going to cover here, as our integration is based on Jeremy's.

PivotTable.js has also been featured in Carafe.FM as an add-on which you can check out here.

So What's Missing?

We used PivotTable.js in our solutions and our clients loved it. But there was something missing. Clients were not able to save the pivots they constructed. Each time they logged back in they would have to rebuild their previous configurations.  

Hmm, wouldn't it be great to be able to easily save and load configurations of the Pivot Table so that users can easily recall their favourites.  Each construction of a pivot table is really like a report in and of itself.  With save capabilities users can basically build their own reports !

And so that is exactly what we did. Turns out it wasn't too difficult.  Thanks to FileMaker 19's new web viewer integrations, we can utilise the following two powerful actions:

• Perform Javascript in Web Viewer script step
• FileMaker.PerformScriptWithOption javascript function

In the following sections we'll see these in action.

Saving Presets

The first step is to get the users current configuration of the pivot table out of the web viewer and stored in FileMaker.  We achieve this through a very simple process:

1. A FileMaker Script calls the getPreset javascript function
2. This javascript function in turn runs a FileMaker script using the FileMaker.PerformScriptWithOption function and passes it the current configuration as a parameter.
3. This script in turn sets the configuration into a global variable.
4. The original script continues to run, and stores that global variable contents into a preset record.

Note that we use the FileMaker.PerfromScriptWithOption rather than the FileMaker.PerformScript function.  

in FileMaker 19.0 release, the default behaviour was to run the initiated script after any currently running scripts are finished.

Instead what we want is to have the script run immediately, returning it's configuration data for use in the earlier script, in order to be saved as a preset. To do this, we make use of the new FileMaker.PerformScriptWithOption call which allows you to specify how the currently running script is handled when the new script is run.

You can learn about these options here in this Claris Article.  We want to use option "5" which is suspend & resume, meaning the initiating script is suspended, the new script run, and then the initial script is resumed - exactly the same as if you were running a sub script.

So that's the FileMaker side of the equation, we just need to write a javascript function to return the current config. This is really easy. in PivotTable.js there is a variable "currentConfig" that contains just that. We simply return this to FileMaker as shown below:

var currentConfig;

function getPreset(parameters) {

     FileMaker.PerformScriptWithOption ( "PIVOT: Get Preset", JSON.stringify ( currentConfig ) , "5" ) ;

};

Loading Presets

Loading a preset is basically the reverse. We have the preset code stored in FileMaker and we want to pass this into the web viewer and have PivotTable.js load it in for us.

This is done by calling a second javascript function - updatePreset - and passing it the configuration code. 

This function will in turn parse the JSON configuration sent to it, and load it into PivotTable.js

function updatePreset(config) {

    let configjs = JSON.parse(config);

    configjs.onRefresh = function(config) {

        //delete some values which will not serialize to JSON

        delete config["aggregators"];

        delete config["renderers"];

        delete config["rendererOptions"];

        delete config["localeStrings"];

        currentConfig = config;

    };

    $("#output").pivotUI($.pivotUtilities.tipsData, configjs, true);

}

If you don't understand how it works, don't worry - you don't need to.  The end result is the ability for end users to save their own presets! 

FileMaker.PerformScriptWithOption

Just a quick note on the usage of FileMaker.PerformScriptWithOption for the saving of presets.  We use Option 5 of suspend/resume. This was only introduced in version 19.1.2 and above, so those of you still on an original release version of 19 will not see this working as expected - you should upgrade to the latest release of 19.

If you choose not to, then you can still achieve saving of presets without issue, you just need to be a little more creative in your scripting. Given the javascript initiated script will be queued to run, you need to have this script carry out the preset saving.

Example File

What FileMaker Weetbicks article wouldn't be complete without an example file? In this you'll find everything discussed in the article, along with a simple pivot table example, and a preset example. You'll also find links to resources we have used to create this file and write this article.

Click here to download the example file

We love feedback so please leave a comment if you have anything to say - even if you don't it would be nice to hear from you :)