The Support Group Blog

FileMaker 18 New Calculation Functions

FileMaker 18: New Calculation Functions

If you had a specific situation where you needed to perform loops within a calculation, you were resigned to recursions. But now FileMaker 18 allows for While calculation functions so you can avoid those cumbersome recursive functions. No custom function necessary, no having a function call itself, no worrying about being sucked into a wormhole – maybe that last one is just me.

In any case, here’s what the While function looks like when you select it in the Calculation workspace:

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

The While function has four parts:

  • Initial variable – the initial values for each of the variables you're going to use in your function
  • Condition – a boolean condition; as long as this condition returns as true, the loop will repeat
  • Logic – the function that is repeated every time the loop iterates
  • Result – the final line of the expression that is evaluated when the loop stops
While Example

Now let’s walk through building a calculation, using the While function. Just for practical purposes, I’m going to show you how to build an acronym calculation. Let's say our users need to figure out how to create an acronym for the company name within their Contacts module in FileMaker. Of course, we can do this with a script, in fact, here's what that script would look like:

FileMaker 18 New Calculation Functions

Now we'll turn this into a While function. I just want to point out a few things before we get started. The While function operates in a similar manner as the Let function so when you add a series of variables between brackets, you have to make sure to not put a semicolon at the end. Also, if you have just one variable or expression between the brackets, you really don't need the brackets at all.

With that said, the first thing we'll do is express each part on its own line. This is a personal preference for me since calculations ignore line breaks and whitespace, so it just makes it easy to read and write.

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

Let’s work through each part. We've got multiple initial variables, so we're going to need the brackets for our initialVariable part. We need to grab the text that will be the basis of the acronym and get a word count of it. We also need to declare a variable that we'll use at the end as our result. I like to declare all of my variables at the beginning, but that's another personal preference.

While (
[
_chunk = Contacts::Company;
_wordCount = WordCount ( _chunk );
_acronym = ""
] ;
condition ;
[ logic ] ;
result )

Notice I put the brackets on their own line; this is just for legibility. Also, remember not to use a "$" at the beginning of the variable's name in a calculation function as they could conflict with local variables that you declare in scripts.

Next up is the condition. This is where the While condition doesn’t work quite the way we're used to loops working in the script workspace. In the script workspace, we exit when a condition is true. In the While function, we continue looping while the condition is true, hence the name. So in our script above, we exited when the word count equaled zero. However, in a While statement, we have to do the opposite of that – continue while word count is greater than zero.

While (
[
_chunk = Contacts::Company;
_wordCount = WordCount ( _chunk );
_acronym = ""
] ;
_wordCount > 0 ;
[ logic ] ;
result )

Now we get to the meat of this, the stuff that happens every time we perform the loop. This is going to be just like our script. We grab the first letter of the first word, take our word count down by one and then change our base text – the chunk of text – to omit the first word.

While (
[
_chunk = Contacts::Company;
_wordCount = WordCount ( _chunk );
_acronym = ""
] ;
_wordCount > 0 ;
[
_acronym = _acronym & Left (_chunk;1);
_wordCount = _wordCount - 1;
_chunk = RightWords ( _chunk ; _wordCount )
] ;
result )

Again, I separated the brackets onto their own lines for legibility. And, we make sure that the final variable declaration doesn’t have a semicolon at the end.

The final step is to return a result. In this case, we’ve updated the acronym variable with the first letter of each word through every iteration of the loop. So at the end of the function, we should have a complete acronym of the company name. Here’s the full calculation, with comments:

While (

//Initial Declaration
[
_chunk = Contacts::Company;
_wordCount = WordCount ( _chunk );
_acronym = ""
] ;

//Condition
_wordCount > 0 ;

 //Logic
[
_acronym = _acronym & Left (_chunk;1);
_wordCount = _wordCount - 1;
_chunk = RightWords ( _chunk ; _wordCount )
] ;

//Result
_acronym )

Now, instead of having to write a script that creates this acronym and figure out when to trigger it to store the acronym into a field, you can just have a calculation field that stores this for every record automatically. You no longer have to rely on recursive custom functions.

 
Bonus: Set Recursion

We all write perfect code every time, right? We would never create a While calculation that would have an end condition that never executes, right? Well, for those of us who might make a mistake once in a while, we have a fail-safe. If you wrap your entire While function in the SetRecursion function, you can specify a limit to the number of iterations it performs. FileMaker's default limit is 50,000 iterations, but you can adjust that using this function. Here’s what the function looks like by itself:

SetRecursion ( expression ; maxIterations )

Take our acronym calculation, for example, here's how you would limit it to 1,000 iterations:

SetRecursion (
//Begin While Calc
While (
[
_chunk = Contacts::Company;
_wordCount = WordCount ( _chunk );
_acronym = ""
] ;
_wordCount > 0 ;
[
_acronym = _acronym & Left (_chunk;1);
_wordCount = _wordCount - 1;
_chunk = RightWords ( _chunk ; _wordCount )
] ;
_acronym )
//End While Calc
;
 //set maxIterations Limit
1000
)

If your calculation hits the limit, the expression will return a question mark, making it look like your function isn’t working properly for some reason. If you do get that result, double check that your While calculation isn’t going over your MaxIteration limit before you spend hours troubleshooting the actual code in your While expression.

One final note, any iterations or results that may have been created as a result of the iterations that ran before you hit the MaxIterations limit will be discarded. Once it hits the limit, the function just results in the question mark. You have to either increase your iteration limit or fix your While function in order to see the result.

Join our mailing list to keep up with the new features, like the new import interface, in FileMaker 18. It's these features and more that make FileMaker a Workplace Innovation Platform.

Keep me posted

 

This article is also published on FileMakerProGurus.com. 

Share this entry
1 replies
BrowseMode

Sign up to receive news and information about the FileMaker platform and other custom app development tools.

Keep me posted

Most Popular

Developer Resources

News, Tips & Tricks and Demos

Archives