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.
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.
Hi Fabrice,
Completely agree re: the Nobel Prize nomination.
Kevin
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.
Gosh darn it Jonathan, there you go again being practical. Care to share an example?
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.
Nice. Thanks Fabrice.
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])” )
)
Hi Lisette,
That’s a great use of CustomList.
Regards,
Kevin
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
Bonjour Agnès,
Regards,
Kevin
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.
Hi Kevin,
Thank you for the thorough and informative look at CustomList! I looked up this post today after we talked about it, but I definitely think I had this bookmarked (and returned to it OFTEN) when I first learned about CustomList and began using it. I remember the Sum Matching Values example.
Reading this post helped me open up my understanding of the different ways I could use CustomList, like using it to return only certain values from a list stored in a variable (when the list items were passed in as a parameter or they were in a related field).
Thank you for sharing this with the citizens of the internet, we appreciate it! :)
And yes, Nobel Peace Prize for Agnès, if that hasn’t been rectified yet!
Thank you Yelena. Your comment just made my day.