I’ve always found it a good practice to try to reevaluate how I do things. Whether it’s questioning an old established method or just always keeping an eye out for new emergent technology, if you don’t move forward, you move backward. Especially in software development.
However, the search for better code sometimes starts with something as simple as reevaluating a fundamental concept, like the GetValue() function, and seeing where it takes you. Which, in our case, found us pushing this function to its limits and ultimately testing a new method for looping in FileMaker scripts.
This function is probably one of the most basic functions that developers use in the FileMaker function workspace. By using it within a loop in a script or within a while statement in a function, developers can systematically iterate through a return-separated list of data. For instance, GetValue ( List ( “3” ; “2” ; “1” ) ; 1 ) will return “3” since “3” is the first value in the list. This is a foundational task that most take for granted and wouldn’t think twice about in terms of efficiency. However, in our day-to-day development, we came across a little-known behavior… and this was the beginning of the search to find a better solution.
A Little-known Behavior
The problem is that this function can become very slow for large data sets of return-separated data. When put to a basic stress test of just looping through a returned separated list of UUIDs, the amount of time it took to loop through that list grew at an increasing rate as the size of the list of data increased. When things grow at an increasing rate, it’s generally considered exponential growth–which is bad news for the code trying to perform that action.
The chart below illustrates the results of our stress testing. With 500 values in the list, it took a quick 45 milli-seconds to loop through each value, giving us a time of 0.09 ms/record. Add more values to the list and not only does the amount of time to run through the list increase but so does the average amount of time it takes for each record to get processed (milli-seconds per record). At 50,000 values, it takes a whopping 33,541 milli-seconds, which translates to an average time of 1.67 ms/record.
This doesn’t mean the GetValue() function is built wrong or is no longer useful. In fact, this function is incredibly useful and, when used appropriately, can be a lifesaver given the frequency and breadth of functions within FileMaker that utilize return-separated lists. Just be aware of the size of the data set in your list!
Furthermore, this behavior is only noted when using the GetValue() within a looping structure. So one-time instances of the GetValue() aren’t a concern. It’s only when using GetValue() in a looping mechanism for large return-separated lists that we become concerned with efficiency.
Without being able to peek under the hood, it is hard to know what causes this behavior. However, it’s probable that every time the GetValue() function runs, it has to count every single return character in order to get to the correct value. For instance, GetValue ( $list ; 100) would probably have to count 99 return characters in order to find the 100th one, and therefore the 100th value. And then if doing this in a loop and incrementing to the next value it would probably have to count 100 return characters in order to find the 101st one, and therefore the 101st value. This would repeat until it finished the loop.
Doing this counting, and specifically recounting within a looping structure, would be one explanation of what causes this behavior. In either case, the question then is this: How does one go about finding a better solution to such a fundamental problem? The answer is to change our mindset and how we think of the problem.
Changing Our Mindset = Changing Our Paradigm
Simply put, a programming paradigm is a way you use a programming language to solve a problem. There are many different types of paradigms, the most employed being an Object Orientated Programming (or OOP) approach. Others include Functional, Procedural, and Logical. Different programming languages are more conducive to different types of paradigms, but the lines are usually blurry and not set in stone.
For instance, traditional OOP languages like C++ can utilize functional things like recursion, and modern functional programming languages have ways of structuring data that traditionally would be reserved for OOP-style languages. Ultimately, it’s less about what a language can do, and more about the way you write your code to solve the problem.
When using the GetValue() function to loop through data, we are taking a very common OOP approach to our looping scheme. So, in order to rethink how we loop, ultimately to find an alternative to GetValue(), we need to find another language or paradigm that approaches loops differently. One paradigm that does this is Functional Programming.
Functional Programming to the Rescue
Functional Programming, as its name suggests, features a type of programming style that is very function–centric. Data is passed back and forth as parameters of functions, and functions tend to be the focus of developers in this mindset. Without getting stuck too much in the weeds, here are some examples of how you might have already gotten yourself in a functional mindset without realizing it:
- Recursion. As mentioned, the ability to have a function recursively call itself isn’t unique to functional languages, but it is a basic building block of functional programming. Anytime you’ve implemented recursion, you’ve been in the functional mindset. FileMaker functions do support recursion and can also simulate recursion by using the while loop.
- Not having data types. Again not unique to the functional paradigm, and certainly not a requirement, but pure functional languages tend to treat all data as something like a string or list of characters. Which is exactly the way that FileMaker functions behave. Date and number data can be represented in a function, but there’s no separate data type within the function workspace for these things. Everything is just a blob of characters.
- Functions as Parameters. Using the Let statement (and in general, anytime you nest FileMaker functions as parameters) within another FileMaker function, you’re in the functional mindset.
Bridging the Gap – How to get in the functional mindset
To better illustrate the difference in programming paradigms we’ll write a sample function in two ways: one using an OOP style, the other using more of a functional approach.
Let’s imagine we had a custom function called TestFactorial that took an input, n, and returned the factorial of that number. For instance, TestFactorial (5) should return 120 ( 5 * 4 * 3 * 2 * 1 ).
We’d have two functions that looked like this:
TestFactorial – OOP Style
While( [ result = 1; index = 1 ]; index ≤ n ; [ result = index * result; index = index + 1 ]; result )
TestFactorial – Functional Style
Case( IsEmpty( n ) or n = 0 ; 1 ; n * TestFactorial ( n - 1 ) )
Let’s quickly discuss what’s going on here.
The OOP style Factorial uses a while loop as a control structure to loop over data. In this case, the data it’s looping over is the result object and the index object. As it loops, it updates the objects until it loops through the list and exits. Its main mode of looping is to update data objects until it reaches a certain number of data or count.
The functional version of this uses recursion to solve the problem. It takes the initial input, then multiplies it by the result of the same factorial function, but with an input decremented by one. All the way down until the input is 0, in which case the function returns 1, which then is returned to the previous function call and multiplied by 1, the result of which is returned and multiplied by 2 all the way up. The key takeaway is that the main mode of looping is to return a result until a list is empty.
To sum up: Both functions are correct and generate the desired result. The OOP style, using the GetValue() function loops until a counter reaches zero or some value. The Functional style loop operates until nothing is left to operate on. A minor, seemingly philosophical difference since the result is the same, but an important distinction both in the thought process and how it ultimately is implemented in the code.
Let’s take a look at what we set out to do initially, replace the GetValue() function in a loop, by applying this new thought process to our script – Operating until empty.
Replacing the GetValue() function
Below is the stress test script we had built initially that started this whole process. It uses an OOP mindset, and the GetValue() function to loop through a list, $List, of numbers. Just like our Factorial-OOP example, this one uses a $Count and an $Index to increment through a list, until it reaches the last value of that list.
It works in this case. But as mentioned with larger lists within the $List variable it gets increasingly slower because of how the Get Value() function operates in the background. This behavior is what we are trying to avoid, so we need a new way to loop through a list of data. To help wrap our heads around getting rid of GetValue() function, we need to view the $List variable from a functional perspective instead of an OOP one.
The ‘Old’ view way of doing this is entrenched in the OOP mindset, which treats the return-separated list of values as an array, where each value can be accessed by referencing its index similar to how we are using the GetValue().
From a functional perspective, a return-separated list is just a string of characters. Some functional languages even go as far as not having a native function equivalent to GetValue(). Instead, they have a concept of a Head and Tail; where Head is the first delimited value, and Tail is all the rest. Depending on the language implementation, the Head can be just the first character, or it can be more complex and use some combination of delimiters to determine what the defining line is. Regardless, the Head is the first, and the Tail is the rest. And you can use the Tail function to continually build smaller lists until your list is empty. In other words, we can use Head(s) and Tail(s) to loop until our Tail is empty.
So a theoretical function called List.Head ( $List ) would return “1” and List.Tail( $List ) would be equivalent to List ( 2 ; 3 ). Or to put it a different way, the Head function is equivalent to GetValue ( $List ; 1 ), and the Tail function returns everything else remaining that we still need to process.
Building Our Functions
Since we are working within the context of the List function, we want to use a return character as our delimiter and by using some built-in FileMaker Text Functions, we end up with some custom functions that look like this:
Let( [ ~position = Position( MyList ; "¶" ; 1 ; 1 ); ~result = Case(IsEmpty( MyList ) ; "" ; ~position = 0 or IsEmpty( ~position ); MyList ; Left( myList ; ~position - 1) ) ] ; ~result )
Let( [ ~position = Position( MyList ; "¶" ; 1 ; 1 ); ~result = Case(IsEmpty( MyList ) ; "" ; ~position = 0 or IsEmpty( ~position ); "" ; Replace ( MyList ; 1 ; ~position; "" )) ]; ~result )
So List.Head( List( “1” ; “2” ; “3” ; “4” ) ) returns “1”
And List.Tail( List( “1” ; “2” ; “3” ; “4” ) ) returns the same as List( “2”; “3”; “4” )
And now we are ready to get rid of the GetValue() function from our script. Below is what that might loop like:
The resulting script is quite different, despite producing similar results. To start, we don’t need to have a $Count or an $Index value for our loop. Something that would be considered sacrilegious for someone ingrained with an OOP mentality. Instead, we use an empty list as our exit condition from the loop. As we loop through, we continue to use the List.Tail function to continually reduce our list until there is nothing left, in which case we exit. And instead of using the GetValue() function, we can use the List.Head() function to grab just the first value every time from the constantly reduced $List until the $List is empty.
The resulting script is also much faster. When put through the same stress test, it performed at almost a linear scale – meaning that instead of getting exponentially slower as the amount of data increased, the amount of time it took to run was proportional to the size of the list of data.
We included a graph depicting this and also attached a sample file below, so you don’t have to take our word for it.
And just like that, we’ve applied a functional perspective to our FileMaker development to optimize a little-known behavior in a commonly used FileMaker function.
Happy coding, everyone!