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…

2-26-2014 8-31-27 AM

…with help from these four demos:

A couple thoughts before we get started…

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

2-26-2014 8-27-30 AM

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…

2-26-2014 3-05-26 AM

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

2-26-2014 3-11-55 AM

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:

2-26-2014 9-08-47 AM

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.

2-26-2014 11-06-25 PM

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.

2-26-2014 11-00-49 PM

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.

2-26-2014 11-04-52 PM

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…

2-27-2014 3-51-28 PM

…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.,

2-26-2014 11-12-27 PM


2-26-2014 11-23-04 PM

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.)

2-27-2014 11-18-28 AM

The first panel on the slide control shows the data…

2-27-2014 11-30-37 AM

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

2-27-2014 11-33-32 AM


2-27-2014 11-36-10 AM

Demo 3: Column Chart on Found Set

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

2-27-2014 12-10-38 PM

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).

2-27-2014 11-46-43 AM

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

2-27-2014 11-49-19 AM

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…

1-19-2014 12-38-56 AM

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

2-27-2014 3-38-01 PM

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.

2-27-2014 3-33-36 PM

Here it is under the hood:

2-27-2014 3-40-33 PM

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.

3 thoughts on “FM 13: Simple Column Charts

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s