FM 13: Simple Column Charts

Two things I appreciate about FileMaker charting are its user-friendliness and its flexibility. I do a fair amount of charting with xmChart, but when it’s time to “go native”, my typical approach is to load up some variables with return-delimited lists of data, and use those as my data source.

[Note: click here to see all FileMaker Hacks articles with charts.]

An advantage of basing charts on variables is that it allows charts to be context-independent, reusable objects. But a disadvantage can be a loss of simplicity, and missing out on some of the ease-of-implementation that FileMaker charts offer “out of the box”. So today we’re going to take a look at some simple FM 13 column charts that do not rely on variables…

…with help from these four demos:

• FM 13 Column Chart on Current Record, v1
• FM 13 Column Chart on Current Record, v2
• FM 13 Column Chart on Found Set
• FM 13 Column Chart on Related Data

A couple thoughts before we get started…

1. All FileMaker charts use one of these three data sources:

2. Recent versions of FileMaker make it super simple to build a “current found set” chart even if you have absolutely no idea what you’re doing. Just a) sort on any field, and then b) choose “Chart by” + the field you just sorted on…

…and you will be handed a chart on a silver platter, with zero stress and zero learning curve.

We’ll examine found set charting in demo 3, but first we’re going to take a look at a couple demos based on delimited data in the current record.

Note: None of the charts in today’s demo files were created using the above method; instead they began life in layout mode, like so:

Demo 1: Column Chart on Current Record, v1

At the risk of stating the obvious, the data source for this demo and the next one is the current record.

Have you ever wondered what the absolute minimum requirement is to produce a FileMaker chart? If so, your wondering days are over, because all it takes is a pair of return-delimited lists: one for the X axis and one for the Y axis. The lists might be the result of calculations, they might be variables populated via script (or via the Let function)… or they might simply be return-delimited values sitting in dedicated text fields, which is what you’ll find in this demo.

Use the slide control to view 15 different variations on the above chart. At any point you can pop into layout mode and see what’s going on by double clicking on the chart object.

Working in Chart Setup, and repeatedly encountering the “Earth” chart style, I get the eerie sensation of having somehow wandered into a Giorgio Morandi painting…

…but I digress.

If you work your way through the slide control from left to right, you’ll see that each iteration of the chart is slightly more refined (or at least a bit different) than its predecessor, and the chart title always tells you what changed, e.g.,

…or…

Demo 2: Column Chart on Current Record, v2

The main difference between demo 1 and demo 2 is the addition of multiple data “series” for the Y axis. (I chose to use repeating fields for the Y labels and the Y values, so the repetition number corresponds to the “series” number.)

The first panel on the slide control shows the data…

…and the remaining twelve panels show various iterations of the column chart. E.g.,

…or…

Demo 3: Column Chart on Found Set

As implied by its name, the data source for this demo is the current found set.

This time around we don’t have multiple iterations — I just chose some options that made sense to me and placed the chart in the footer of this list view. Note that “found set” charts need to be sorted — essentially they’re graphical summary reports (which also need to be sorted).

Here’s what’s going on behind the scenes:

Incidentally, here’s the code to generate the unique state count in the chart title. (There are other ways to do this of course, but this is the approach I chose.)

ValueCount ( cfUniqueValues ( customers::summary_list_state ) )  
     & " States in Found Set"

1. The summary field returns a list of every state entry in the found set, including duplicates
2. The custom function removes the duplicates (if there were more than 10,000 records in the found set I would hit the CF recursion limit and would have to come up with a different approach)
3. The ValueCount function counts the unique entries

Update 28 Mar 2014: On reflection, I strongly prefer this method to count the unique states.

Demo 4: Column Chart on Related Data

In this demo we have a very basic sample invoicing system, architected like so…

From our customer layout, we want to chart related invoices, like so:

I likened the chart in the previous demo to a “graphical summary report”; similarly, the chart in this demo can be thought of as a “graphical portal”. In fact, FileMaker forces you to specify the relationship in chart setup, in much the same manner as you would for a portal.

Here it is under the hood:

And here’s the code for the X-Axis title:

"Count: " & Count ( invoices::ID ) & 
"       Average: " & cfAddDollarFormat ( Average ( invoices::total ) ) & 
"       Total: " & cfAddDollarFormat ( Sum ( invoices::total ) )

Well, that’s about it for today. In an upcoming article we’ll look at combining FileMaker charts with the Virtual List technique, and why that might be worth doing.