[FMP Tip-N-Trick] Don’t Get Thrown for a Loop with FileMaker’s New While Function

Check Out This Custom Function for Merging JSON Arrays

By Rob Poelking, eXcelisys FileMaker Developer

FileMaker Inc. (errr, I mean Claris International) introduced a new function with the release of FileMaker 18 that I was both eXcited and intrigued by until I tried to use it the first time: the “While” function.

I’ve written a number of custom FileMaker functions through the years and lots of loops in scripting to manage repetitive tasks like complex string manipulation or complicated mathematical calculations. So, when I heard that the latest version of FileMaker would include a new function that accomplished both a loop and assignment functions all at once, I was anxious to get my hands on it — until I did.

Gotchas — Clue to Self: Stop Thinking Like a Loop

The problem with eXploring new functions in FileMaker is that sometimes we come with baggage — and I’ve got a lot of “developer” baggage after 30 years in the industry. As I quickly discovered, the baggage that got in my way was years of working with recursive functions or loops, which is precisely the reason why “While” came about.

The classic approach involved a count (and possibly an iterator) and running some logic over and over until we reached an escape point.

For example, a loop:

Adding recursive functions to older versions of FileMaker worked in a similar fashion in that you embedded the test within the function and until the eXit condition had been reached, you kept calling the function itself.

These functions worked great for years, but they could be cumbersome and required much more coding to accomplish repetitive tasks.

Enter WHILE. The new function takes the form:

Before you dive in, I hope you have a good grasp on the use of another function: Let. There is a similarity. When FileMaker introduced the Let function, we were able to assign multiple variables inside the brackets ( [ ] ). I got used to that. However, unlike the Let function where your variables are only set once, the FileMaker While function allows you to define two sets of variables.

The first set of variables represents your initial variables. Think of that as where your iterator starts. I commonly use $i as my iterator, but inside a “Let” you do not need to include the $ notation. Many developers use the ~ (tilde), @ (at sign), or nothing at all inside the variable declarations of a Let statement and the same rules apply here. Also, analogous to the Let statement, make sure you declare all variables you will use throughout the While statement in this segment. To create a super simple eXample, let’s start with:

I’ve declared 2 variables (~n and ~z) and assigned them simple integers.

I’m going to skip the “condition” for a moment and think about what I want at the end, otherwise known as the result. In this case, I simply want the value of ~z after performing some function. So, let’s put that as the result variable now:

Now, the logic. What do you want to happen to ~z repeatedly? Keeping things simple, we’ll perform some simple math:

Notice how this is just like the declaration assignment of the Let statement, eXcept that we get to assign variables a second time. The logic part of the evaluation is where your variable assignments will change, whereas, the first part of the loop is just the initial assignment or declaration of those variables. You could have even declared the variables as part of the initial declaration and left them empty.

But wait! There’s more. What happens if you declare a loop with an eXit condition that depends on an iterator but forget to increment the iterator? Your client will think, “It’s crashed!” Not really. It’s just an infinite loop and it’s stuck. Fortunately, FileMaker will enforce recursion rules and eXit out after a period of time, returning only a “?” as your result. But that’s not what we want. So, we have to add a means of incrementing the iterator — in this case ~n. You can choose whether that should increment before or after your logic. That will largely depend on how you want to arrive at your final product. For now, I want to increment before my logic.

One last part — and this is where I got stuck. What to do with the condition? In a Loop, we test for whether a condition is true before we eXit. Like the Loop eXample above, I am pre-programmed to test for $i>$count. If that is false, the loop continues. Because of my baggage, I wanted to write:

In other words, when the value of ~n is greater than the value of ~z, eXit the loop. OUCH … try it. You will not get the result you eXpected because this isn’t a Loop! It’s a While. Instead, you have to think, “While the value of ~n is still less than the value of ~z, then continue.” In other words, the eXit condition must be false before the evaluation stops. While the condition is true, FileMaker will continue evaluating the logic portion of the statement.

I can’t tell you how long I struggled with that until my colleague Ken Moorehead said, “Yeah, it’s the opposite of what you think.” I thought he was full of crap until I flipped the sign and proved him right. So, flip that bad boy and our final function looks like this:

You can copy and paste that final statement into your data viewer and get the result.

Practical Application for Using the New WHILE Function 

Okay, you have a powerful function on your hands, but “practically speaking,” what can you do with it? For starters, you can replace all of your recursive functions and a lot of loops with it. But today I had a very practical problem. I was working with the WooCommerce API and ran into a limitation. When listing products, WooCommerce paginates the returned data as JSON arrays. The default number of items returned is 10. The max you can return is 100. That’s a very small shop if it only has 100 products. I had to loop and make several calls to the API to get all of the products. I left that part of my script as a traditional loop, but what I ended up with was several repetitions of the result sets — all independent JSON arrays. What I wanted was a single JSON array. This was a perfect problem to solve with the new FileMaker While function. So much so, I decided to just make it a custom function on its own for later use; however, you can just declare a variable and use this as an independent calculation.

Start with initial variables. I know that my end result will be a new array, so I created:

I need to know how many times to get the contents of array 2 to insert them into my ~newArray so I use a combination of ValueCount and JSONListKeys to get a count of the array objects.

I have to have an iterator. I’ll just use:

Finally, I have to evaluate the length of the initial array so that I have an offset position of where to start adding the values from array 2. Just like getting the length of the array 2, I use the same evaluation on array 1:

My continuation condition will be while ~n is still less than the length of array 2

Now all that is left is to iterate over array 2, grab each element one at a time, and insert them into ~newArray, starting at an offset while also remembering to increment my iterator:

Starting at the offset, get the next element from array 2 and set it in ~newArray.

The final calculation will look like this:

The Loop call to pull the product list from WooCommerce:

The resulting multiple response variables:

Formatted as a custom function for reuse – ENJOY – it’s worth your WHILE

Like this tip on using the new WHILE function in FileMaker? Check out more FileMaker Tips-N-Tricks from our development team. 

**This article is provided for free and as-is. Use, enjoy, learn and eXperiment at your own risk – but have fun! eXcelisys does not offer any free support or free assistance with any of the contents of this blog post. If you would like help or assistance, please consider retaining eXcelisys’ FileMaker Pro consulting & development services.

Founded in 2001, eXcelisys (www.excelisys.com) is an FBA Partner and FileMaker Certified developer organization. eXcelisys specializes in designing, developing, customizing, supporting, consulting, migrating, upgrading, fixing, and integrating database solutions for Desktop, Mobile, and Web applications. Our core technology competencies are the Claris FileMaker desktop and web tools suite, MySQL and PostgreSQL for database frameworks, along with WordPress, PHP, CodeIgniter, Joomla, Drupal, Magento and WooCommerce for websites and web applications. Aside from providing eXcellent customer service, our goals are to use these technologies to intuitively automate your organization’s data solution needs seamlessly and flawlessly across the web, mobile, and desktop platforms. Contact eXcelisys anytime for a free estimate and consultation about making your business more efficient through intuitive and effective software automation. 866-592-9235.