It has been a busy couple of months since my last post, and in that time FileMaker, Inc. has unveiled the FileMaker 18 Platform. With that in mind, I’ve decided to write a series of articles highlighting some of my favorite new features. Without further ado, let’s dive in and look at the While ( ) function.
As you know, the Let ( ) function was added to the platform all the way back in FileMaker 7. It gave developers the ability to declare a series of global variables, script variables, and even local variables within the function. When creating these variables, the expression can be a constant, a field value, or a calculation incorporating one or more functions. Finally, the developer writes a final expression, typically a combination of functions and the variables set earlier in the statement, which is the result of the Let( ) function. Naturally, any calculation can be written without using Let ( ), but its use certainly makes the calculation easier to read and also modify if needed. Depending upon how the variables are named, one could even say use of the Let ( ) function makes any calculation self-commenting.
The syntax of the While ( ) function is similar to the Let ( ) function. At the beginning, a series of variables, again local, script, and/or global in scope, can be declared within the first set of square brackets with the expression being a constant, a field value, or a calculation. A result using both functions and the variables set previously appears at the the end. In between the initial variables and the result is where, as I like to say, the magic happens. The full syntax of the function is shown below.
While (
[
initialVariable
] ;
condition ;
[
logic
] ;
result
)
Working from the bottom up, the second set of square brackets contains the logic which is repeated until the exit condition is reached. Yes; I said the logic which is repeated until the exit condition is reached. As with the initial variables, the repeated logic are those expressions, again any combination of functions, variables, fields, and constants, that yield the sought after result. Did I mention this section of logic is repeated? (You may be picking up on the fact that I am a little excited by this property of the function.)
The conditional statement located between the initial variables and repeated logic is the exit condition. When setting up the While ( ) function, there are two important things to remember related to the exit condition. First, it is evaluated BEFORE the repeated logic is executed. If your result appears slightly off, make sure you have defined the exit condition in a manner that ensures the logic is repeated the proper number of times. Second, the logic is repeated until the exit condition is FALSE. In other words, the function loops through the logic while the condition is true before generating the final result.
An obvious use case for the While ( ) function is as a replacement for recursive custom functions. A unique property of a developer-defined custom function is the ability for the custom function to reference itself in its calculation. Prior to FileMaker 18, this recursion was the the only method available to developers who wanted to loop, or repeat, logic within a calculation.
One custom function that I employ on a regular basis is FoundList ( field ; start ), which was initially developed by Eilert Sundt. (You can find it here on Brian Dunning’s FileMaker Custom Function website: https://www.briandunning.com/cf/1058 ) With the target field specified and the start position set to 1, the function yields a return-separated list of all values in that field across the current found set.
To compare the recursive custom function with the While ( ) function, I wrote a script to return the number of unique city names represented by the 10,000 records stored in a sample Contacts database. It captures the start time, using Get ( CurrentTimeUTCMilliseconds ). Then, the custom function is employed to return the list of cities in the found set. This result is passed to the UniqueValues ( ) function to return the list of unique cities, and then this result becomes the parameter for the ValueCount ( ) function to get the final count of unique city names in my found set. Another Set Variable script step captures the stop time of the process, before displaying the results in a simple Custom Dialog. The whole process took 1.305 seconds to count the 2156 unique cities in the list of 10,000 addresses.
I then re-wrote the script using the While ( ) function in place of FoundList ( ). The new calculation for that particular Set Variable script step is shown below:
While (
[
index = 0 ;
foundCount = Get ( FoundCount ) ;
cityList = ""
] ;
index < foundCount ;
[
index = index + 1 ;
cityValue = GetNthRecord ( CONTACT::BillCity ; index ) ;
cityList = List ( cityList ; cityValue )
] ;
cityList
)
I ran the updated script on the same found set, and the same unique city count was returned in 1.189 seconds. This is a slight but still obvious performance improvement. Without going into all of the details, I have replaced a few other recursive functions that I commonly employ with While ( ) and have seen speed improvements in all cases, and some markedly so.
With that in mind, the question becomes “Should all recursive custom functions be replaced with While ( )?” I think the answer depends on the solution. If it is running in an environment where some users are still running FileMaker 17 or earlier, you will need to either add logic to select the calculation method based on the application version that is running, or to just continue employing the recursive custom function for all users. It is worth noting that in my testing I purposely used large data sets to more easily see any time difference between the two methods. If your recursive custom function is only operating on a handful records, any performance improvement may be negligible and not worth the additional programming time to replace each custom function with its While ( ) equivalent.
That said, there are a number of practical applications for the While ( ) function, but I think I’ve rambled on long enough for now. We can explore these examples next time.
Until then,
TW
