Array Charting, part 1

In recent postings we’ve looked at…

• Using a lookup to populate or clear an array (Lookups and Repeaters)
• Determining the position of an item in a list (ValuePosition)
• Parsing subsummary values from a found set (Fast Summaries)

While these three techniques may not appear to have much in common, all of them are used as building blocks in today’s demo, line-chart-from-field-array. Our data set is a table of web site visits, by week and by state, beginning in March 2006 and running through June 2011.

Wouldn’t it be nice to to use FileMaker 11’s built-in charting capabilities to produce a line chart showing weekly visits per year? And of course we would want to be able to do this for any arbitrary found set (e.g., California visits in 2009 and 2010), not just the entire table.

Incidentally, if you’re looking for an overview of FileMaker 11 charting, here are a couple PDFs: Introduction to Charting and Using FileMaker 11: FileMaker Charts

When you go into Chart Setup, the two most important items are a) the Chart Type…

…and b) the Use Data From setting.

At first blush, it would seem that the current found set would be the proper basis for our chart, but what if we have gaps in our data (e.g., one or more missing weeks)? FileMaker’s charting tool is not psychic… it will only report on data that is present. This is why we’re going to load up an array with all zeros first…

…and then parse the data for each year and week into the appropriate “cell” (intersection of column and row), where columns represent years and rows represent weeks. This way, gaps in our data set, such as the missing first eight weeks of 2006, are assigned zeros and can be charted properly.

I like to use the term array because it sounds geeky and slightly arcane, but there’s nothing mysterious about it… it’s simply a matrix of values, where each entry has a unique address. In this case, the array is based on 12 repeating global number fields, each of which has 100 reps — why 100? The number isn’t important, as long as it’s more than you will need… in this case 52 would suffice, since each rep number corresponds to a week number. Also, why are there 12 columns? Because that’s the maximum number of data series (i.e., chart lines) that FileMaker can accommodate.

Here’s an overview of the main script actions:

1. Populate the array with zeros
2. Sort the found set by year and week
3. Build a list of unique years for the current found set
4. Parse the web visits into the array
5. Clear future zeros for current year (if it’s not a complete year)
6. Display chart

Item 1 has already been mentioned above (I also discussed the technique in my recent Lookups and Repeaters article), and item 2 is necessary preparation for items 3 and 4. So what’s going on with item 3?

Our table contains records from the years 2006 through 2011, but of course there’s no guarantee that all those years are present in whatever found set the user happens to be in. We need a way to build a unique list of just the years that are actually present in the current found set, and the Fast Summary technique is a great way to accomplish this. Not only will this list be crucial for subsequent steps, but the chart object itself will rely on it (to render the legend), so we store the year list in a global variable called $$yearList. If our current found set contains visits from 2008-2010, then $$yearList will contain:

2008
2009
2010

Item 4 is the where the data actually gets parsed into the array, again using the Fast Summary technique. The $column variable is populated using the Value Position custom function — it compares the year of the current record against $$yearList, and returns a 1 if it’s the first value, 2 if it’s the second, etc.

Note the use of Set Field By Name; the fields in the Array table are named array_01, array_02, up to array_12, and prior to FileMaker 10 we would have had to use separate Set Field steps for each of them. But thanks to Set Field By Name, we can dynamically construct the field name using the $column variable (we’ve been able to dynamically specify the repetition via Set Field since FileMaker 7). The down side is that the routine will break if we rename the fields in the array table, so let’s not do that.

Since the current year most likely will not contain 52 weeks’ worth of data, it would be misleading to have the chart line for this year plummet to zero. Instead we want it to simply break off in mid-air (so to speak), and that’s what item 5 accomplishes for us.

This routine simply picks up where the previous one left off, using the final $column and $rep values as a starting point, and clearing out all the “future” zeros in that column.

One more visit to Chart Setup shows what’s going on behind the scenes — the 52 week numbers of the X axis are simply defined as List (1;2;3…52) — this means, incidentally, that any array rows beyond number 52 will be ignored. And for the Y axis, there’s no harm in specifying array columns for non-existent data series; they will not be drawn because the chart object is smart enough to ignore a data series containing all zeros.

And at last, here is the fruit of all our efforts (reduced to fit on this page; click the image to see it at 100% magnification).

Finally, if you’re wondering how the legend gets populated, we use GetValue to parse items 1 through 12 from $$yearList.

In my next posting we’ll dispense with the field-based array, and use one based on variables instead.