Last week I was down in Miami for FileMaker DevCon 2012. This was a great event, as always (not least because Soliant picked up a second award from FileMaker Inc. as Business Partner of the Year in the Americas, but I digress). The technical level of the sessions has gone up over the years, with the addition of more and more features that encourage full-fledged programming within FileMaker.
I attended a session given by Todd Geist, whose talks I try not to miss. Among many other interesting points on reuse, Todd offered his take on custom functions in FileMaker. Todd’s said this, before, but still it rattled me a little to hear him reiterate that he prefers to avoid custom functions wherever possible, preferring to use scripts instead. “Heresy!” was my first thought, since I write custom functions for everything. But I thought the question merited some rational thinking.
Todd’s points were that custom functions aren’t accessible to all developers, since you need FileMaker Pro Advanced in order to define, modify and import them, and not everyone has the more expensive FMPA. Personally, I’d like to think that everyone involved in FileMaker development does have a copy of Advanced, but I recognize that this might not be the case. He also noted that custom functions introduce additional dependencies, since scripts or other logic that use custom functions will break if the functions aren’t already in the file. Again a fair point, though to me this kind of dependency management kind of goes with the territory — scripts that depend on other scripts will also break if the other scripts aren’t present, and they break less gracefully than custom functions, since you lose the name of the missing script, whereas custom function text is simply commented out, making it trivial to fix the dependency once the missing functions are installed.
But still, this made me reflect on the issue of custom functions versus scripts. Is one better than the other? Could you replace all your custom functions with scripts? Would you want to?
As a matter of programming, there is nothing you can do in a CF that you can’t do in a script. In fact, a key programming technique, namely looping, is much easier in scripts than in CFs. In CFs this requires recursive programming, which is more complex than looping, and is limited to a maximum of 50,000 iterations in a custom function, or more likely 10,000 unless you’re skilled at implementing what’s called “tail recursion” in your functions.
Still looking at functionality, again, everything you can do in a CF can be done in a script, and there are things that can be done in a script that cannot be done in a CF, such as directly manipulating database data: creating, editing or deleting records, for example.
Even though custom functions are a subset of scripts in terms of the tasks they can accomplish, they differ from scripts in two important ways.
Firstly, custom functions are tied to FileMaker’s calculation engine. This means they can be made to fire whenever the calc engine evaluates an expression. This can certainly be made to happen within a script, but at many other times as well, such as when conditional formatting is invoked, or custom record-level security. This link to the calc engine can be both a blessing and a curse — used too much outside of scripts, it can scatter important program logic through all the elements of a FileMaker solution and make the solution harder to maintain.
Another key difference, and frankly an advantage for custom functions, is their ability to explicitly accept multiple parameters. This is a key capability for any programming language, and it’s a capability that FileMaker scripts don’t have natively. This limitation can be overcome, though, by using any of a number of solid, mature techniques that have been developed for managing multiple parameters in FileMaker scripts.
One final area of comparison I wondered about was speed. I figured that custom functions surely had to execute more quickly than scripts. So I set up a test. I created a simple custom function called square() which takes a parameter x and returns the value of x squared, or x * x. I then wrote a script that did the same thing, taking a single parameter, squaring it, and returning the result from the script. Finally I wrote a pair of looping scripts, each one set up to run 500,000 times, one of them calling the square() function, the other one calling the corresponding script.
To my surprise, the results were virtually identical, with the script actually enjoying a slight edge in speed. The average time for a loop in the script calling the custom function was .0976 milliseconds. The average time for a loop in the script calling a script was .0952 milliseconds. (These results are an average of 2.5 million iterations of the respective loops).
Clearly the difference between these would be completely imperceptible to a user. The results suggest there is no meaningful speed penalty to using a script in place of a custom function.
I’m not quite ready to throw out all of my custom functions, but the results here certainly give me second thoughts. I’ve migrated to doing more and more in scripts anyway, rather than using the calc engine, mainly because doing so centralizes logic and makes it more debuggable. This may all push me even farther in that direction, we’ll see.
Hey Steve,
Thanks for attending my session. :-).
This topic has spawned a couple of interesting debates over the last few years. Just to clarify my position on custom functions let me say that I do use custom functions, but only when I can’t do the same thing with a script. I would like to be able to use them more, but I feel like the costs outweigh the benefits. I put a very high premium on portability. And custom functions are not as portable as they should be.
I am of the opinion that sharing is the most important thing we can do in programming. Anybody can write spaghetti code that works for their one project for some period of time. But truly useful code is used by many many people. And not just “used” but “improved” over time by many many people. Great code grows and evolves. And for that to happen it has to be portable.
The most portable thing we have is scripts. They can be organized in folders. They can be commented. They can be imported even by people without FM Advanced. And they are extremely powerful. More and more I am pulling logic out of the schema and relationship graph and putting it in scripts where I have some hope of organizing and maintaining them. The only place I have any hope of building something “module like” is an a folder of scripts.
You bring up some great points, like the one about multiple parameters. Scripts really suffer here. Everyone uses their own custom function or one of the few that enjoy widespread use to parse multiple parameters. I hate this 🙂 We really need native way to pass multiple params to scripts. This might be my number 1 feature request, because it would dramatically increase code reuse. I stick to native code here as well. I use the list() function a lot to pass up to 3 or 4 params. Beyond that I use Let variable strings, as I showed in the session.
Thanks for actually doing the speed test on recursion. I had been saying that recursion was one of the things I used custom functions for. I had assumed that scripts would be way slower. One less reason to use custom functions. 🙂
Thanks for the great post!
Todd
Hi Steve,
Thanks for the interesting post.
There are at least 2 things that custom functions can do and scripts can’t do (without plugins).
1. Define a calculation field that relies on recursion, for example, an exploding multi-key.
2. Certain calculated tool tips might require a custom function instead of a script at least until we have “on hover” script triggers.
I like script functions and custom functions, in the same way that I like chocolate and raw almond butter. 😉
Look forward to seeing further comments on this topic.
Tony
Great post, Steve. Todd’s sessions are always worthwhile; I’m sorry I missed this one. And your speed test results were very interesting, thanks for posting those as well.
@Todd: “We really need native way to pass multiple params to scripts.”
Amen, brother. Amen.
Geoff
@Tony, I definitely agree that CFs can do certain things that are unique to them. I tend to use them as an adjunct to scripted programming, and that's the place where I'm starting to see Todd's point of view.
I found to my surprise that a tail-recursive custom function with GetNthRecord is considerably slower than a script that just loops trough a recordset. It seems more specific to the GetNthRecord function rather than the custom function.
To me, ease of debugging is key. It always takes a while to get your head around a recursive custom function while a script with a loop can be clearly documented.
Thanks for the post!
Interesting post.
One possible use for a custom function that might be better than a script is for a startup verification with the idea that the verification is hidden from the debugger and that the esc key won’t stop it.
Testing for speed using a million iterations seems rather pointless to me if you are only using the single script step… 🙂 Will saving a milimicrosecond mean that much?
Hi Jack — I don’t think saving that tiny amount of time is significant at all, no. That was really my point — there is no speed advantage of custom functions over scripts that I can see.
Sorry to necropost, but as of 2020, this post is still the first search result I got for benchmarking recursive functions vs. looping scripts. For what it’s worth, I tested looped generation of a string 500,000 characters long, one character at a time, in FileMaker 18.
The script was a simple loop using a counter variable and Set Variable $Output to $Output & “a”. The custom function used the While function (not available when this post was written) to do the same process. In my test results, the script ran in 25 seconds, while the function ran in 14 seconds. Presumably some of the speedup comes from the While function not requiring individual function calls as recursion used to require.
This speed difference is a little unfortunate, because I always find it harder to debug a complex While function than a complex looping script.
You may want to consider using a JavaScript function for this kind of operation. In 19+, the implementation of this is simple but it can be done in older versions of FM as well.
Also beware of the lessons learned in the years since, about the immutability of a variable and how using “insert calculated result” can be faster than repeated “set variable” into the same variable.