The A-Z of FileMaker: Z is for Undo

And here we are at the end of the A-Z of FileMaker. But undo doesn’t start with Z. Of course, Z is the ubiquitous application keyboard shortcut for the undo command. Use either Ctrl-Z for Windows or Cmd-Z for macOS.

Over the years of FileMaker Pro versions, there have been multiple steps of undo added as well as new areas where undo can be used.

The many steps of Undo (and Redo)

If the undo command is available to you, there are usually many undo steps available (sometimes called an undo stack). This means that if you do something wrong, followed by a series of other actions, you can use the undo command repeatedly to step back through the actions.

Let’s say you delete an object on a layout. Then follow with resizing a field, making a label bold, then adding another field. That’s four actions. If you decide that you should not have deleted the object, and it would be difficult to recreate, then you can call the undo command four times.

If you accidentally went one undo step too far, you can step forward again using the redo command – Ctrl-Shift-Z on Windows or Cmd-Shift-Z on macOS.

The Edit menu shows which step is next to undo or redo. If there is no undo or redo available, the command will be greyed out and say Can’t Undo or Can’t Redo.

undo redo commands

The persistence and therefore availability of the undo stack varies according to the situation.

Where can you undo? And how persistent is it?

There are many places in the current version of FileMaker Pro where you can undo your actions. This has been greatly improved over the years. Note that this list is of places where there is a specific undo command available. In other places, you may be able to Cancel a dialog to effectively cancel all changes made there.


The main time I use undo is around 3am and it usually tells me that it is time to stop and go to bed.

Layout mode

As mentioned above, you can undo actions in Layout mode. This has recently been given great functionality in that the undo stack is persistent across mode changes. This means that you can make some changes to your layout, check them in Browse and Preview mode, and then return to Layout mode to undo any changes you need.

The undo stack for a layout is lost when you switch layouts.


If you want to edit a number of layouts and maintain their undo stacks, you can do that in different windows.

Browse mode

Although there is undo available in Browse mode, it is very limited and not often useful. The undo stack is maintained for changes made within a field. For example, you could add some text, format a word in bold, delete some text, then add some more. These steps can be undone.

But the undo stack is lost as soon as you leave the field. If you have not committed the record, changes to the record as a whole can be undone with the Records > Revert Record… command. But that will revert all changes to the record since it was last committed.

The one place where undo in a field is very useful is when you paste in formatted text. If you paste and then immediately undo, it will remove all the formatting from the text and take on the formatting set for the field.

Script Workspace

When creating and edits scripts, undo is available. The undo stack is available until the Script Workspace is closed. Even if you save a script, as long as you do not close the tab in the Script Workspace, the undo stack will be retained. You can even run the saved script (without closing the workspace) then go back and undo changes!

Relationship Graph

Changes to table occurrences and relationships in the FileMaker relationship graph (File > Manage > Database) can be undone. The undo stack is persistent until you close the Manage Database dialog, or if you make changes in either the Tables or Fields tabs.

Important times when Undo is NOT available – beware!!!

If you are about to do something in a FileMaker solution that is destructive with no undo available, you will generally be well warned.

The following actions have no undo available:

  • deleting a record or deleting a set of records
  • Records > Replace Field Contents…
  • File > Import Records…
  • deleting a field or a table (and the data contained therein)
  • performing a find – there is no way to revert to the previous found set

The A-Z of FileMaker: Y is for Year

Year (date) is one of the family of FileMaker date functions. It does something very simple – it extracts the year out of a given date. Now why would you ever want to do that? I’m glad you asked!

Reasons to use it

Summarising by year

When you want to summarise a set of records by the year of a date, you need access to the year itself. For example, if you wanted to group customer invoices by year (of invoice date), you can certainly sort the records by the date field. However, to summarise you will need a break field which is the year. It is a simple calculation field:

calculation yearThis will be the field used to define the sub-summary part:

subsummary by year

calculations based on year

There are a lot of useful calculations that involve dates. It is important to understand that a date is stored as a number. This means that when one date is subtracted from another the result is a number of days. If we are to use this result to calculate a person’s age in years, we would need to divide by 365 and then take the integer of the result. This will never be accurate around a person’s birthday due to having to account for leap years.

The basic calculation of a person’s age takes the current year and subtracts the year they were born. So we use the Year function on each date:

Year ( Get ( CurrentDate ) ) – Year ( DateBirth )

This returns an accurate whole number unless the person has not had their birthday in the current year.

An Accurate age calculation

To perform an accurate age calculation, we must include logic as to whether the person has had their birthday in the current year. In plain English, we would ask when their birthday is this year and compare that to the current date. This requires use of other date functions.

Their birthday in the current year is:

Date ( Month(DateBirth); Day(DateBirth); Year(Get(CurrentDate)) )

So, if this date is greater that the current date, they have not had their birthday – you need to subtract one (1) year from their age result above. Here is the full calculation using the Let function:

Let ([
  DOB = DateBirth;
  today = Get ( CurrentDate );
  birthday = Date(Month(DOB);Day(DOB);Year(today));
  notHadBirthday = (birthday > today)
  Year (today) - Year (DOB) - nothadBirthday

Remember to make this calculation unstored to ensure that it updates correctly each day.

Reasons not to use it

While the Year function is very useful, there are cases when you do not need to create a calculation.

Displaying the year of a date

If you need to display just the year for a particular date, that can most easily be done with date formatting. Select the field (or merge field text object) and set custom date formatting in the Inspector:

inspector custom date year

A field formatted in this way will display only the year. However, if the user clicks in the field, they will see the full date stored.

Finding records by year

You may be aware of the convention for finding a range of dates. When in Find mode, you enter a two dates separated by an ellipsis (…). For example, this


finds all dates in 2017. However, FileMaker allows a shortcut form when searching for all dates in a specific year – simply enter the four digit year and perform the find. FileMaker will construct the above date range for you and perform the search.

This shortcut is also available when searching for all dates in a specific month – searching for 12/2017 will find all dates in December 2017.

The shortcuts can also be used for range searches. Try searching for 2015…2017 and see what is returned.

The A-Z of FileMaker: X is for XY Coordinates

In mathematics, a flat plane or surface is defined by two axes X and Y. On a screen or piece of paper, the X axis goes across (remember an X is “a cross”); the Y axis goes up and down (remember a Y looks like a tree). The position of any point is defined by its position on the X and Y axes – the point coordinates.

Layout positions

Let’s consider a FileMaker layout.  Object coordinates are based on the definition of the origin point – being 0 on each axis. On a FileMaker layout, the origin is at the top left of the layout. Display rulers (View > Rulers) to confirm:

origin layout coordinates

The coordinates of every point on the layout are measured right of and down from the origin point.

Function Trivia

There is a function – FieldBounds ( fileName ; layoutName ; fieldName ) – which returns the location, in points, of each field boundary and the field’s rotation in degrees.

For example, the expression:

FieldBounds ( Get(FileName); Get(LayoutName); "task")


20 82 350 132 0

for a field named task on the current layout of the current file. The first four numbers are left, top, right and bottom coordinates in points.

Units of measurement

FileMaker Pro offers three units of measurement for position and size – inches (in), centimetres (cm), and points (pt). There are 72pt in one inch. It is easy to switch between these units by clicking on the unit symbol between the two rulers, or by clicking on the unit in the Position tab of the Inspector.

Layout Design Tip

When working with position and size, it is strongly recommended that you work in points. This provides whole numbers which are easier to work with – 20pt instead of 0.278in or 0.706cm. Some options (such as those in script steps) must be entered in points.

Inspector – Position tab

The first tab of the Inspector is the Position tab. The first section in that tab is the Position section.

inspector position tab


For any selected object, the Inspector shows the position of the left, top, right and bottom points. The coordinates of the top left corner of the selected object in the screenshot are ( 20, 82 ) – 20pt right of, and 82pt down from the origin.

The Inspector is very useful for both determining and adjusting the position of an object on a layout. Click in any of the position boxes, type a number and press tab accept the change and move the object. The same can also be done for size.


Often a text object containing merge fields is larger than what is needed to display the data on the layout. You can resize the text object with the Inspector Position so that it is the appropriate size for data display.

Where an object needs to be in exactly the same position on two or more layouts, using the Inspector to set and confirm the position is priceless.

Layout Parts

If you select a part label (see below where the Body part is selected), the Inspector displays the position of the bottom of the part and the height of the part. This can be very useful to make accurate adjustments to the size of any layout part.

layout part size

With no objects and no parts selected, the Inspector displays the width of the layout (to the explicit right edge). Again, this is useful for making accurate adjustments to the layout width.

Window position

XY coordinates also come into play when creating new windows. The options for the New Window script step provide settings for the position of the new window:

new window options

The position is defined (down) “from Top” and (across) “from Left”.

FileMaker Annoyance

Why would the order of Position be defined Y then X instead of X then Y?

Unlike object position, the origin point used for most new windows is the top left corner of the screen. The exception is for card windows where the origin is the top left corner of the layout of the parent window.

The Move/Resize Window script step allows you to move a window by defining the position relative to the origin used for the window type:

move resize window options

If you want to reposition a window relative to its current position, use the functions Get ( WindowLeft ) and Get ( WindowTop ). For example, to move the window down 100pt, use the expression:

Get ( WindowTop ) + 100
Strange window positioning

FileMaker Pro will allow you to position a window off the edge of the screen by entering a negative value for the left position. This has been used by FileMaker developers over many years to create utility windows that the user does not see. Often this is done for a cleaner user experience – whatever happens in that window is not seen by the user and is not rendered by FileMaker Pro for performance gains.

The A-Z of FileMaker: W is for Window

A window is a device used to display the user interface of an application. When a user opens a FileMaker solution, it will open a new window. Since FileMaker Pro 7, there has been support for multiple windows for a single file.

Opening  a new window

The simplest way to open a new window is to choose Window > New Window.  A new window created in this way is a duplicate of the current window – same size, same layout, same found set, same sort order. However, the windows are independent – each can display a different layout, found set and sort order.

A developer can also use the equivalent script step – New Window. This provides additional options such as a choice of four window styles (see below), setting the window name, changing the layout and setting the size/position. There are also options to show or hide various window features such as the Close button (see below).

new window options

A developer can also use the Go to Related Record script step to create a new window. Using the Show in new window option provides the same options as above.

go to related record options

FileMaker window styles

Scripted new windows provide a choice of four window styles.


This is the standard modeless FileMaker window. It is used to provide a second view into the file such as displaying a different set of records (perhaps from a different table) so that the two sets can be compared side by side.


This is just like a document window except that it is always on top of all other windows. If multiple floating windows are created, any one can be selected to be in front, but all floating windows will always be in front of any document windows.

floating window arrangement

Quirky behaviour?

If a script creates multiple floating windows, any of those windows can be selected and brought to the front. However, if a new floating window is created from an existing floating window (using Window > New Window), then that floating window remains behind all others (but still in front of document windows). Weird eh?


A dialog window is modal. Opening a dialog window will prevent access to all other windows for the current file. The dialog window must be closed to resume use of other windows. Dialog windows are therefore used in a similar way to custom dialogs but with more flexibility in the design (being based on a layout).


Card windows were introduced in FileMaker Pro 16. They are created inside the parent window and are modal to it – the parent window cannot be used until the card window is closed. However, card windows allow the use of other windows.

Being a new window, card windows can display a completely different context (layout) to the parent window. This makes them particularly useful in interface design.

Each window can have only one card window open at any time. The card window can be positioned outside the bounds of the parent window.

Window options

When scripting creation of a new window, there are options for display:

window display options

The options available vary by window style.

  • Document and Floating Document: all except Dim parent window
  • Dialog: all except Minimize and Dim parent window
  • Card: only Close and Dim parent window

When creating a new window, any of the available options can be turned off. However, developers are well advised to carefully test their choices to ensure that the user can proceed. For example, if there is no Close button on a Dialog or Card window, the user may not be able to close the window at all and therefore be effectively locked in the solution.

Window script triggers

There are four file triggers available through File > File Options… that relate to windows. They are:

  • OnFirstWindowOpen
  • OnLastWindowClose
  • OnWindowOpen
  • OnWindowClose

The OnFirstWindowOpen and OnLastWindowClose triggers are activated when the file opens and closes respectively.

OnWindowOpen is activated when any new window is opened for the file. This includes the first window when the file opens. It occurs after the OnFirstWindowOpen trigger.

OnWindowClose is activated when any window is closed for the file. This includes the last window is closed. It occurs before the OnLastWindowClose trigger.

The A-Z of FileMaker: V is for Variable

A variable is a named location in memory capable of storing a value. Depending on the type, a variable has a limited lifespan and accessibility. In programming, the accessibility of a variable is called its scope.

In FileMaker programming, there are three types of variables – global, local and Let. Unlike other programming environments, all FileMaker variables store text values. While the value itself may be a number or a date, it is important to understand that FileMaker treats all variables as text.

Global variables

Global variables are named with two $ signs. A developer may create a global variable called $$LastLayout which stores the name of the last layout visited by the user.

A global variable is accessible from within a user session for a file. This means that the value can be accessed from any layout, any window, during any script, using any mode, etc., from within the file. When the file is closed, the global variable is cleared and the value contained is lost.

The scope of a global variable means that it cannot be accessed from another FileMaker file. In addition, it cannot be accessed by another user of that file.

Due to their scope, global variables are sometimes called file variables.

Local variables

Local variables are named with a single $ sign. A developer may create a local variable called $counter to keep track of the number of times a loop has run.

A local variable is accessible from within the script in which it is defined. When the script ends, the local variable is cleared.

The scope of a local variable means that it cannot be accessed from another script. For example, if script A creates a local variable called $user and then calls script B, script B has no access to the $user variable.

Due to their scope, local variables are sometimes called script variables.

Let variables

Let variables are created within a Let statement when defining a calculation expression. While some FileMaker programmers define Let variables with a prefix such as @, it is not required – Let variables can be plain English words.

Let variables are limited in scope to within the calculation in which they are defined.

Creating, modifying and destroying variables

Global and local variables are usually created and modified with the Set Variable script step.  By definition, Let variables are created and modified within a Let statement in a calculation.

set variable script step
Set Variable script step options
Let calculation variables
Let calculation using variables
define any variable in a calculation

Global and local variables can be created and modified wherever a calculation is created. This can include web viewers, conditional formatting and visibility, and various calculated labels (tab names and button bar segment names). This can allow for some creative programming if you understand when these calculations are evaluated. 

In FileMaker programming, variables are created by assigning them a value. This is different to other programming environments where the variable is often created before assigning it a value.

Reassigning a variable value is done using the same method as creating the variable – using the same name. In cases like counters, the variable can be incremented using a value that references itself – $counter + 1.

Local and Let variables usually die a natural death when the script or calculation ends; global variables die when the file is closed. However, any variable can be explicitly destroyed by assigning it a null value. This is done in FileMaker using two double quote marks (“”).

Naming variables

FileMaker variables are not case sensitive – setting the value of $counter will reset the value of $Counter. However, global, local and Let variables can exist with the same name – you can have counter$counter and $$counter at the same time.

Although not advised, FileMaker variable names can contain spaces – they can be multi-word names. For readability, it is advised that variable names use either camel-case or underscores e.g. $myVariable, $my_variable.

Strange but true

Let variables can be used to redefine constants such as Pi and True. For example, if you redefine Pi using Pi = 4, any subsequent expression will use the variable rather than the function. Similarly, you can define True = 0 with some strange results!

Using variables

As stated in the introduction, variables are temporary storage locations for text. As such, variables are often created in a context in which the value is known and then used in a subsequent context where the value may no longer be accessible.

create related record

A classic case is the creation of a related record. The primary key of the parent record is stored in a variable before moving to the child record location. The new child record is created and the Set Field script step is used to populate the foreign key with the variable value:

create related record with variable

Simplify and improve efficiency

In a calculation Let statement, variables are often used to improve readability, reduce the length of expressions, and improve the calculation efficiency.  Consider the Let statement below:

Let calculation variables
Let calculation using variables

The variables, today and taskdate have been used to simplify their source and meaning. The proximity variable is used to calculate the number of days to the task date from today. Once this is done (once), it is used in the Case function over and over without needing to be recalculated. This is far more efficient that getting the current date and comparing it to the task due date each time it is needed in the Case function.

Pass values between scripts

While the scope of a local variable is limited to the script in which it is created, the value can be passed to a called script in Perform Script as a script parameter:

pass variable as parameter

In the called script, the script parameter could then be assigned to a variable for use in that script:

set script parameter as variable

Note that the $sortOrder variables exist independently in the parent and called scripts and can have different values. When the process returns to the parent script, it will resume with its own value of the variable that exists there.

However, if the variable value has been modified in the called script in some useful way, and that new value is required by the calling script, the value can be passed back as a script result using the Exit Script step.

exit script with variable

In the parent script, the value passed back can be retrieved using the Get ( ScriptResult) function.  In the following script code the script result is used to redefine the $sortOrder variable in line 10.

refine variable with script result

Many FileMaker developers think that global variables are needed to be able to access stored values between scripts. The sequences above show the ability to pass a value to a script, have it processed in some way, and then passed back to the calling script. This is standard programming practice available in FileMaker scripting.

The A-Z of FileMaker: U is for Unstored (calculation)

When using a calculation field, you have the option of storing the result or having it unstored. The latter option means that the FileMaker engine will calculate the result “when needed”. This is usually when the field is displayed on screen or used in a printed report.

calculation storage options

The default setting for a new calculation field is to store the result (the checkbox is unchecked).

FileMaker Annoyance

This is an example where the FileMaker interface is confusing. Checking the box (with a tick) means “yes, do not do this”; unchecking the box means “no, do this”. It would make more sense if the option was “Store the calculation results”. Just read it carefully for now.

Sometimes there is no choice to store the result

If you do not have the option to store the calculation result, FileMaker Pro will tell you:

calculation storage not allowed

A calculation result cannot be stored where it references any of the following:

  • related field
  • summary field
  • unstored calculation field
  • global field

The case for not storing the result (unstored calculation)

An unstored calculation will be forced to evaluate every time it is needed. This means that if the field is displayed on a layout, the calculation expression will be evaluated.

Continual re-evaluation is important when you need a dynamic result such as that based on the current date. The majority of calculations using the Get(CurrentDate ) function should be unstored.

Another advantage of not storing the result is that no disk space is required. With large numbers of records, this can result in significant file size reductions. For calculations that simply concatenate data into a useful format (such as full name or full address), storing these results will simply double the storage space required for the data.

When displaying related records, such as in a portal, the FileMaker client will fetch the entire record except for any of the following not being displayed:

  • unstored calculations
  • container fields
  • summary fields

Stored calculation results are fetched even if they are not displayed. So it may result in better performance to leave the results unstored.

The case for storing the result

Sometimes it is a good idea to store the calculation result.

An example is the line totals on an invoice – calculated by multiplying the quantity by the unit price. Once the line has been created, the result is not expected to change. The layout displaying the portal of invoice lines will draw faster with a stored result for each line total.

Stored calculations can be indexed. An index is used for faster searches on a field and is required if a field is used at the many end of a relationship.

Calculation results can only be stored if the data they reference is also stored. If you want to store a result in field B that references calculation field A, then the field A result must also be stored.

Alternatives to calculation fields

If you need a calculated result, you can also consider the use of auto-entered calculations (field options) or a scripted process to set a field by calculation. Used correctly, these options can provide more control over when and if a calculation is evaluated.

An example is the grand total of an invoice – the total of all the line totals less any discounts plus any taxes. Once the invoice has been created, the result is not expected to change. If the invoice grand total is stored, this will mean that summary fields based on the invoice totals such as those used in monthly or quarterly sales reports, will work faster because they are referencing static values rather than those that need to be evaluated on the fly.

However, if a calculation field is used, the invoice grand total cannot be stored because it references related fields (line totals). If the field is a simple number field, a scripted process can be used to update and store the invoice total when required.

What about other places where calculations are used?

Calculations are used in many places in FileMaker solutions including conditional formatting and visibility, button and tab names, and for record level access in security. In these cases, any calculation will be evaluated when required – the result is never stored. It follows that the judicious use of these features should be carefully considered as it can add considerable overhead to the interface.

The A-Z of FileMaker: T is for Trigger

A script trigger in a FileMaker solution is set to run a script when a specified event occurs. The first event for any file is when the file opens, or more specifically, when the first window opens. It is common that a script will run when a file is opened.

Trigger categories

There are three main categories of script triggers – file, layout and object. You can check out all the triggers and links to detail of how they work in the online FileMaker Pro Help.

File triggers include those for opening and closing windows – the first, the last and any windows. The most useful of these are OnFirstWindowOpen and OnLastWindowClose. These can run various housekeeping tasks when a file opens or closes.

File triggers can be set through File > File Options…

file script triggers

Layout triggers deal with loading layouts and records. For example, exiting or entering a layout can trigger a script. Some developers have used these triggers to build a Back/Forward navigation system building (amongst other things) a list of layouts for navigation.

Layout triggers can be set in Layout mode through Layouts > Layout Setup…

layout script triggers

Object triggers deal with actions on layout objects such as fields and panels. In a user interface context, object triggers can be used to enforce data entry rules or to validate data. The OnObjectValidate trigger activates before fields are validated and saved. This provides more options for providing feedback to the user than field option validation. However, it should be understood that triggers are not always activated – they are dependent on the context of the data entry.

Object triggers can be set in Layout mode by selecting the object and then Format > Set Script Triggers…

object script triggers

Other script triggers

Although not listed as script triggers in the FileMaker Help, other events can trigger a script to run. These include:

  • clicking a button (the first ever trigger!)
  • Perform Script  and Perform Script on Server script steps
  • Install OnTimer Script script step

Pre-event vs post-event triggers

Pre-event script triggers run the script before the event is processed by the database engine; post-event script triggers run the script after the event is processed.

Why is this important? It determines whether the trigger event can be cancelled. If the event has already been processed before the script runs, the event cannot be cancelled.

Pre-event triggers are powerful in that the script can be configured to cancel the event. This is done by exiting the script with a false result (0 or False).

An example of this is when you want to allow a maximum number of characters in a field.

A Tax File Number (TFN) in Australia is nine digits. So we might write a script to check the number of characters in the field in a script that runs OnObjectKeystroke (a pre-event trigger).

Consider the following script:

If [ Length ( data::TFN ) >= 9 ]
   Exit Script [ Result: False ]
   #do nothing
End If

Note that the script will run before the event (typing a new character) occurs. This means that we need to check the length of the field before the addition of the new character. If there were 5 characters in the field, the script would allow another character – the If statement returns false and the script exits doing nothing. If there were 9 characters in the field, the If statement would return true and the script would exit with a result of False. This will cancel the triggering event so the new character would not appear.

This script appears to do a good job. It certainly stops the 10th character being entered. However, it has a couple of problems.

  1. One problem is that if the field contains 9 characters, all keystrokes in the field are cancelled. This includes Delete (if you need to make any corrections) and Tab (if you want to exit to the next field). A better script will account for all possible valid keystrokes and allow them.
  2. Another problem is that the user can still enter invalid data in other ways than by simple character keystrokes. For example, a ten digit number can be pasted into the field. Other triggers or methods may be needed to watch for this.

The takeaway here is that your script trigger behaviours should be well tested to ensure that they behave in reasonable ways.

Developer tools

When working with a file with script triggers enabled, it can be difficult to troubleshoot exactly what is happening and when. This is where the Script Debugger provided in FileMaker Pro Advanced is very useful. With the script debugger open, you can navigate around a solution and see which events trigger which scripts, and even cancel them if needed. This is particularly so when first opening a file – open the Script Debugger then open the file to catch the OnFirstWindowOpen script.

The Tools in FileMaker Pro Advanced allow the developer to temporarily disable script triggers. Do this by choosing Tools > Debugging Controls > Disable Script Triggers.

Final Word

Script triggers are very powerful but with power comes great responsibility. Make sure you properly test all script triggers for expected behaviour. You have been warned!

The A-Z of FileMaker: S is for Self

Self is a function which returns the content of the object to which it is applied. Rather than explicitly naming a field, using the Self function means that an expression can be easily applied to another context with the same result.

Conditional formatting

One area where you will see the Self function being used is in conditional formatting. Let’s say you have a number field, outstanding amount, that you would like to display red when the amount is more than $1000. You can select the field on the layout and choose Format > Conditional…. Then you set the condition as Value is…greater than…1000.

conditional format value is

Setting the condition is easy – FileMaker Pro provides a lot of comparison operations in plain English. However, if you want to see how it works, change the Value is to Formula is. This exposes the expression being constructed for you:

conditional formula is

The Self function is used to reference the field to which the conditional formatting has been applied.

The great value in working this way is that if you duplicate the field object and specify another field (say, invoice total) then the conditional formatting will apply to the new field value. No changes required. Magic!

Field options

Another useful area for using the Self function is in field options – specifically in calculated auto-entry and calculated validation.

For example, if you have a field for State in an address, you may want to ensure that only uppercase letters are accepted. So you can use a auto-entered calculated value that replaces existing values:


Working from inside out, this expression starts with the lowercase version of any value in the field (using Self). It then filters it to just letters of the alphabet. Then it returns it as uppercase.

So if a user enters ”  n. s .w” into the State field, it will be corrected to “NSW”. And the beauty of this is that the expression can be copied and pasted into the auto-entered calculation for any field and it will just work.


Create a custom function called CleanUpperChar (text) which uses the same expression but with a text parameter instead of Self. Then when using the custom function, you enter CleanUpperChar (Self).

Where else can Self be used?

Self can also be used in Tooltips. This can be useful to be able to display the full contents of a field when the field object is too small.

field self tooltip

You can also construct a conditional tooltip based on the value of the field. If this mirrored the conditional formatting set, the tooltip could explain what the formatting means. For example, a red figure displays the tooltip “overdue”, while an orange figure displays “pending”, and a green figure displays “paid”.

Self can be used in Placeholder text but I cannot find any practical examples of that use. Self returns the content of the field but placeholder text only displays when the field is empty! If you have a use case, please let me know.

Where is Self not accepted?

Before you get too excited and want to use the Self function in everything to abstract your code, there are many areas where you cannot use it. FileMaker Pro will tell you where these are:

One example of this is when using Replace Field Contents with a calculated result. It seems to make sense that you could apply the above expression to a field and quickly clean up all existing content. But you cannot use the Self function. Fortunately, you can still do it – you just need to explicitly name the field.

Self cannot be used in an If statement when scripting. Again, it seems like it would make sense to be able to run a script in the context of the current object. But no go!

Consider yourself to have been Self-actualised!

The A-Z of FileMaker: R is for Recursion

The trite definition of recursion is “see recursion”. And further, that to understand recursion, one must first understand recursion!

According to the online Dictionary of Technical Terms, recursion is:

“…a process in which a function calls itself as a subroutine. This allows the function to be repeated several times, since it calls itself during its execution.”

FileMaker custom functions

In FileMaker programming, recursion is possible when creating custom functions. The definition of a recursive custom function will include the function itself. So the function is said to “call” itself when it evaluates an expression.

Care must be taken when designing recursive custom functions – they will need an exit point. If there is no valid exit point, the function will keep calling itself infinitely or until all memory is consumed. FileMaker custom functions will only allow a specified number of recursions until they exit and return ?.

Why do I need recursion?

Good question. There are some problems that require the same process to be repeated an indeterminate number of times.

A simple mathematical concept is the factorial – denoted by n! – the product of all positive integers less than or equal to n:

5! = 5 x 4 x 3 x 2 x 1 = 120

This could also be considered as

5 x (5 - 1) x (5 - 2) x (5 - 3) x (5 - 4)
n . (n - 1) . (n - 2) ... 2 . 1

The generalised process is:

n! = n . (n - 1)!

The factorial process takes the first number and multiplies it by the factorial of itself minus 1 until the last multiplier is 1. Clearly, the reason this needs recursion is that there will be the same number of multiplications as the value of the initial number.

How do we write a custom function in FileMaker Pro?

Well, first you need FileMaker Pro Advanced. This will provide access to File > Manage > Custom Functions…. When you create a new custom function, you will use the dialog box:

edit custom function

You give your custom function a name, create as many parameters as you need and define the function.

(As it happens, FileMaker Pro already provides a function called Factorial so you cannot use that name here.)

So here is my factorial custom function, Factorial_c:

factorial custom function

There is one parameter – number. Looking at the function definition:

Case ( number < 0; "?"; 
     number = 0; 1;  
     number > 1; number * Factorial_c ( number - 1); 
     1 )

It uses a Case function to test for multiple outcomes. The first test returns an error (?) if the number provided is negative.

The second test provides the special case where 0! = 1.

The third test is where the recursion occurs – for any number greater than 1, it multiplies that number by the factorial of the number one less than itself. Highlighted in bold is where the function calls itself.

The function has three exit points – when the number is less than zero, zero, or one. Otherwise, it will call itself.

Note: for simplicity, this function does not explicitly account for non-integer numbers.

So if the number passed was 4, the process goes:

Expression:   Factorial_c ( 4 )
First pass:   4 * Factorial_c ( 4 - 1)
Second pass:  4 * 3 * Factorial_c ( 3 - 1)
Third pass:   4 * 3 * 2 * Factorial_c ( 2 - 1)
Fourth pass:  4 * 3 * 2 * 1
Exit Result:  24

Is there a more practical example?

If you are not mathematical, well done for hanging in there! For your reward, here is a more practical example of recursion.

There may be cases where you need to create an acronym of a phrase. For example, Australian Computer Society would return ACS.

So we create a custom function called Acronym ( text ) with one parameter, text, and defined as:

Upper ( Left ( text; 1) ) &
If ( WordCount (text) > 1;
   Acronym ( RightWords (text; WordCount(text)-1) )

The recursion (highlighted in bold) simply takes the current string less the first word (by getting the all the words from the right except the first one).

The custom function calls itself whenever the word count is more than one. The exit point is when the text has one word (or less).

So if the text passed was “new south wales”, the process goes:

Expression:   Acronym ( "new south wales" )
First pass:   "N" & Acronym ( "south wales" )
Second pass:  "NS" & Acronym ( "wales" )
Exit Result:  "NSW"

To reiterate, this recursive custom function will work for a string of any* number of words. That is the beauty of recursion in the efficiency of the code.

Bonus points

For bonus points, it would be useful if the acronym ignored certain words like and, of, &, etc. For example, the Australian Bureau of Statistics should be ABS rather than ABOS.

How could you modify the function to ignore defined words? Perhaps using the Substitute function?

* Recursive limits in FileMaker

As mentioned above, the FileMaker calculation engine will allow a certain number of recursions before exiting with an error (?). For standard recursion, this limit is 10,000. For the special case of tail recursion, the limit is 50,000.

So what is standard recursion? It is when the calculation engine needs to preserve a previous result (in a stack) for each recursion. Both examples above do this. For the acronym function, the calculation engine needs to remember all the previous letters while it runs the next acronym recursion on the shorter text string. When the exit point is reached, the result is returned from the stack.

Then what is tail recursion? This is when a stack is not used because the entire result is passed to the next recursion. Each recursion is complete in its own right.

Here is an example of tail recursion for the Acronym function. The structure of the function is Acronym_t ( text ; result ). When using the function, the result always starts with a null value (“”). The result passed onto the next recursion is the progressive accumulation of the final result.

If ( WordCount (text) > 1;
   Acronym_t ( RightWords(text;WordCount(text)-1); 
               result & Upper(Left(text;1)) ); 
   result & Upper ( Left ( text; 1) ) 

The process goes:

Expression:   Acronym_t ( "New South Wales"; "" )
First pass:   Acronym_t ( "South Wales"; "N" )
Second pass:  Acronym_t ( "Wales"; "NS" )
Result:       "NSW"

Creating tail recursive custom functions requires a little more thought and effort. However, it is sometimes important to be able to exceed the standard 10,000 pass limit.

But I still don’t understand recursion!

Well then you need to read this article – The A-Z of FileMaker: R is for Recursion. See what I did there? :p

The A-Z of FileMaker: Q is for Quick Find

Quick Find searches records across multiple fields on a layout. This provides quick and easy searches of a FileMaker table much like Google searches work. This search method will often be sufficient for most user requests.

Using Quick Find (Browse mode)

Unlike standard searches in FileMaker Pro, Quick Find operates in Browse mode.

quick find browse

The user simply types their search criteria into the Search box in the status toolbar and presses Return/Enter to search. Matching records are found instantly.

The Quick Find operation searches all fields on the current layout except for summary, container and global fields. Quick Find will also search fields that are on the layout as merge fields (in a text object). Quick Find will search fields that are placed to the right of the explicit right edge of the layout (and therefore not visible or accessible in Browse mode).

FileMaker Pro provides access to recent Quick Finds via the down arrow in the search box:

quick find recent

Field indexing

The default indexing option for new fields in any table is None and Automatically create indexes as needed. This means that when Quick Find is first run, all fields on the layout will be indexed and will continue to maintain a field index. This can have consequences for file size where tables have large numbers of records and where fields have large indexes.

Search Operators

If you type more than one word in the search box, this perform a logical AND search for records that have all of the words typed. The only search operator supported by Quick Find is the match phrase operator (” “). This enforces a search for a specific string of text from the start of a word.

For example, searching for leichhardt street will find all records containing both the words leichhardt and street across any fields on the layout (not necessarily in the same field). Searching for “leichhardt street” will only find records with the exact phrase in any field.

Configuring Quick Find

Configuration is specific to a layout. When in Layout mode, choose Layouts > Layout Setup….

The checkbox at the bottom of the General tab in the dialog allows you to Enable Quick Find (uncheck to disable). If Quick Find is disabled, the Search box in the status toolbar is not accessible for that layout.

In Layout mode, show the Quick Find badges for fields on the layout by enabling View > Show > Quick Find. A small badge next to each field shows the Quick Find status.

quick find badges

A green badge shows the field is searchable. In the above screenshot, the First field and the text object containing Group merge field show green badges.

A yellow badge shows the field is searchable but that the search may take longer. The Title field above has indexing set to None. The Full Name field is an unstored calculation field. The yellow badge would also be shown for related fields on a layout.

The Photo Container field has no badge because it is not supported for Quick Find. The Last field has no badge because it has been excluded from Quick Find in the Data tab of the Inspector.

In summary, FileMaker Pro provides control of Quick Find for the entire layout and for individual fields on the layout. Use this to fine tune the behaviour of Quick Find for the user experience.

Scripting Quick Find

The Perform Quick Find script step allows you to craft a script to provide customised access to Quick Find. This may be via a global field on the layout rather than using the search box in the status toolbar. The user interface may be neatly presented in a popover or a card window.