Take a While to Solve the Calculation

Learn to loop through iterative calculations with an impressive function

The While function is an impressive calculation recently added to FileMaker Pro 18.  It takes the place of many calculations that were previously accomplished through recursive functions or by scripting loops.  The advantage of the While function is that it is more easily tested and controlled than recursive functions, and it is also much faster than a scripted loop function.  Once you understand how to implement it, While will become a powerful function at your disposal.  

There are several parts of the While function that will be covered.

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

Begin with a Let function

The first part of the function is [ initialVariable ].  This allows you to set an internal variable, or using the bracket notation, multiple internal variables.  It works just like the first part of a Let function, where the initial variables are set before performing the calculation.  In this example, a counter variable, i ,and a max value, n, are set.

[ i=0; n=5; result=1 ]

Set the conditions

The condition part of the function determines how many times the logic part of the function is executed.  This is written as a boolean statement.  You can loop through the calculation 5 times, i < 5.  You can loop through a calculation until result is set to True, result <> True. You can set any logical test to determine is the while loop should continue running.

An important fact to note is this is just like the Exit If script step that is used to determine when a script loop should exit.  However, Exit If exits the loop if the logical test is True.  The While condition is the exact opposite, it continues to execute the loop while the logical test is true.  As soon as the condition evaluates to false, the calculation exits.

In this example, we will loop through the calculation until it’s completed 5 times.

[ i=0; n=5; result=1 ]; i<n; 

Run the logic

The third part of the function is [ logic ].  This section is also formatted like the beginning of the Let function, allowing you to set multiple internal variables to simplify the calculation. Since for most loops, the goal is to loop a calculation a certain number of times, the most important variable to set is the counter.  In this case we will increment the counter variable by 1.

[ i=0; n=5; result=1 ]; i<n; [ i=i+1; 

Next, we can set any other variables that are needed by the calculation.  In this example, we will do a factorial calculation.  5! = 1 * 2 * 3 * 4 * 5, and this can be calculated using the While function. Each loop, the result gets multiplied by the next increment value.

[ i=0; n=5; result=1 ]; i<n; [ i=i+1; 
   result = result * i ];

Before the first iteration, i=0 and result = 1. During the first iteration, i is set to 1, and result is set to 1 * 1 = 1.  Next, the condition is evaluated to determine if the calculation should continue to run, 1 < 5 = True, and the calculation goes to the next iteration.

2. i=2; result = 1 * 2
3. i=3; result = 2 * 3
4. i=4; result = 6 * 4
5. i=5; result = 24 * 5

After the fifth iteration, the condition evaluates as 5 < 5 = False, and the logic section is exited.

Other variables can be set in the logic portion of the function, however, only variables declared in the initialVariables section persist across the loop. That means that variables declared in initialVariables can be referenced anywhere else in the function.  If they are set to a new value in one loop, that new value is available in the next loop. If the variable is declared here, in the logic section, it becomes reset to an empty value on every loop.

A best practice is to label the non-iterative variables with a “~” character, to note that they are internally scoped to the current iteration.  In the example below, ~test is recalculated each time with the result of the last iteration, and can’t be used to concatenate a value over all of the iterations. After the loops, ~test = 24*5, not 1*1*2*3*4*5.  Keep this in mind when setting variables in the logic section.

[ i=0; n=5; result=1 ]; i<n; [ i=i+1; 
   ~test = result & “*” & I;
   result = result * i ];

Return the result

Once the logic section is exited, the result section is run.  This can be any calculation that returns a result, and can reference the variables set in the initialVariable or Logic sections.  That works just like the last section of the Let function, where any calculation can be evaluated.  Variables from the logic section return their values most recently calculated in the last iteration.

While ( [ i=0; n=5; result=1 ]; i<n; [ i=i+1; 
   result = result * i ];
   result )

Putting it all together

Here’s a task that previously would have to be accomplished by looping through records in a script and setting variables, which can now be accomplished with a single calculation. Take the example of setting a variable to a list of all the primary keys of the records in the found set.  Previously, that might look like this:

Go to Record/Request/Page [ First ]
Loop
	Set Variable [ $result ; Value: List ( @UUID; $result ) ]
	Go to Record/Request/Page [ Next ; Exit after last: On ]
End Loop

Every time the loop goes to the next record, all record data and related records need to be loaded. Now we can use the While function to simplify those four steps into a single calculation:

While ( [ i=0; n=Get ( FoundCount ); result="" ] ; i<n ; [ i=i+1;
	result = List ( result; GetNthRecord ( @UUID ; i ) )
] ; result )

Final Notes

By default, FileMaker limits the number of iterations to 50,000.  This is a helpful limit that prevents developers from accidentally creating an infinite loop with an exit condition that never evaluates to false.  In that case, after 50,000 loops, the function will exit with the result, “?”.  Depending on the calculation, those 50,000 loops execute extremely fast, and prevent the application from hanging up for a long period of time for the user.  If you know the calculation will need to execute more ore less times than 50,000, you can use the SetRecursion ( expression ; maxIterations ) functions.  Place the While loop in the “expression” section and set maxIterations to the maximum number of loops you want to allow.

While is a powerful function that enables looped calculation across records, arrays, or value lists that was much more complicated before. Looping through a found set of records with GetNthRecord ( field ; i ), or records related to the current record through a portal becomes exceedingly simple. Check this previous post on arrays to learn how to use this function to transform records into JSON. Comparing multiple fields across records and returning a list of values from another field can be done to return a filtered set of values. Now get started putting the While function to good use.