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…

2016-09-19_000003

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…

…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 whole number
  • End:  a whole number >= 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:

2016-09-19_114802

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.

2016-09-19_120144

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.

2016-09-19_161728

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

2016-09-19_122131

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…

2016-09-19_135107

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

2016-09-19_135810

If you’re wondering about the use of the “local” 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…

2016-09-20_000111

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.

2016-09-19_160617

Here’s how it’s done.

2016-09-23_170350

Explanation

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

2016-09-19_173617

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.

2016-09-19_192743

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

2016-09-19_174454

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

2016-09-19_181312

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

2016-09-19_181705

…which is calculated like so:

2016-09-19_182119

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…

2016-09-22_093635

…and here’s how it’s derived.

2016-09-24_025127

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.

2016-09-20_000000

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

2016-09-19_191444

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

2016-09-19_191911

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

2016-09-19_184932

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.

11 thoughts on “CustomList

  1. Fabrice Nordmann

    Rare have been the days during the last 8 years or so where I haven’t used it, either to implement in a solution or to debug.
    If there was a FileMaker Nobel Prize, the author would definitely have my vote.

    Reply
  2. instig8r

    Yeah, the oft-forgotten Nobel Prize in FileMaker Geekery. But, sorry, I’m not on the committee, so I can’t help A.B. win. Funny enough, they never call me to ask my opinion either. But, if they did, I would certainly tell them they would be stupid not to give it to Agnes.

    I use it a lot to do cool things that aren’t nearly as math-oriented as Kevin does though.

    Reply
      1. Fabrice Nordmann

        Just an example yesterday.
        Two strings that looked similar and should have been, but weren’t.
        stringA=stringB returned True, but Exact ( stringA ; string2 ) was returning False.
        I suspected an invisible character to be the cause.
        So I just put this in the DataViewer:
        CustomList ( 1 ; length ( stringA ) ; “Code ( middle ( stringA ; [n] ; 1 ))” )
        then did the same for string B (I could have done it in one go), and could compare easily, character by character, which was the naughty one.

        Reply
  3. lisettewilson

    Thanks, somehow I’d never used this before, and it helped me solve a problem today.

    When duplicating a record and specific sets of line items, two sets of line items are also tied together by the primary key of one line item being a foreign key in the other line item. These need to be linked again in the duplicated line record and line items.

    So, when looping through and duplicating the set that would be the foreign key in the other set, I made two lists in variables. $oldIDs and $newIDs.

    When looping through the other set, I made note of the old ID used as the foreign key before duplicating the line item. Then did a needle and haystack search to find out the row number of that value in the old list, so that I could pull the corresponding number from the new list, to be used on the duplicated record.

    I’ve used another function for this in the past, but didn’t happen to have it in this database.

    Solved it with this multifunction tool via…

    GetValue ( $newIDs ;
    CustomList ( 1 ; ValueCount ( $oldIDs ) ; “Case ( GetValue ( $oldIDs ; [n]) = $thisPartID ; [n])” )
    )

    Reply
  4. Agnès

    Hi Kevin, Hi All !

    I am pleased to read CustomList still help you in 2016….
    It serves me a lot to debug or get quickly a list or calc in the Data Viewer
    She can replace many occurrences, and it serves me to edit contracts and <> since it will pick information everywhere
    I used only when a native solution is impossible or too constraining
    but it does not help me to improve my English

    thank you for your article, thanks to use it !

    Regards

    Agnès

    Reply
  5. Ben Miller

    Hi,
    Kevin – thank you for this article, in fact all of them!
    Anges – Merci bien pour CustomList() c’est formidable!

    I use CustomList() to solve problems often, as Agnes notes, where there is no nice way to use native FileMaker. One thing I use all the time is an abstracted script to set any number of fields with any number of values, used in place of Set Field, where field names and values are specified in the script parameter using #(). CustomList() solved the problem of having to enter multiple parameters in a specific order.

    Reply

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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