CustomList

Earlier this year, in Virtual List Reporting, I used a custom function written by Agnès Barouh called CustomList to generate a couple basic lists, and then remarked…

Well today I’m going to attempt to make good on that assertion. In case you aren’t already familiar with CustomList, it allows you to iteratively generate and/or parse lists using the full power of the FileMaker calculation engine. CustomList can process up to 500,000 rows — it is non-recursive and very fast — and today we’re going to use it to solve a variety of problems, both real world and theoretical. [Actually, the row limit can be adjusted, and it varies by platform — for full details see the CF definition.]

Today’s article features four demos…

• Demo 1:  Lists Based on Found Set
• Demo 2:  Sum Matching Values
• Demo 3:  Date Filtration
• Demo 4:  Prime Number Generator

…as well as a number of examples that don’t require a demo to illustrate their point.

Basic Usage

First let’s review some basics. CustomList takes three arguments…

CustomList ( Start ; End ; Function )

• Start:  a positive integer
• End:  a positive integer >= Start
• Function:  code that will be executed iteratively n times where n = End – Start + 1
  — this code must be wrapped in quotation marks.

Part of what makes CustomList so powerful is the [n] iterator, a magical little counter that starts with 1 and increments by 1 for each iteration between Start and End. Note that you are not required to use [n], e.g., you can generate a block of UUIDs like so:

But most of the time you will want to use [n] because it enables a row to be aware of its iteration number. While [n] by itself increments by 1, you can use any combination of FileMaker functions and operators to achieve whatever incrementation you wish, e.g., produce the first ten square numbers, or powers of 2.

The [n] can also appear explicitly in the results, in this case with zero-padding where appropriate. Note that since the code is wrapped in quotation marks, any quotation marks that appear within the code must be escaped.

Initial Encounter

My first real-world use of CustomList, about eight years ago, was to populate the x-axis labels of an xmChart with 88 weeks’ worth of dates (to keep things from getting too crowded, every other week is displayed, i.e., 44 values).

The idea is to start with May 1st in one year and then increment by 14 days through the end of the following year (the year is intentionally not shown because the purpose of the chart is to overlay data from multiple years for comparison purposes).

//   note: the years are irrelevant... but avoid leap years
Substitute ( 
     CustomList ( 1 ; 44 ; "Date ( 5 ; 1 ; 2009 ) - 14 + [n] * 14" ) ; 
     ["/2009" ; ""] ; ["/2010" ; ""] 
)

Okay, we’ve seen some basic examples, now let’s dive into the demos.

Demo 1: Lists Based on Found Set

Here the challenge is to produce a list of students and their grade level, mirroring the found set and current sort order (or lack thereof). Note that we want to list students by Last name and then either their First name or (if it is populated) their Preferred name.

Here’s the calc (unstored, text) for standard mirroring…

…and here’s the calc for reverse mirroring; the main difference is the 2nd argument passed to GetNthRecord, which allows us to iterate backward, i.e., from End to Start.

If you’re wondering about the use of the “script” variable ($x), it’s quite common to use Let to declare variables before invoking CustomList, but CustomList won’t see them if you use a standard “Let variable”, i.e., a variable without $ or $$ prepended to its name.

This note from the custom function definition explains that each row created by CustomList is generated from within its own Let clause — my nickname for them is “Let bubbles” — and therefore is oblivious to standard “Let” vars declared outside of that clause/bubble.

Basically, CustomList() does two things :
1/ Transforms your formula in a literal chain :
CustomList ( 1; 4; "GetNthRecord ( Field ; [n])") therefore becomes
"Let ([ CLNum = 1 ] ; GetNthRecord ( Field ; CLNum )) & ¶ &
Let ([ CLNum = 2 ] ; GetNthRecord ( Field ; CLNum )) & ¶ &
Let ([ CLNum = 3 ] ; GetNthRecord ( Field ; CLNum )) & ¶ &
Let ([ CLNum = 4 ] ; GetNthRecord ( Field ; CLNum ))"

2/ Evaluates this chain.

So if you want CustomList to “see” a variable declared outside of the CF, use a $var or $$var. You should also clear the variable when you’re finished, as per the Let ( $x = "" ; "" ) at the end of the Reverse Mirror calc… a ) to free up memory, and b ) to avoid possible unanticipated negative consequences.

Interlude 1: A replacement for MiddleValues

Have you ever been annoyed by the dangling return FileMaker “helpfully” appends when you invoke the MiddleValues function? Well try this instead and no more pesky problem. Given a variable $theList populated with A¶B¶C¶D¶E¶F¶G¶H¶I¶J¶K…

And of course this technique can also replace LeftValues, and with minimal tweaking be adapted to replace RightValues (functions which also append a dangling return) as well.

Demo 2: Sum Matching Values

This came about in response to a challenge posted to one of the FileMaker discussion forums. Given two lists with a one-to-one correspondence between list elements, how to sum all values in the second list corresponding to a value chosen from the first list.

Here’s how it’s done.

Explanation

1. Combine the elements of the two lists into a single list

2. Generate a new list containing just the rightmost word from the rows where the leftmost word in the row is the specified character… e.g. for “C” the result = 7¶7¶7

3. Using Substitute, transform the preceding to 7+7+7

4. Evaluate ( 7+7+7 )

Interlude 2: A better Factorial function

FileMaker’s native Factorial function maxes out at 212 (however, Jason DeLooze provides an excellent work around in Fun With Factorials). With CustomList you can directly calculate factorials as high as 374… and you can always apply Jason’s work around if you need to go higher.

Explanation

1. Generate a de-incrementing list of numbers: 361¶360¶359¶358…

2. Using Substitute, transform the preceding to 361*360*359*358…

3. Evaluate ( 361*360*359*358… )

[Actually, it would be simpler and equally effective to increment upward from 1…361, but it feels more factorial-like to de-increment.]

Demo 3: Date Filtration

The ultimate goal of this demo is to produce a value list in a pop-up menu displaying Monday dates in correct date order that occur during the school year (i.e., between the school year “Begins” and “Ends” dates).

However, we need to omit Monday dates that occur during week long breaks, which as you can see if you examine the “No School” portal, occur five times during the school year (two of them consecutively in the second half of December).

The first task is to produce a list of all Mondays (valid or otherwise).

Next we need to generate a list of Mondays to exclude…

…which is calculated like so:

And, finally, we make use of Bruce Robertson’s AntiFilterValues to “subtract” the second list from the first, so that only valid Mondays remain (a Monday is considered to be valid if there is school any day during that week). Here is the valid Monday date list…

…and here’s how it’s derived.

Interlude 3: CustomList invoking itself

As we near the end of today’s CustomList odyssey, I want to show a couple examples of the CF calling itself (i.e., generating a list composed of other lists). In this first example, the inner lists are comma-delimited.

Here the goal is to produce two groups of UUIDs, each numbered from 01 – 03.

Come to think of it, the preceding can also be achieved with a single instance of CustomList.

But returning to the topic of CustomList invoking itself, our final demo today uses this technique to generate a comma-delimited list of prime numbers.

Demo 4: Prime Number Generator

The basic idea: use the aforementioned AntiFilterValues to push a range of whole numbers through a lazily constructed Sieve of Eratosthenes. There is definitely room for improvement here, and I am open to suggestions.

Explanation

1. Generate a list of all whole numbers between 2 and the upper limit

2. Generate a list of all composite (non-prime) numbers between 2 and the upper limit

3. Filter them using AntiFilterValues, and substitute hard returns with commas

Conclusion

My goal today has been to convey some sense of the breadth and depth of what can be accomplished via CustomList. The particulars of my examples aren’t nearly as important as the general idea that this is a powerful and flexible tool worth having in your FileMaker tool box. If you’ve solved an interesting problem using CustomList, I encourage you to post a comment here, explaining what the problem was and how you solved it. Good luck and Happy FileMaking.