Log in
Hot & Trending on FMT

Scripts or Custom Functions?

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.

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.

Media

Soliant Consulting

Soliant Consulting employs the largest FileMaker development team in the world. We have a deep bench of talent starting with our CEO, Bob Bowers: he's co-authored seven books, led the team that has written six editions of the Authorized Training Series for FileMaker, Inc., spoken at more than a dozen FileMaker Developer Conferences, and has taught literally thousands of students spanning two decades of leadership in the field. Certified Expertise Soliant's team is certified in every version of FileMaker Pro and are specialists in building high-performance databases, hybrid web-FileMaker applications, integrations with a range of technologies including SQL, and conversions from prior versions. We work with your solution as is or can build from scratch. Find out how we can take your FileMaker databases to the next level.

Website: www.soliantconsulting.com/filemaker