We’ve been waiting for While()… for a while

FileMaker has been missing a looping control structure in its function set for…well…a while. So, the introduction of FileMaker 18’s While() function is less a fancy new feature than it is a fix for a long standing limitation in FileMaker’s function set.

But even if it’s long overdue, its introduction profoundly changes what is now practical, alleviating the need for many clever hacks and workarounds so common in FileMaker development.

To appreciate how elegantly FileMaker has implmented this fix, it is useful to approach While() as a basic and necessary coding concept as it exists in most other languages.

Why While() is important, and overdue

While() is important, because While() is a control structure function. Like If() and Case(), it evaluates a condition before deciding what to do. In FileMaker, we have control structures in both scripts and functions. In scripts these are the “Control” steps. In functions, they are found mixed among the “Logical” functions. A looping control structure has been available in scripts for a while, but within functions, and thus basic calculated fields, we’ve been missing any function that allows us to easily control loops. This is such a basic need that most languages have more than one flavor of looping control structure in their function set. They are called things like: for, foreach, do-while, while, et al. A basic ability to control a loop has been an almost embarrassing omission of FileMaker’s function langauge.

Without a native function to control loops in a calculation, FileMaker developers have had to rely on various workarounds—most commonly, either scripts or custom recursive functions. Using scripts, of course, requires managing triggering those scripts. Recursive custom functions not only rely on wrapping one’s head around the technique, but also could be unreliable because of built-in and previously inflexible recursion limits. Though these and other methods work, they’ve only been necessary because there hasn’t been a While() or its equivalent for the last 17 iterations of FileMaker.

In fairness to FileMaker, however, implementing a looping control structure also requires managing a whole host of other concepts—recursion limits and variable scope to name a couple—making its introduction into the platform and the userbase less than trivial. In addition, with While() it is much easier to create calculations that can really slow performance, which can affect perceptions of the platform, and one can imagine a reluctance to take the risk. Fortunately, something FileMaker does consistently well in version releases is not only rationing the number of conceptual adjustments users have to digest, but also solving these things in a solid clean way. While() is no exception. It is great.

All of which is to say, “Job well done… and it’s about F-@%!-ileMak-@%!-ing time.” Now let’s get on to how While() works in FileMaker 18 and how to understand it.

Understanding the structure of While()

A basic prerequisite for understanding While() is to make sure one first understands two other functions: If() and Let(). There’s not much to say about If() other than it demonstrates how to use a “condition” to control what happens next.

Let() is relevant, because not only does it uses the same basic parameter structure, but also it requires understanding variable scope within a calculation. In fact, one can think of While() as “Let() with looping.”

Like While(), Let() requires you to declare variables, then evaluate an expression. Here are the parameters:

Let ( [ declare variables ] ; calculation )

The first parameter is a list of variable declarations. You can use as many as you need, and each follows the format of “this equals that”:

variable = calculation

Note the first Let() parameter is enclosed in square brackets indicating that a list of statements is expected (separated by semicolons.) Let() is super useful because it allows us to declare variables, which helps make our code more organized and self-documenting.

So, for example:

Let ( [ needle = "hello" ; haystack = "hello world" ] ; PatternCount( haystack ; needle ) )

returns 1.

Let() takes a set of variable declarations, then returns a calculation. One fairly common thing to do with Let() is to complete the whole logic of the calculation in the variable declarations, thus making a calculation simply a sequential set of operations, setting a “result” variable as the last step. (This habit will be useful once one is accumulating a result in a loop.) So, for example, the above calculation can be rewritten like this:

Let ( [ needle = "hello" ; haystack = "hello world" ; result = PatternCount( haystack ; needle ) ] ; result )

While()’s parameters follow the same format. They both have variable declarations, and the last parameter is a result calculation. But whereas Let() has simply variable declarations and a result, While() adds a set of variable declarations to repeat in the middle, and a condition to control when to stop.

Let(     
  [ initialVariables ] ;                    
  result    
)

While(   
  [ initialVariables ] ; //-- Let( [ initialVariables ] ;
  condition ;   
  [ logic ] ;
  result                //-- result )
)

As mentioned already, the “condition” parameter takes the same format as the “test” parameter in the If() function. For example, it might evaluate “x > y”, which is either true or not. In While() the condition statement is evaluated on every iteration before the logic. So on each iteration, including the first one, there is the opportunity to escape the loop.

Controlling the loop

To use While() we do need a method for controlling or managing our loop. As there are no “start” and “stop” parameters, we have to define and manage those ourselves. That is, we have to manage not only the condition statement but the logic that changes the conditions on each loop. Generally this is done using some sort of “counter” or “iteration” variable and comparing it to a “limit” or “maximum” value. This isn’t the only way, but in the vast majority of cases it will be easiest way to go one by one through a set.

Continuing on the theme of building our understanding off of Let(), imagine the following Let() calculation:

Let( [ 
  string = "hello world" 
] ; 
  string 
)

This returns hello world

An equivalent While() would look like this:

While( [ 
  counter = 1 ; 
  string = "hello world" 
] ; 
  counter = 1 ; 
[ 
  counter = counter + 1 
] ; 
  string 
)

This returns hello world.

So, we set the counter to “1” in our our initial variables, then in the logic, we increase the counter by 1. Our limit is in the condition statement is just 1, so after the first iteration, it stops.

To make the hello world example perform more than one iteration, we need to introduce a bigger limit. (Note I’ve renamed the counter “iteration”):

While ( [ 
  iteration = 1 ; 
  timesToRepeat = 3 ;  
  string = "hello world" ; 
  result = "" 
] ;
  iteration ≤ timesToRepeat ;
[ 
  result = list( result ; string ) ; 
  iteration = iteration + 1 
] ;
  result
)

This returns:

hello world
hello world
hello world

As long as the counter is below the limit, the function loops. In the above example, I’ve called the counter “iteration” and the limit “timesToRepeat”. Note also the introduction of a “result” variable to accumulate our result.

We can call our iteration and limit variables whatever we want, although there is always a tenuous balance between good self-documenting code and convenient conventions. Typing out “iteration” everytime is clear, but it’s also a bit tedious and easy to introduce a typo. Likewise it is sometimes good to name your limit variable what the limit is based upon, such as “numberOfRecords”, “valueCount”, etc.

On the other hand, with loops, I tend to prefer a simple convention of “i” as my counter and “j” as my limit. The “i” is short for “iteration” or “index”, and “j” is not just a convenient next letter, but is standard for expressing limits in mathematical formulas. With this convention the above calcuation would look like:

While ( [ 
  i = 1 ; 
  j = 3 ;  
  string = "hello world" ; 
  result = "" 
] ;
  i ≤ j ;
[ 
  result = list( result ; string ) ; 
  i = i + 1 
] ;
  result
)

And, more importantly, each While() function shares a common format by convention:

While ( [ 
  i = 1 ; 
  j = {{calculationToSetLimit}} ;  //--  e.g., ValueCount( {a list} ), Count( {related field} ), etc. ;  
  result = "" 
] ;
  i ≤ j ;
[ 
  result = {calculationToUpdateResult} // Which generally includes "result" itself , e.g., List( result ; GetNthRecord( {field} ; i )
  i = i + 1 
] ;
  result
)

That’s enough basic theory. Let’s move on to a more practical example.

Practical examples leveraging GetNthRecord

One of the more practical uses of While() is assembling data from a set of records, using the function GetNthRecord(). Whether it’s working with a found set, or a set of related records, GetNthRecord is just about always the easiest way to get values while looping through a set of records. When working with a found set, the Nth record is based on how the records are sorted regardless of which record is the current record. With related records, the Nth record is based off the current record and any sorting in the relationship definition.

So, imagine a table of people each of which has a first_name and last_name. We could create a calculation to get a list of full names from the found set like this:

While(
[
i = 1 ;
j = Get( FoundCount ) ;
result = ""
];
i ≤ j
;[
name = GetNthRecord( people::first_name ; i ) & " " & GetNthRecord( people::last_name ; i ) ;
result = List( result ; name )
];
result
)

Or, imagine a contact database, where phone numbers, email addresses, and websites are all entered and stored as related records, but where one wants a simple display field for reports or reference.

So, we’d use a calculation such as this:

While ([
i = 1 ;
j = Count ( contact_data::id ) ;
result = ""
] ;
i ≤ j
; [
value = GetNthRecord ( contact_data::contact_value ; i ) ;
label = GetNthRecord ( contact_data::label ; i ) ;
result = List( result ; value & " (" & label & ")" ) ;
i = i + 1
];
result
)

This returns something like:
555-444-3333 (cell phone)
john@doe.com (email)
etc.

An even more useful way to use While() is to construct JSON objects from related data. Again, the generic name/value for children provides easy understanding.

So, a calculation such as:

While (
[ i = 1 ; j = Count ( children::id ) ; result = "{}" ] ; 

i ≤ j ;

[
key = GetNthRecord ( children::name ; i ) ;
value = GetNthRecord ( children::value ; i ) ;
result = JSONSetElement ( result ; key ; value ; JSONString ) ;
i = i + 1 ]

; JSONFormatElements ( result ) )

returns

{
"name1" : "value1",
"name2" : "value2"
}

Pitfall #1 – Infinite Loops and Recursion limits

If you create While() function that is invalid it will return a simple “?”. The most common invalid While() function is one that either creates an infinite loop, or hits the recursion limit that FileMaker sets to protect against infinite loops.

So, for example, consider this While() statement:

While( [
  i = 1
] ; 
  i = 1
; [
  result = "nothing"
];
  result
)

This would go on forever. Since we never are increasing the value of i, the condition i = 1 will always be true, so it is an infinite loop. While() returns a “?”.

But, one can get a “?” by simply exceeding the MaxIterations. By default FileMaker’s maximum iterations limit is set to 50,000. If your function hits that many iterations it will return “?” rather than the result you want. So, for example, if we had a While() calculation that used the found set of records, and there were more than 50,000, While() by itself fails. To control this, While() has a complimentary function called SetRecursion function, and takes the format:

SetRecursion( expression ; maxIterations )

So, for example, our found set example would take a format like this:

SetRecursion( While( ... ) ; Get(FoundCount) )

Basically, SetRecursion() is a wrapper around the function you wish to control. It’s worth mentioning, however, that one should be careful with this. One can introduce drastic performance problems trying to get too ambitions with While() on a large set of data.

SetRecursion() in not limited to use with While(), by the way. It also can override the default limits for those custom recursion functions, meaning these don’t need to be recoded. In fact, there are many cases where a recursive function may still be faster than While(). Now, however, the recursive function can be reliable as the recursion limits can be explicitly controlled.

Pitfall #2 – Scope

If we look back at our example above using contact_data, notice that the “result” variable is declared in the initial variables. This is because While() variables are very carefully scoped — remembering this will help you from tearing your hair out trying to figure out why you get a “?” so often. Only variables that are defined in the initial variables are available to the whole function. Or, conversely, variables defined only in the logic section are only available to each iteration of the logic, not the next iteration or even the result expression. (A caveat here is that rules are more nuanced if you nest Let() or While() within a Let() or While(). More on that below.)

If you don’t declare a variable in the initial declarations, each time the logic is run, that variable doesn’t exist yet. It’s because “result” calculation refers to itself that it needs to be declared to be remembered for each loop.

While ([ 
  i = 1 ; 
  j = Count ( contact_data::id ) ; 
  result = "" 
] ; 
  i ≤ j 
; [
  //-- value and label are local to the logic expression
  value = GetNthRecord ( contact_data::contact_value ; i ) ;  
  label = GetNthRecord ( contact_data::label ; i ) ;

  //-- the result and i variables were declared in the first parameter and so are available to themselves
  result = List( result ; Substitute( List( value ; " (" ; label ; ")" ) ; "¶" ; "" ) );  
  i = i + 1 
]; 
  result  //-- the only variables available here would be "result", "i" and "j"
)

Fortuantely, when you try to use a variable improperly in the logic section, the calculation editor will give you the familiar “The specified table cannon be found” error. However, if you improperly use a variable local to the logic section in your result expression, you just get the crypitic “?”

Scope becomes even more important when you start nesting and combining Let() and While(). In fact, the documentation examples in FileMaker’s reference go to great lengths to illustrate it. The simple rule of thumb to remember is that in While() the “logic” parameter has it’s own local scope, varaibles defined in the “inital variables” parameter are local to the function they are defined (and don’t overwrite variables of the same name defined outside the function). In this way, scope in While() and Let() work very similarly to variable scope in JavaScript.

Here, without extra comment, is an illustration using a While() within a While() that illustrates this

While ( [
  i = 1 ; j = 10 ; result = ""
] ;
  i ≤ j
; [
  result = List(
    result ;
      While( [
        i = 1 ; j= 9 ;result = ""
      ] ;
        i ≤ j
      ; [
        result = result & i ;
        i = i + 1
      ] ;
        result
      )
    ) ;
  i = i + 1
] ;
  result
)

… which returns

123456789
123456789
123456789
123456789
123456789
123456789
123456789
123456789
123456789
123456789

The inner "i", "j", and "result" declarations don't knock out the outer declarations, but they would if they weren't declared again on their own in the inner While().

Scope can get confusing, and that's without getting into how one can set local script and global variables (ie. $var or $$var) within a Let() or While() thus affecting scope outside the function, which is, yes, possible. For now it's enough to simply remember that While() and Let() will require attention to scope, and undestanding will be key to debugging more complex operations.

Next steps

There are many great examples of how to use While() out there. Here is a demo file that I created, and links below to other coverage of While() in the FileMaker community.

Demo File: What While() makes Practical

Here is a sample file illustrating some practical examples with related records:

What While makes Practical [demo .fmp12 file]

 

Explorations & Inspirations

Others in the FileMaker community have delivered useful and inspiring coverage of While() in FileMaker 18:

For some clever examples and performance comparisons to recursive functions, see Kevin Frank's post at filemakerhacks.com

The Support Group's blog on While() has a good example of the same logic in a script and using While().

I hope you found this article useful in learning more about While() and FileMaker 18. Keep watching blog.beezwax.net for more examples, about While(), JSON, and other FileMaker techniques.