Goodies 27: Custom Function Tools

Recently we noticed a new idea on the FileMaker Community, a wish to extend the custom functions dialog with some useful capabilities to execute a custom function or check the syntax. And that's what wish was my puzzle for the weekend to solve. I remembered we had something like this in FileMaker as database: Formatting and error checking expressions in FileMaker, where you can enter a formula, format syntax and check for errors. Let's put that from a script to C++ and do it within the plugin:

As you see we got two new buttons for the custom script editor dialog in FileMaker Pro. When you click Check Syntax, we take your expression and build a test expression around it. We wrap it in a Let statement to define the parameters as local variable and then we let FileMaker's Evaluate() function check for errors without executing it.

The second button executes the script. If you have comment lines starting with /// followed by the parameter name and an equal sign, we will take those to initialize the parameters. The result is shown in the dialog left to the buttons. If text is longer, you can scroll the area via mouse wheel. Please note that this will show result as text and looses formatting.

Please note that the variable assignments can use other custom functions. But if any of the functions call itself, you will call the version before your modifications. If Claris someday implements this into FileMaker Pro, they may do some trick to temporary replace the function with your edits to make sure recursion runs the new version. Please avoid endless loops or endless recursion as you may need to wait a long time.

Later we added the feature to more dialogs:

As you see we got a white box for the result and put a place holder text inside. And we had to do code to dynamically look for what controls FileMaker places there and measure their size to place our box between controls on the left and controls on the right.

Add/Remove table occurrences

For MBS FileMaker Plugin in version 12.0 or newer we change the buttons to be a segmented control with four segments to do the syntax check, evaluate and now add and remove table occurrences:

As you see we got a button labeled "+ TO" and one named "- TO". The labels may change later in case we localise it or find nice graphics for them. The buttons run some code from the plugin to lookup the field names in the table on the left with the table name from the popup menu above. This may help you when you have to copy calculations from field definitions to calculations in scripts or data viewer.

Internally we use a regular expression to do this and it looks for the field names and add the prefix for the table occurrence for you. Same for removing where we look for table::field combination and replace with only field names.

The feature is available on macOS for various dialogs including field definition dialog and data viewer.

Format button

Since version 13.5 of MBS Plugin, we have a format button labeled with "ƒ" for the calculation dialog in FileMaker. We parse the formula, detect which brackets belong together and then output the formula with additional whitespacing.

Let's start with a formula we found in our documentation:

You see the calculation has some spaces before and after the equal signs, but not much indention. Pressing the ƒ button on the bottom right toolbar, we get this output currently:

As you see we indent, add whitespace around operators and break up lines based on their length to show parameters. This is the first version and we have some rules on this, but may fine tune them in the next release to find a balanced formatting that most users are fine to use regularly.

Please try it and please report problems you see. The toolbar appears in the dialog if you either enabled it with the SyntaxColoring.SetCustomFunctionToolsEnabled function or by the checkbox in the preferences dialog.

Shortcuts for calculation editor
⌘ Rshows rulers.
⌘ Ishows invisible characters
⌘ Fshows find bar and go to find.
⌘ ⇧ Fshows find bar and go to replace.
⌘ Ggoes to next found text.
⌘ ⇧ Ggoes to previous found text (new in v14.0)
⌘ [reduce indention of text.
⌘ ]indent text to right.
⌘ Ccopy as text.
⌘ ⌥ Ccopy as styled text.
⌘ ⌥ ⇧ Ccopy as html text.

Option Click 27 Hide lines