Array Charting, part 2

Print PDF

Last week we looked at building a FileMaker 11 native chart based on data that has been parsed into a field-based array.

I happen to like field-based arrays, for reasons I’ll get to in a minute, but is a field-based array really necessary to generate the above chart?

Absolutely not, and today we’re going to look at a method to create the same chart as in part 1, but using a variable-based array. Feel free to follow along in today’s demo file, line-chart-from-local-variable-array, if you are so inclined. Also, if you haven’t yet read last week’s article, I strongly recommend you do so before proceeding.

To briefly recap, our data set is a table of web site visits, by week and by state, beginning in March 2006 and running through June 2011. To parse this data into an array, we use the Fast Summary technique, which walks the found set, grabbing one summarized value per week for each year.

Avantages of using a field array

1. When you point the List function (or any aggregate function such as Sum or Max)  at a repeating field, it sees all repetitions — whereas when you do the same for a repeating variable, you must specify each rep explicitly. Which would you rather type?
   1. List ( repeatingField ), or
   2. List ($array_01[1] ; $array_01[2] ; $array_01[3] ; $array_01[4] ; $array_01[5] ; $array_01[6] ; $array_01[7] ; $array_01[8] ; $array_01[9] ; $array_01[10] ; $array_01[11] ; $array_01[12] ; $array_01[13] ; $array_01[14] ; $array_01[15] ; $array_01[16] ; $array_01[17] ; $array_01[18] ; $array_01[19] ; $array_01[20] ; $array_01[21] ; $array_01[22] ; $array_01[23] ; $array_01[24] ; $array_01[25] ; $array_01[26] ; $array_01[27] ; $array_01[28] ; $array_01[29] ; $array_01[30] ; $array_01[31] ; $array_01[32] ; $array_01[33] ; $array_01[34] ; $array_01[35] ; $array_01[36] ; $array_01[37] ; $array_01[38] ; $array_01[39] ; $array_01[40] ; $array_01[41] ; $array_01[42] ; $array_01[43] ; $array_01[44] ; $array_01[45] ; $array_01[46] ; $array_01[47] ; $array_01[48] ; $array_01[49] ; $array_01[50] ; $array_01[51] ; $array_01[52] )
2. You can easily place repeating fields on a layout and view them as per the “array” screen shot above — this can come in very handy when troubleshooting.
3. Clearing or zeroing-out the array is quite simple (see Lookups and Repeaters)
4. If you need to generate multiple charts concurrently, simply change the storage type of the repeating fields in the array table from global to regular. Then create and populate a new array record for each chart. (I’m not saying you can’t do multiple charts with variable arrays, just that it will be more convoluted.)

Advantages of using a variable array

1. No need to tamper with table or field level schema
2. You create your array on the fly via script at run time. This is sometimes referred to as “dynamic instantiation”.
3. You only define the “cells” (columns and rows) you actually need
4. Clearing or zeroing-out the array is pretty easy once you get the hang of dynamic variable instantiation (see below for more on this)

OK, enough theoretical discussion… what’s different about today’s demo?

The big difference is it doesn’t have an Array table. It doesn’t need one since the array is built in memory. There are three main differences in the script that generates the chart:

a) The “zero out the array” segment. See last week’s posting for why the zeroing out is important.

b) The parse segment. Instead of pushing summarized data into a certain row (repetition) of a certain column (field), we push it into variables that we declare on the fly, i.e., “instantiate dynamically” (more on this below).

c) The clear partial year segment. At the risk of becoming repetitive, see last week’s posting for why this is necessary.

Note that all three segments above have a comment re: the lack of a “Set Variable By Name” step. Although FileMaker provides a “Set Field By Name” script step, it does not provide a direct method to dynamically declare the name of a variable.

Note that I said there isn’t a “direct” method to accomplish this… but it can be done indirectly.

Creating Variables on the Fly

There’s something cool and slightly miraculous about being able to dynamically name and populate variables at runtime. In the code below we don’t care about $x at all — it’s just a convenient way to access the calculation engine so we can invoke the Evaluate and Let functions.

We’ll come back to that in a minute, but first let’s consider a much simpler example. If we know the name of the variable we want to declare, but want to specify the repetition dynamically, it can be accomplished in a very straight-forward manner, like so:

This gives us a variable named $array_01[x] where “x” represents whatever number is currently in the $rep variable. The following also achieves the same result, but normally we wouldn’t bother since it’s more work with no additional benefit. But it does show that a variable can be defined via Let, with the rep specified dynamically — in this example, via a variable, $rep, but Get(RecordNumber) or any calculated expression would work.

However, the technique breaks down if we try to dynamically specify the name of the variable itself. In the above example, we’re telling FileMaker to define a variable named $array_o1 with whatever repetition number happens to be sitting in the $rep variable.

But we want to declare a variable named $array_xx where “xx” could be anything from “00″ to “12″ and is based on the value sitting in the $column variable (padded with a leading zero where appropriate), and to pull that off we need to bring out the heavy artillery, i.e., Evaluate.

It makes my head hurt, but it actually works. And to wrap it all up, here’s the Chart Setup.

Note that all 52 variables for each of the 12 data series are individually enumerated. I’m thinking that there is a smarter way to go about this, but that is code for another day.