Charts and Data in FileMaker Pro Advanced 17

According to the FileMaker Pro 17 Advanced Help, there are two options on how to create charts:

  • Create a quick chart in Browse mode.
  • Use the Chart tool in Layout mode to create a chart.

Since this article is aimed at developers, I will be taking a look at the second point here. In addition, I would add:

  • Using the WebViewer to create charts. (Another post for another day)

As mentioned, I will be looking at adding charts to a layout. I will assume you have some knowledge of FileMaker Charts.

If you need a refresh, you can always  click ‘Learn More…’ on the bottom left of this window shown, or view the FileMaker Help Page.

I think it is always worth having a look at the help pages. It is easy to get stuck in your ways, and not use a feature available to us, or be reminded of something we have completely forgotten.

Customising your chart

In short, you have the option to set the following:

  • Title
  • Type (Column, bar, line, and area chart, Pie chart, or Scatter and bubble chart) – More information on FileMaker Help
  • Data Labels
  • Colour schemes (limited to preset options) and fonts
  • Type of Data used
  • Data Source
ChartTypes

Select your Data Source

I will be looking more at the data source here, where again, there are some more options. You can chart data from the current found set, the current record, or from related records.

Current Found Set

This will look at all the records in your current found set:

You can choose a display option:

  • Summarised groups of records displaying data points for groups of records.(summary data in a header, footer, top or bottom navigation part, or body layout part.)
  • Individual record data displaying individual data points.

Related Records

Self explanatory really. You can pick from related data from the current layout (The right Context).

Current Record

This can be a powerful option, as will be explored below…

Charting Delimited Data

When charting delimited data, FileMaker is using carriage return-delimited data. To select where we get the data from, we can either ‘Specify Field Name’ or ‘Specify Calculation’.

Using ‘Specify Calculation’ really opens up our options on how we gather the data, which is why it can be powerful.

You may even want to use this option to replace the ‘Related Records’ and ‘Current Found Set’ options. The important thing here is speed. The Charts can be clunky and slow things down a bit, especially if you have a few on a dashboard. But, using Perform Script on Server (PSoS), when can build our results (for multiple charts in one script) a lot quicker and break them down in each chart.

  • More Options with ‘Specify Calculation’.
  • Can use it to get ‘Related Records’ and ‘Current Found Set’
  • Can be a lot faster.
  • Use PSoS to build the data.
  • Multiple charts with one block of data/one query

I will cover using ‘Specify Calculation’, using a PSOS script to build a JSON script result. Reasons for doing this include:

  • One script can run for multiple charts.
  • PSOS is faster.
  • Can be run as part of an open script.
  • It doesn’t matter what Layout we are on.
  • Limits the need for extra relationships.

Setting a JSON Script Parameter

Since version 16, we have been able to use JSON. This makes for a great script parameter. It allows us to build large amounts of information which can be quickly broken down into the parts we need.

The script, as mentioned can be called by the Open script, trigger, or perhaps a refresh button.

Let ( [
_date = get ( CurrentDate ) ;
_year = year ( _date ) ;
_month = month ( _date )
];
JSONSetElement ( “{}” ; //Create the basic JSON Object
[ “invoicePie” ; // The name of our 1st chart
JSONSetElement ( “{}” ; //Create another object within the JSON
[ “startDate” ; Date ( _month ; 1 ; _Year ) ; 1 ] ;
[ “endDate” ; Date ( _month + 1 ; 0 ; _Year )  ; 1 ]
)

; 3 ] ; //type JSONObject
[ “invoiceBudgetChart” ; //the name of our second chart
JSONSetElement ( “{}” ; //Create another object within the JSON
[ “startDate” ; Date ( 1 ; 1 ; _Year ) ; 1 ];
[ “endDate” ; Date ( 1 ; 0 ; _Year + 1 ) ; 1 ]
)
; 3 ] //type JSONObject
)
//End the Let
)

The Pretty Result:

{
“invoiceBudgetChart” : {
“endDate” : “31/12/2019”,
“startDate” : “01/01/2019”
},
“invoicePie” :
{
“endDate” : “30/03/2019”,
“startDate” : “01/03/2019”
}
}

Some things to note here:

  • The JSON is resorted to be in alphabetical order. This does not matter when getting the results.
  • Note the use of the “0” in the day part of the calculation. This is an easy way of putting the last day of the month before.
  • I used a Let () statement to avoid calling the same function multiple times, but this isn’t necessary.

Gather the Data

My server script will then gather the data and return it as a result.

I set my script parameter into a variable (to save calling it multiple times) and then extract the information as needed. I would then use the information to build the results. I’m skipping a few steps below, but the gist of it is:

Get the start date from the JSON so I can perform a search, using JSONGetElement.

Set Variable [ $invoicePieStartDate ; Value : JSONGetElement ( $jsonParameters ; "invoicePie.startDate" ) ]

Set up some blank objects, so we can add our results to them, using JSONSetElement.

Set Variable [ $JSON ; Value : JSONSetElement ( "{}" ; [ "invoicePie" ; "" ; 3 ] ; [ "invoiceBudgetChart" ; "" ; 3 ] ) ]

Set the data – I’d normally use a loop.

Set Variable [ $JSON ; Value : JSONSetElement ( $json ; "invoicePie." & $invoiceType ; Round ( $sum / 1000 ; 1 ) ; 1 ) ]

Might result in something that looks like:

{"invoicePie":{"Overdue":"1.2","Paid":"1500"}}

And some column chart data:

{"invoiceBudgetChart":{"January":{"actual":"0","budget":"1"}...

This example will create an element within ‘invoicePie’ with the $invoiceType and $sum (as set in the script). These are the values to show in the chart.

Our Bar chart will have two data series, and is based on months. So each month will have two elements – Actual and Budget

Populate the Chart​

First let’s assume the Script has run to produce the results, and the results have been put in to a variable – $$dashboardChartJson

NOTE: Make sure the Data Source > Chart Data is set to ‘Current Record (delimited data)’.

Pie Charts are very simple. JSONListKeys will get the labels, JSONListValues will get the data. The built in function returns the results in ‘carriage return-delimited data’. Just what we need for our chart.

Category Labels > Specify Calculation…

JSONListKeys ( $dashboardChartJson ; "invoicePie" )

In this example gives the result: Overdue¶Paid i.e. ths $invoiceTypes from our PSOS script.

 Slice Data > Specify Calculation…

JSONListValues ( $dashboardChartJson ; "invoicePie" )

From the example: 1.2¶1500

For the Column Chart, there is a bit more going on. In this example, we know that the X-Axis (Horizontal) will consist of month names, but, we could be collecting that from the result.

Remember, we are using carriage return-delimited data. Using List ( ) will put our values in to the correct format.

List ( "JAN" ; "FEB" ; "MAR" ; "APR" ; "MAY" ; "JUN" ; "JUL" ; "AUG" ; "SEP" ; "OCT" ; "NOV" ; "DEC" )

For our Y-Axis (Vertical) we will pull the result from the JSON. When the result was prepared on the PSOS script, I Built the JSON to have an object for each month, with ‘actual’ and ‘budget’:

{"invoiceBudgetChart":{"January":{"actual":"0","budget":"1"}

Actual and Budget would become our a ‘Series’ each.

NOTE – When getting the JSON, I need to make sure that there are Keys have a value. It is easiest to handle this on the script when preparing the result, showing a “0” if needed.

We need to make sure that the Data is in the correct order, to match up with our month names, instead of alphabetical.

Series 1 – Budget:

Let ( [
_startDate = JSONGetElement ( $dashboardChartJson ; "invoiceBudgetChart.startDate" ) ;
_month = Month ( _startDate ) ;
_year = Year ( _startDate )

_jan = JSONGetElement ( $dashboardChartJson ;"invoiceBudgetChart." & MonthName ( _startDate ) & ".budget" );
_feb = JSONGetElement ( $dashboardChartJson ;"invoiceBudgetChart." & MonthName ( Date ( _month + 1 ; 1 ; _year )) & ".budget" ) ;
...
];
List( 
If ( IsEmpty ( _jan ) ; "0" ; _jan ) ;
If ( IsEmpty ( _feb ) ; "0" ; _feb ) ;
...
))

Series 2 – Actual:

Let ( [
_startDate = JSONGetElement ( $dashboardChartJson ; "invoiceBudgetChart.startDate" ) ;
_month = Month ( _startDate ) ;
_year = Year ( _startDate )
_jan = JSONGetElement ( $dashboardChartJson ;"invoiceBudgetChart." & MonthName ( _startDate ) & ".actual" );
_feb = JSONGetElement ( $dashboardChartJson ;"invoiceBudgetChart." & MonthName ( Date ( _month + 1 ; 1 ; _year )) & ".actual" ) ;
...
];
List(
If ( IsEmpty ( _jan ) ; "0" ; _jan ) ;
If ( IsEmpty ( _feb ) ; "0" ; _feb ) ;
...
))

Why this method?​

  • Speed – a big reason to do this. The data can be built on the PSoS script as part of the layout load or even part of the database load. We aren’t relying on calculations being performed on the local machine.
  • Performance – Once all the data is displayed, it won’t change, and the database won’t be checking to see if it has changed (unless the user requests a refresh).
  • Easy – less relationships, less complicated calculations. The data can be built in any way, so you don’t (necessarily) require the relationships that you otherwise would.

Alternatives​

Using Web Viewers is a great way of opening up our options. I have some experience using Google Charts, but there are more options out there.

Google Charts give us a lot more customisation options and are a lot more dynamic.

With no previous experience I was able to get some charts working. More about this in Charts – Part 2 (coming soon).