Array Charting, part 3

[This article assumes that the reader is familiar with part 1 and part 2 of this series.]

The other day, I ended part 2 of this series by looking at the y-axes of a variable-array-based chart, and commenting, “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.”

Well, now I’m thinking that “another day” has arrived.

Having done so much work at the script level to parse the data into a variable array, it seems wrong, or lazy at any rate, to embed the above logic in Chart Setup. So in today’s demo, line-chart-simplified, the “heavy lifting” to populate the Y Axes takes place at the script level, not in the chart schema. As per the code at the left, chart objects are easier to reuse when their axes are based on variables, rather than dedicated calculations or hard-coded values.

And, having made that mental leap, the same should hold true for the X Axis, shouldn’t it? A simple variable is more “portable” than the dedicated calculation I used in parts 1 & 2.

And while we’re at it, let’s not forget Format Chart…

Okay, sounds great. The script will populate all the variables… and in most cases it’s easy enough to imagine how… but what about those $chartLine[n] variables? Well my thought process went something like this: Evaluate could help eliminate the tedium of pointing the List function at 52 reps for each $array_xx[n] variable. It would be nice to be able to say, “For reps 1 through 52 of $array_xx, give me a return delimited list of values.” Hmm… that’s going to require recursion, i.e., a custom function — hey, I wonder if someone’s already written one?

So, I headed over to Brian Dunning’s custom function repository, searched on “repeating variable”, and promptly discovered a sweet little work of art called JoinRepeatingVariable by Sam Barnum of 360Works. While studying the internal code for this CF would be educational, we don’t need to understand the inner workings to reap the benefits.

I think this point is worth making explicitly: custom functions (a.k.a. CFs) allow you to ignore the “how”, and stay focused on the task at hand. It’s not that the “how” isn’t worth knowing, but you can defer that exploration until a more convenient time.

The syntax for this CF is:

JoinRepeatingVariable ( variableName ; delimiter ; startIndex ; endIndex )

…and, incidentally, both startIndex and endIndex are optional. I was expecting this CF to behave like the List function and ignore empty reps, but it doesn’t do that. Let’s assume that we’ve populated three variables as follows: $$var[1] = 111, $$var[2] = 222, and $$var[5] = 555, and that $$var[3] and $$var[4] do not exist.

• List ( $$var[1] ; $$var[2] ; $$var[3] ; $$var[4] ; $$var[5] )
  will return 111¶222¶555, but
• JoinRepeatingVariable ( "$$var" ; ¶ ; 1 ; 5 )
  will return 111¶222¶¶¶555

And if we clear out $$var[1] and $$var[2], so that only $$var[5] contains data…

• List ( $$var[1] ; $$var[2] ; $$var[3] ; $$var[4] ; $$var[5] )
  will return 555, whereas
• JoinRepeatingVariable ( "$$var" ; ¶ ; 1 ; 5 )
  will return ¶¶¶¶555… that’s right, four empty lines and then 555

So, by using JoinRepeatingVariable instead of the List function, it’s no longer necessary to pre-populate all possible reps with zero, because JoinRepeatingVariable enumerates missing reps within the specified range.

Here’s how this plays out in the demo file. The earliest entry in the web visits table is dated March 1, 2006. FileMaker 11 charting doesn’t give us any way to start a chart line in “mid-air” so to speak — you can end in mid-air, but you can’t begin there — so I can’t simply start the chart line for the year 2006 on week #9, even though I don’t have any data for weeks 1 through 8. [Update 6/24/2013: This was fixed in FM12.]

When the chart generation script has processed all records for the year 2006, $array_01[9] through $array_01[52], will be populated with data, but $array_01[1] through $array_01[8] will not exist.

The $chartLine variables are populated via the code at the right, and since reps 1-8 don’t exist for $array_01, eight empty lines will appear in their place, as per the tool tip below.

(To get the local variables to appear in the data viewer, I chose to invoke the script debugger and pause it just before the chart script finished running.)

And you can see the chart line for the year 2006 at the bottom of the above screen shot.

One final observation: since we only have data for the first half of the year 2011, the highlighted code at left ensures that for the current year, endRep will equal 26 instead of 52 as it normally would. The $finalRep variable is set by the chart generation script as per the highlighed line below:

And the chart line for the year 2011 ends suspended in space, rather than plunging to zero as it would if 52 had been used as the endRep.

Well, arrays are endlessly fascinating, but I think I’ve said all I need to say on the subject for a while. Next time we’re going to take a look at a technique called Magic Key, which is quite possibly the best-kept secret in the FileMaker development universe.