When I set out to write a series of articles highlighting my favorite new features of the recently released FileMaker 18 Platform, I did not expect to write three posts focusing only on the While ( ) function. I want to thank the faithful who have stuck with me through what has become a three-part dissertation.
For those of you just joining me, While ( ) is a new function similar in syntax to the Let ( ) function. During execution though, While ( ) is able to repeat the designated portion of logic within the function while the condition is true, before returning the final result. If you missed the insight and examples I have already shared in While I Was Away: The First Part or While I Was Away: The Second Part, I encourage you to do so now. I’ll wait for you to get back.
Given the power and possibilities with While ( ) in our toolkit, I trust you understand my excitement and why I have been unable to stop rambling on about it. As I begin this final installment with my final example, I realize that this also becomes my third post that discusses the FileMaker script parameter.
Back in Evolution of the Script Parameter and Evolution of the Script Parameter Redux, I examined the various methods I have used over the years to send multiple values to a script. My current method involves formatting these multiple “parameters” as name / value pairs in a JSON object. Then using a custom recursive function that relies heavily on the Evaluate ( ) function coupled with a specific property of the Let ( ) function, I am able to dynamically parse these parameters as local variables using a single script step.
Since While ( ) is ideally suited to be a replacement where recursion is employed, I decided to re-write my custom function using the While ( ) function. It becomes the following:
While ( [ index = 0 ; keyList = JSONListKeys ( json ; "" ) ; keyCount = ValueCount ( keyList ) ; out = 0 ] ; index < keyCount ; [ index = index + 1 ; key = GetValue ( keyList ; index ) ; rawValue = JSONGetElement ( json ; key ) ; value = Case ( Left ( rawValue ; 6 ) = "<|DT|>" ; GetAsDate ( Substitute ( rawValue ; "<|DT|>" ; "" ) ) ; Left ( rawValue ; 6 ) = "<|TM|>" ; GetAsTime ( Substitute ( rawValue ; "<|TM|>" ; "" ) ) ; Left ( rawValue ; 6 ) = "<|TS|>" ; GetAsTimestamp ( Substitute ( rawValue ; "<|TS|>" ; "" ) ) ; rawValue ) ; out = out + Evaluate ( "Let ( [ $" & key & " = " & Quote ( value ) & " ] ; 1 )" ) ] ; out )
The first set of square brackets contains the initial variable declarations. It is here that the function determines how many name / value pairs are included in the script parameter. The exit condition compares the index value to the total to ensure the logic is repeated for each name / value pair. The second set of brackets contains the logic which is repeated while the conditional statement remains true. In this example, the index value indicates which value to extract based on the name extracted from the key list for that iteration. Finally with the Name and the Value both known, the Evaluate ( ) function allows the calculation to be written in a dynamic way since the Let ( ) function can declare a local script variable without using the Set Variable script step.
As noted in my earlier post, I found it easiest to handle date, time, and timestamp values as a string and include a prefix to designate the data type when the JSON object is assembled. Then, once the value is extracted for the current iteration of the repeated logic, a simple check for these prefixes allow us to easily convert the value back into the correct data type.
After making the switch in my custom function, I found myself asking the question “Is it worth the time to change this function everywhere I’ve already implemented it?” In my other testing of the While ( ) function, I noticed some performance improvement, but I wanted to get more empirical evidence before deciding to revisit every solution I’ve worked on since switching to this new method of handling script parameters.
I started out by writing a simple script that would build a JSON object in a global variable with 5,000 name / value pairs. Then, I wrote two additional sample scripts, one for each method of parsing the results, and used this global variable as the input to my old custom function and then my new custom function to see how long it took to declare 5,000 local variables. I added a couple of steps to each script to capture the start and end times using Get ( CurrentTimeUTCMilliseconds ) and a final step to calculate the elapsed time.
When using my original custom function during one test, it took about 1 minute and 34 seconds to declare all 5,000 local variables. (The exact time was 1:34.128, but I digress since I have seen it only take about 55 seconds on a separate occasion.) When using my new custom function built around the While ( ) function, the 5,000 script variables were assigned in just under 5 seconds. Given the obvious time difference which is at least one order of magnitude, I have deemed it worth the effort to use While ( ) when and where ever possible.
It bears repeating that in my testing I purposely used a large data set to more easily see any time difference between the two methods. If your recursive custom function is only operating on a handful values, any performance improvement may be negligible and not worth the additional programming time to replace each custom function with its While ( ) equivalent.
Before I close, there is one more thing I had to consider: Do I stop using recursive custom functions and just replace the function in the corresponding Set Variable or Set Field script step with its While ( ) equivalent. That’s a good question if I do say so myself. I did not detail it above, but I did run some tests on this scenario. I used the same global variable containing the 5,000 name / value pairs, but replaced the custom function with the full calculation as shown above. The difference between the two While ( ) function methods was negligible, consistently completing the operation in the same 4.5 to 5.5 second range. The benefit to replacing the custom function with the calculation is that fewer mouse clicks are required to view the calculation if a change is needed. However, I lose the portability of the code since custom functions can be easily imported from one solution to another. For this reason primarily, I believe the answer should be a resounding “No”. It is best to maintain the custom functions and simply update the recursive ones using the While ( ) function.
That’s all I have for now. Again, thank you for humoring me as I have spoken at length on the While ( ) function. Next time, we can explore another new feature of FileMaker 18 that has me equally excited.