Last time I gave some of my reasons for enjoying custom functions so much. I also pointed out a few of the limitations of custom functions that I hope will be addressed some day. Until then, here are some simple and not-so-simple techniques you can use when working with custom functions to make them a bit easier to deal with.
All of the custom functions mentioned here (and a couple hundred others) are available in my open source custom function library.
First of all, I use a Mac, have for over twenty years, and won’t be developing on Windows as my primary platform any time soon. So this tip demonstrates how to give File>Manage>Custom Functions a keyboard shortcut on the Mac. I’m sure there’s a way to do it on Windows (other than Alt F M, etc. to navigate the menu from the keyboard).
I used to use OS X’s built-in ability to generally assign a keyboard shortcut to any application’s menu item by going to the Keyboard preference pane in System Preferences and assigning Shift-Cmd-C to the “Custom Functions…” (with an elipsis at the end, entered by pressing Opt-;), and that still works as is free. The downside to this was that I lost them whenever I did a clean install of OS X or moved to a new computer.
Some time ago I purchased a license to Keyboard Maestro for other reasons. This is a macro utility that allows general automation and is quite useful. One of its features is the ability to export its macros. I do this periodically and store them in Dropbox so they can be quickly imported to a new machine.
Whichever way you do it, while creating a keyboard shortcut for custom functions, consider doing so for some of the other items in the “Manage” submenu. I use Shift-Cmd-A for “Security” (from the days when it was called “Accounts”), Shift-Cmd-M for “Custom Menus”, and Shift-Cmd-V for “Value Lists”.
The first custom function in my library is called
_template. Any time I create a custom function I begin by duplicating this one. It has the following contents:
// Template // // Purpose: description // // Parameters: _param: description // // Requirements: requirements // // Author: Charles Ross // Version: 1.0 written 15-03-11 // // Notes: Notes // // Todo: To dos // // Example: // sample = result Let ( [ _result = devp.Nil ]; _result )
Since custom functions don’t yet use FileMaker 14’s new calculation dialog interface, which means that the font is variable width instead of monospaced, I still use Opt-Tab to align the various fields. It looks like the screen shot below when viewed within FileMaker, all nice and tidy.
Most of those fields are self-explanitory. The
Template line has a commented-out version of what appears above the calculation text. For example, the first line of my library’s
date.BusinessDays (written by Jeremy Bante) custom function is
// date.BusinessDays ( _start; _end )
This is because my GitHub repository also keeps text versions of each custom function and it’s a nicely formatted way of documenting what the parameters of the function are from within the text file.
Example portion is meant to contain one or more simple unit tests. Copying and pasting this into the data viewer should return
1. From the same
date.BusinessDays custom function, the header comments finish up with
// Example: // date.BusinessDays ( Date ( 1; 1; 2015 ); Date ( 2; 1; 2015 ) ) = 20
Sometimes the example portion gets quite complicated, and sometimes getting a
True value from it will require more setup since the function might be state-dependent, but it always gives a good idea of what the call to it will look like and what it will return.
FileMaker’s scripting and layout interfaces have organization tools. Primarily, scripts and layouts can be grouped into folders, and the first technique I use with custom functions emulates this feature and provides namespacing at the same time.
Every custom function in my library is prepended with a four-letter code, such as
devp for developer custom functions, or
lsts for those that handle list processing. That’s the namespace portion. But every prefix has a custom function that begins with the prefix, includes a more human-friendly name and fills the extra space with underscores, such as
devp_____ Developer Functions __________. When the custom function list is sorted alphabetically, these folder headings come before the functions, providing a fairly good organization scheme. These header functions only have commenting code that descripts the functions in that section:
// Functions that generally are only useful to the developer, making his or her life a bit easier, hopefully.
The prefixing scheme also has the advantage that it’s extremely unlikely that FileMaker will ever use it in naming their built-in custom functions, so there’s almost no chance that a future version of FileMaker will introduce a function that conflicts with something in my library.
The final naming convention I use deals with recursive helper functions. Some custom functions exist solely to be called by a wrapper function. When this is the case, the recursive function is the same as the wrapper function, but suffixed with an underscore. so I have functions like
lsts.QuickSort, which calls
rang.ListToRanges that calls
I have a
Let call in the template custom function because unless a function is trivially simple, I’ll use
Let to break the logic into discrete steps. Without a step debugger for custom functions, complex calculations can be very difficult to troubleshoot. One way to make this a bit easier is to store every major step in the function in its own variable. Then, if something isn’t working correctly, the
Let function can return individual variables instead of the final result so that some insight can be gained into what the function is doing.
Here’s the calculation code for
plug.IsReady, a function that returns true if the named plugin is installed and enabled:
Let( [ _plugins = Get ( InstalledFMPlugins ); _installed = PatternCount ( _plugins; _name ); // No need to continue if the plugin is not installed. _result = _installed and ( plug.Attribute ( _name; 3 ) = plug.Enabled ) ]; _result )
While building this function, if I find it’s not returning the expected value, I can replace the final
_result variable with either
_installed to see what they’re returning. This is very useful, but depends on there being variables which can be easily copied and pasted into the final result clause.
Local variables are those prepended with a single dollar sign and are usually used within scripts, but they’re also available in custom functions. In this context they can serve two useful purposes.
First of all, they can be used in place of
Let variables so that when a custom function is being evaluated from within a script that is running through the debugger, you can see the values that each variable is receiving. For
plug.IsReady above, doing so would make the function look like this:
Let( [ $_plugins = Get ( InstalledFMPlugins ); $_installed = PatternCount ( $_plugins; _name ); // No need to continue if the plugin is not installed. $_result = $_installed and ( plug.Attribute ( _name; 3 ) = "Enabled" ) ]; $_result
This will work exactly the same as the first version above, but after being evaluated within a script, the vlaues of
$_result would all be visible in the data viewer.
If you use local variables in this manner, I’d recommend including a flag that indicates if the function is being debugged. When this flag is
1, the local variable values will be kept after the function has finished executing. When it’s
0, the variables will be cleared out so they don’t clutter up the data viewer. Adapting this suggestion to the above function results in
Let( [ _debug = 0; $_plugins = Get ( InstalledFMPlugins ); $_installed = PatternCount ( $_plugins; _name ); // No need to continue if the plugin is not installed. $_result = $_installed and ( plug.Attribute ( _name; 3 ) = "Enabled" ); _result = $_result; _ = Case ( not _debug; Let ( [ $_plugins = devp.Nil; $_installed = devp.Nil; $_result = devp.Nil ]; devp.Nil ) ) ]; _result
Note the storing of
_result before clearing it out and that the
_ variable exists solely to allow for the execution of the
The other use for local variables is to store multiple values across recursive function calls or to store values that can’t be easily passed. An example of this from my library is
Let ( [ _highest_count = Max ( ValueCount ( _weights ); ValueCount ( _values ) ); // Create a list that multiplies each weight by its matching value. $_weighted_average_weights = _weights; $_weighted_average_values = _values; _code = "GetValue ( $_weighted_average_weights; %n ) * GetValue ( $_weighted_average_values; %n )"; _products = lsts.CustomList ( 1; _highest_count; _code ); _product_sum = lsts.Sum( _products ); _weight_sum = lsts.Sum( _weights ); $_weighted_average_weights = devp.Nil; $_weighted_average_values = devp.Nil; _result = _product_sum / _weight_sum ]; _result
In this case the weights and values can’t be directly passed to the
lsts.CustomList function, so they are temporarily stored in local variables, used, and then cleared out so they won’t clutter the data viewer. Also note the long names these local variables have. This is to reduce the liklihood that assigning them values here will conflict with other similarly named local variables (such as
$_values) that might exist in a calling script.
The last tip for dealing with custom functions, at least until FileMaker applies their new calculation interface to the custom function dialog, is to use an external text editor. I currently use Vim with the filemaker.vim plugin that provides syntax highlighting, line numbers, auto-formatting and documentation lookup. There’s also a bundle available for TextMate which serves similar purposes.
Custom functions are a powerful tool in the FileMaker utility belt, but there are still improvements that could be made in their organization and debugging. Hopefully the techniques above will aleviate those hurdles in the meantime.