The New Jersey FileMaker Pro User Group’s November 30, 2011 meeting was held at Essex Computers in Paramus. Debi Rubel, Certified FileMaker Developer for FullCity Consulting, lead a group discussion of custom functions.
Members reviewed some of the advantages of custom functions, such as:
1. DRY (don’t repeat yourself) – the ability to store re-usable code
2. Storing global data outside of fields, away from easy viewing and editing
3. The ability to create recursive calculations
Using the Manage > Custom Functions dialog box, formulas can be created using FileMaker’s “calculation engine,” an interface similar to that used for the program’s field calculations. FileMaker Pro Advanced is required to create custom functions but, once saved, a new function can be used wherever FileMaker’s built-in functions are available: in field definitions, script steps, the data viewer – even other custom functions. An important distinction between custom and standard functions is the use of developer-created parameters, rather than direct access to fields.
FileMaker introduced custom functions with version 7. The current version (11) makes it even easier for developers to incorporate custom functions in their work through new copy/paste and import features.
Several custom functions were reviewed, including those for text, numbers, dates and times. Examples ranged from simple formatting options and mathematical standards such as median and hypotenuse, to more complex concepts involving date offsets that account for weekends and holidays, and recursive functions.
A recursive function is one that calls itself within its formula – much like a loop for scripts. And, as with loops, developers need to remember to provide an exit condition. FileMaker enforces recursion limits for both stack and tail recursion functions.
Variations of a function were shown to demonstrate methods for error-trapping, which can occur within the function and/or the calling calculation. Trapping for empty data and particular data types can be particularly helpful in ensuring correct outputs, and in preventing the dreaded “?” result when recursion limits are hit. Additional discussions revolved around the need to approach any particular problem with the end goal – the client’s needs – in mind.