New in FM 18: SetRecursion and While

Author’s note: Inevitable comparisons will be drawn between the new While function and the venerable CustomList custom function, which I wrote about at length several years ago. Preliminary testing indicates that CustomList is faster under certain circumstances and While is faster under others. In my opinion, both of them belong in your developer tool kit.

Today we’re going to take a look at two new functions introduced in FileMaker 18: SetRecursion and While, and you can follow along in today’s demo files if you are so inclined.

Demo files: While-Sandbox.zip and Maclaurin-Series.zip

SetRecursion ( expression ; maxIterations )

Background: since the release of FileMaker 7 in 2004, there have been two recursion limits: 10,000 iterations for standard recursion, and 50,000 iterations for tail recursion. With the introduction of FM 18 this has changed in two ways:

1. No more 10K limit; the default limit is now 50K iterations regardless of recursion type.

2. This limit can now be increased or decreased using SetRecursion. I have not seen any documented upper limit for “maxIterations”, and have successfully gone as high as 500,000,000 (yes, five hundred million) iterations in my own experiments — more on this below.

Okay, we’ve touched on “maxIterations”… what can an “expression” be? An expression can be any valid calculation syntax, but practically speaking there’s no point in using SetRecursion, unless your expression utilizes recursion, i.e., contains one or more recursive custom functions and/or While statements.

Here’s an example: say you have a tail-recursive custom function that generates a JSON array for a given found set. Let’s call it “FoundSetToArray”, and to keep things simple let’s assume this CF requires no arguments. If the CF iterates once per record, then in FM 17 and earlier, it will return a “?” when your found set is >= 50K records.

But starting in FM 18, your CF can accomplish its task regardless of found set size, like so:

SetRecursion ( FoundSetToArray ; Get ( FoundCount ) )

Note: with this function we now have a virtually-unlimited length of rope to hang ourselves with. Bear in mind that there is no free lunch here, in terms of either time to execute or the possibility of exceeding available memory. Proceed w/ reasonable expectations, a bit of caution, and a healthy dose of common sense.

While ( [ initialVariable ] ; condition ; [ logic ] ; result )

The While function performs a task (“logic”) over and over as long as the stated condition remains true; when this condition is no longer true, the result is displayed.

Prior to FileMaker 18, if you wanted to harness the power of recursion in the calculation engine, you needed to define a custom function. Now, thanks to the While function, this power is available directly within the calculation engine, in a clear-cut and easy-to-work-with implementation.

Update 21 July 2019: Technically, what we have in this function is iteration, rather than recursion (i.e., the function doesn’t call itself), but the benefit is the same. Thank you H0nza Koudelka for pointing this out.

Initially you may find the structure of the function to be a bit disorienting, but if you’ll take the time to study some examples and construct a few statements in the data viewer, things will soon make sense… and you will find that the While function is logical and extremely powerful, as well as a tremendous time saver vs. writing and debugging a custom function.

Basic Example(s)

Here are two nearly-identical examples, where the aim is to increment a variable from 1 to 10 and then display the contents of that variable.

Why are there brackets around the first and third arguments in the first example? The brackets are there because these arguments may optionally contain multiple entries, each separated by a semicolon. My guess is that many developers will leave the brackets in place to serve as internal guideposts, even when they only contain single entries, and therefore aren’t strictly necessary.

Annotated Example: Coin Tosses

In this example we are simulating 100 tosses of a coin, with brackets required for arguments 1 and 3 (since they contain multiple entries).

Explanation:

1. Generate a random number, for example .95258565133063877184
2. Use the Middle function to grab the first digit to the right of the decimal point
3. Use the Mod function to divide the preceding by 2
4. The result (i.e., the remainder) will be 0 or 1
5. Use the Choose function to assign “H” if 0 and “T” if 1
6. Append this value to the accumulating string stored in x
7. Repeat steps 1-6 until i exceeds 100, then bail out and display x

Note 1: In the initial variable declaration we assign starting values, whereas in the [logic] section, we iteratively update these variables by setting them to transformed versions of themselves (e.g., x = x & …). If you’re comfortable using the Let function to define variables, then these two bracketed sections (highlighted in yellow above) will feel familiar.

Note 2: Frequently the final entry in the [logic] section will be “i = i + 1”, or some similar construct to increment a counter (a.k.a. iterator) and move things one step closer to meeting the stated exit condition.

I will confess that the most common mistake I’ve made while attempting to wrap my head around While is forgetting to increment my iterator… at which point I become very grateful for the 50,000 iteration default recursion limit (as it prevents FileMaker from locking up indefinitely… assuming of course that I haven’t overridden the default limit via SetRecursion).

Generating Powers of 2

Let’s look at some more examples. This produces the first ten powers of 2 in a space-delimited list.

FizzBuzz

And here’s one courtesy of John Weinshel implementing the FizzBuzz exercise, where you take the counting numbers and…

• if a number is a multiple of 3, say “Fizz”
• if a number is a multiple of 5, say “Buzz”
• if a number is a multiple of both 3 and 5, say “FizzBuzz”
• otherwise, just say the number

While within While

Returning to the coin flipping example… what if we want to toss 100 coins 100 times — can we embed one While statement inside another?  Absolutely, and the “inner while” (boxed in red) can be reused from our earlier example without modification.

Generating a Word Index

Next let’s take the opening chapter of Jane Austen’s Pride And Prejudice…

…and build an index of the words, sorted in ascending order of word length.

Explanation:

1. generate a lower-case list of unique words
2. for each word, prepend a quantity of byte order marks (BOMs) equal to the length of the word
3. sort the list via SortValues using “Unicode_Raw” as the “locale” argument
4. substitute out the BOMs
5. use Ray Cologon’s Trim4 to strip off the annoying trailing return that was “helpfully” added by SortValues and UniqueValues

Note: byte order marks have made a few previous appearances on FileMaker Hacks, including here (It’s Sorta A Value List Thing) and here (Custom Field-Based Value Lists).

Update 2 June 2019: on reflection, there was no need to use Let in the above example. All the variables could have been declared via While like so:

Prime Number Generator

Here’s another example of one While wrapped inside another… in this case to facilitate the generation of all prime numbers from 2 up to a specified limit. This is a bare bones proof-of-concept (i.e., has only been lightly optimized) and takes a different approach than the one used in my CustomList article.

Explanation:

1. The outer While generates a list of odd numbered candidates between 3 and the specified limit
2. The inner While tests each candidate by dividing it by all odd divisors <= the square root of the candidate
3. Since 2 is a special case (the only even prime) it is prepended when the result is displayed.

Thoughts On Further Optimization:

• Rather than generating all odd candidates in the outer While, one could apply some common sense and remove multiples of 3, 5, 7, 11… but where to draw the line?
• The outer While logic could be further improved to only generate candidates of the form Mod ( candidate ; 6 ) = 1 or Mod ( candidate ; 6 ) = 5, since all primes >3 yield a remainder of 1 or 5 when divided by 6. [Unfortunately, many composite numbers take this form as well, so this is not a magic recipe to produce primes, only candidates.]
• Implementing either of the above would allow fine tuning of the inner While logic

Charting the Maclaurin Series

This demo is courtesy of Oliver Reid, who kindly provided these notes:

1. The calculation of y values uses nested While functions — one to click through the x-values and the second to calculate the series sum for each value of x.
2. The Maclaurin series is often an inefficient way of actually calculating values in real applications — the series can converge slowly — the more so the further x is from 0 — to go out to 8π would need a lot more than 50 terms.
3. https://en.wikipedia.org/wiki/Taylor_series

Using SetRecursion with While

At the outset I gave an example of using SetRecursion with a custom function, but it works identically with the While function, taking this form:

SetRecursion ( While ( ... ) ; maxIterations )

Incidentally here’s how I tied up my elderly Windows 7 laptop for 51+ minutes iterating half a billion times.

[Aside: a way to appreciate the difference between a million and a billion… a million seconds is approximately 11 and a half days; a billion seconds is roughly 32 years.]

Closing Thoughts

My nickname for the While function is “recursion for the masses”. I predict that we will see some highly creative solutions emerge from the developer community utilizing this function. Congratulations, FMI, on a job very well done.