Fun with FileMaker Functions by Pam Rotella
January 2012

FileMaker developers often think of the Custom Functions feature as the utility for advanced mathematics or statistics. Custom functions are great for math, as they save time when using the same mathematical formulas in multiple locations. Yet FileMaker's Custom Functions can help with much more than math.

The basic advantage of the Custom Functions feature is that it offers a place to store and name formulas that can then be used elsewhere. When a formula appears in perhaps dozens of calculated fields, even if simple, maintenance of that formula can become cumbersome for the programmer. Occasional adjustments would require that a programmer find each calculated field using the formula, and then change each in exactly the same way. Creating a custom function allows the programmer to update all calculation fields that use the formula at once, by updating the one function.

One type of formula sometimes better-managed with Custom Functions (Menu selections for version 11: File / Manage / Custom Functions) is basic text cleanup. Text cleanup calculations are very common and usually not mathematically complex, but they are often repeated multiple times within the same application.

Some programmers use functions to format name and address information, for example. Name calculations generally include an assortment of name fields -- usually a prefix, the first name, middle name, last name, and suffix -- along with considerations such as defining rules for empty fields, varying abbreviations vs. full names, altering name order, standardizing the format of prefixes and suffixes, omitting stray spaces and other characters, and so on. Including the same text cleanup code in each calculation field that varies name format would require maintenance of the same code across several fields. That's why custom functions can help with something as basic as a name.

Generating web site code is another potential use of the Custom Functions feature. Custom function calculations can include text strings and concantenated combinations of text with formulas, a characteristic conducive to web design formulas.

In database applications that include web design assistance, applying web code to field content often involves copying the same blocks of code from calculation field to calculation field. Multiple instances of the same code can make revisions tedious and vulnerable to mistakes. The Custom Functions feature can instead be used as a central location to manage code strings and formulas, and therefore one place to provide updates to all calculated fields where the same web design formulas are used.

To illustrate the usefulness of centralized formulas in web design applications, and to offer my readers something that's probably new to them, I'm providing a sample custom function below. It's a long one. This is a "simple" HTML character code replacement formula.

I've selected this sample to demonstrate that some text cleanup formulas are so long that you'd never, ever want to copy them from field to field. Take a look...

You may ask, why not put this formula into a field calculation, and then use the calculated field in place of references to the original text field?

My answer would be that the type of formula above is typically needed for several different fields. For example, there may be a title field, a summary, the full text, comments, and so on, all in need of HTML character code substitution. Repeating this particular formula in a calculation field for each, before using the new fields in additional calculations, is much less efficient than the one function available for use on any field that may contain HTML characters.

This example is longer than most formulas, but it does demonstrate how everyday web formatting can benefit from FileMaker's Custom Functions feature. Name this function "HTMLchars," and converting special symbols to HTML codes within a calculated field is as simple as this little statement (replace "FieldName" below with your own field's name):

HTMLchars(FieldName)

Here are a few details about the formula/function above, for FileMaker or HTML enthusiasts who may want to copy and paste the formula into their HTML-code-generating database right away:
1.) I didn't include all HTML text symbols, for example the greater than and less than symbols, the ampersand, various dashes and quotes, etc. have been omitted from this formula. You can adjust the formula to include them, based on your own needs. The reason I omitted them here is that sometimes this formula is used on fields that already have some HTML commands coded by hand, or added by previous database calculations. Writing HTML code assistance into your database will necessitate special considerations like that -- when and where to add automated coding, and how to allow overrides.
2.) I used HTML entity names and not the numeric codes. I feel that if people see the names, then they will be able to learn the HTML code equivalents more easily than by seeing a number. It's just my own personal preference, an effort to help people remember HTML code equivalents.
3.) To create this function, I added the function parameter "FIELD" -- representing the field to be evaluated.
4.) Some may question why I used multiple instances of the "Substitute" command rather than another FileMaker function. I used Substitute in order to replace every instance of every symbol in one calculated step. Others may use different methods, but this formula is one example of how to accomplish the goal immediately.

FileMaker can offer web designers an efficient way to automatically generate code around a simple phrase or file name pasted into a field -- perfect code, with no typos or forgotten quotation marks. The Custom Functions feature may help to simplify that code generation by providing a central location to store and maintain formulas, which can then be used in many different field calculations.

Web code generation and text cleanup are both non-mathematical scenarios where FileMaker's "Custom Functions" feature can increase the developer's efficiency and accuracy. Simply put, any formula used in multiple calculation fields is a good candidate for becoming a FileMaker custom function. With their single location and ease of use, custom functions make many types of formulas and their dependent calculation fields easier to maintain.

- Pam Rotella is a Senior FileMaker Developer with over 20 years of I.T./PC database programming experience, more than 15 of those years working with FileMaker. She frequently consults with major US corporations and medium-sized companies, and can be reached via e-mail at filemakertech@gmail.com.

DISCLAIMER: This site is not affiliated with FileMaker software company, but rather seeks to provide a forum for credible discussion on software use. Tips provided here are not intended as individual consultation or advice. All papers published here are the opinions of their respective authors, and are not necessarily endorsed by FileMakerPapers.com or Pam Rotella.