May 7, 2021
FileMaker Tips #1 — Custom Functions
This is the first of a series of FileMaker Tips that I will be sharing with you all from my 5+ years of developing FileMaker solutions. To get started, I want to talk about custom functions.
Custom functions have been around for a long time in the Claris FileMaker Platform. Custom Functions are a way to write a re-usable calculation that can either be simple or complex to use throughout your system. It’s not too uncommon that when I find myself evaluating a calculation over and over again in my solutions that I consider converting it to a custom function. There are quite a few benefits from using Custom Functions and I’ll try to cover these below.
Cleaner Code:
One use case of custom functions is to make your code more readable. For example, your scripting may need to handle different applications from the Claris Family ( FileMaker Pro, FileMaker Go, WebDirect, Server ), different system platforms ( Mac, Windows, iOS, Web Browser), or different devices.
You may have found yourself using something like this in your scripting:
Now let’s take those Get(SystemPlatform) calls and make them a bit easier to read.
Now you don’t have to remember what the different results for Get(SystemPlatform) are and got some nice easy-to-read code to hand off to another developer. As you code, you’ll probably find other examples where you can make your code more readable and Custom Functions can add a nice way to assist.
Propagate Calculation Changes in a Flash
If you haven’t already, you’ll probably find yourself in a situation where you’ve needed to modify a repeated calculation across multiple scripts. Then your in the hole trying to find all the places you used that calculation, otherwise you’ll break something.
Consider putting that calculation into a custom function. For example, maybe you use a calculation in your scripting to format a body of text like the terms and conditions on a bunch of printable layouts.
If you put your template terms and conditions into a custom function, you can use that custom function to populate the terms on all the layouts. Down the road, you find that you need to make an adjustment to your terms. Go right into that custom function, make your changes, and BOOM you’ve updated all your print layouts with the new terms.
Save Yourself Some Time
Before you even consider figuring out how to write that complex calculation, check out Brian Dunning’s Free FileMaker Custom Functions. There are hundreds of custom functions developers have written up over the years that you can copy and paste into your solution.
And the developers are still adding to the repository!
Here’s a list of some of the most useful custom functions I’ve found myself re-using in the last 5 years of development:
A custom function which allows simple addition or extraction of values from a list. This is useful for adding and removing values from a list.
Returns the current set as a return delimited list. Uses tail recursion so it can support a found set of up to 50,000 records. This is much faster than using a summary field to get a list of field data.
Returns whether the current running script is running on a FileMaker Server instance. This is useful for determining if the script is running on server, either by a Perform Script on Server call or a Scheduled Script.
This function is like GetFieldName () for ExecuteSQL function. Make FQFN quoted and dot separated for ExecuteSQL function. This is useful for making SQL calls and protecting your SQL calls from breaking when Fields or Table Occurrences are renamed.
This function will help you to debug your SQL statement if it returns a “?”. Unfortunately this technique only works in the dataviewer (and not even with the “Edit Expression” window open, so you need to click on “Monitor” first).
Removes leading and trailing carriage returns, line feeds, vertical tabs, blanks…Very useful for cleaning up pasted data in fields since Users love to paste bad data. :)
Performance over scripting
There may be times where you find yourself looping over some variable to manipulate or evaluate it. While loops may be quick 3 lines to script, they can take a performance hit with larger volumes of data. Take for example, the function mentioned about GetFoundList (). If you were trying to get a found set of record ids, a loop or summary field may be fast for 100 records, but try running that loop with 10,000 or 20,000 records and you’ll start seeing the performance hit. A recursive custom function will run within the FileMaker Calculation Engine and speedily at that. You may want to switch to writing a recursive custom function to do that work for you. In practice, I’ve noticed a considerable performance difference between a custom function and a FileMaker script calculation for a larger set of records.
I will note here that performance gains with custom functions is really specific to the use-case of your scripting. There are times when you really will get no benefit out of a custom function. As a rule of thumb, if I see myself looping in my scripting or running a calculation recursively, I’ll move to using a custom function. Sometimes your performance hits in scripting may just be to some inefficient scripting, so always start there. Make your script as optimal as you can.
Custom Functions can be versatile
You may find yourself creating a custom function to calculate business days for a given date range and have a StartDate and EndDate parameter. This has been working great for all your projects so far and then you come across a client who is open 6 days a week instead of the usual 5. Now you’re stuck with a choice. Create a unique custom function for their case or modify your custom function to take an additional parameter for the NumBusinessDays being the number of days the business is open and StartOfWeek being the day to consider Day 1 of the new week. Now you can re-use this function for different businesses and cover a larger range of options.
The caveat to modifying an existing function is that any pre-existing calls in scripting/field calculations will need to be updated to pass in any new parameters.
Resources
There are quite a few sites that store custom FileMaker functions. Use the sites here as a starting point when you’re looking for one:
www.briandunning.com/filemaker-custom-functions/
Want Me to Cover a Specific Topic of FileMaker Development?
Comment below or email me at jeff@jeffreyhenry.me to make a request. If its worth writing a blog about, I will write one up or record a video to cover it!
Feel free to contact me if you need help with FileMaker Development as well. I have a competitive rate.
Happy coding!
