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 ]
Else
   #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: P is for Parameter

A parameter is text that is passed to a FileMaker script when it runs. The text is often used to make decisions during the script. Using a script parameter allows the developer to construct a script that can run differently according to the conditions.

Running a script

A script can be called to run in a number of ways:

  • user clicks a button
  • event occurs such as switching layouts
  • user selects a script in the Scripts menu
  • script runs the Perform Script or Perform Script on Server step
  • FileMaker Server schedule runs a script
  • user selects a custom menu item defined to run a script
  • timer running in a window runs a script

Passing a parameter

In most cases, when a script is called a parameter can be passed to the script. In the simple case of defining a button, when the action is to Perform Script, the (optional) script parameter can be defined in the lower part of the Specify Script dialog:

specify script dialog

The Edit… button provides access to the FileMaker calculation engine such that the script parameter can be a calculated result determined when the script runs.

Parameter is text

It is important to understand that a script parameter is passed as text. This can have important implications when the data is expected to be used as number or date. It also means that raw container field contents cannot be passed as parameters – they must first be converted to text (e.g. using Base64 encoding).

Accessing a parameter

A parameter passed to a script is available only within that script. It can be accessed at any time using the Get ( ScriptParameter ) function.

For convenience, a developer may choose to extract the parameter into a variable (or multiple variables) at the start of the script. This can provide a readable name for the parameter making code more self-documenting.

For example, if the script parameter being passed is the name of a customer and that data will be used to name a new card window, the following code might be used:

New Window [ Style: Card; Name: Get(ScriptParameter);                   Using layout: “Contact Details”]

Without knowing the contents of the script parameter, it is impossible to know how the window will be named. Compare that with the following:

Set Variable [ $customerName; Value: Get(ScriptParameter)]
New Window [ Style: Card; Name: $customerName; 
             Using layout: “Contact Details”]

It is now clear that the new card window will be given the customer name.

Conditional scripting using a parameter

A parameter may be used to control how a script runs.

One example is when the parameter is queried in an If script step. The parameter may contain contextual data such as where the script is being run. Depending on the layout, the script may run different script steps.

For example, the parameter could be set with the Get(LayoutTableName) function. This returns the table occurrence name of the current layout.

set script parameter to layout table

If the table occurrences underlying each layout are named using a naming convention where the first word is the name of the table, this can be used to run different script steps based on the layout where the script was called.

Set Variable [ $table; 
   Value:LeftWords(Get(ScriptParameter) ; 1 )]
If [ $table = "person" ]
   #do these steps
Else If [ $table = "invoice" ] 
   #do these steps
Else If [ $table = "product" ] 
   #do these steps
End If

Similarly, if the parameter contained a number, this could be used to control a loop. This might be to control the number of new records created or the number of records to be processed.

Passing multiple parameters

In most cases, it is sufficient to pass one piece of data as a parameter. To pass more than one parameter, you need to think about how to bundle up multiple pieces of data and then unpack them in the script.

A common method is to use the List function to create a return separated list of values. Knowing the number and order of the values, you can effectively unpack the data into different variables in the script.

For example, if a person’s first name, last name and student number is passed using the List function:

List ( person::First; person::Last; person::StudentNo )

This could be unpacked in the script thus:

Set Variable [ $p; Value: Get ( ScriptParameter ) ]
Set Variable [ $first; Value: GetValue ( $p; 1 ) ]
Set Variable [ $last; Value: GetValue ( $p; 2 ) ]
Set Variable [ $studentno; Value: GetValue ( $p; 3 ) ]

It is also possible to adopt a more structured approach and pass data in a parameter as name/value pairs. This is easily done in FileMaker Pro 16 using JSON format and extracting the data from the parameter using the new JSON functions.

The A-Z of FileMaker: L is for Loop

L is for Loop

FileMaker scripting provides the features of any structured programming language. Conditional and looping structures are key features.

Conditional structures like the If / End If in FileMaker scripting allow for different subroutines to run on the evaluation of a condition. See our previous blog article on If statements.

A loop is a series of instructions that are continuously executed until a condition is reached. A simple example is working through a set of records performing certain operations until the last record is reached.

Loops in everyday life

We often perform repetitive processes. These can be considered to be loops with an exit condition.

peel potatoes loop

For example, if you had to peel a bag of potatoes, you would prepare by getting the equipment required and opening the bag. You pick up a potato, peel it, rinse it and place it in a bowl. Then you check for more potatoes. If there are more, you pick up the next one and repeat – peel, rinse, place. When all the potatoes are gone, you finish and clean up.

Loop steps in FileMaker scripting

The basic loop script steps in FileMaker scripting are Loop and End Loop. The script steps placed between (‘inside the loop’) form the looping operation. Any loop is expected to have two components:

  • procedures to be carried out each time through the loop
  • an exit condition which, when true, will exit the loop
Infinite loops are bad

A loop without a valid exit condition is called an infinite loop – it will continue without ever exiting. This is not something you want in a script.

In many cases, there are steps before the loop to set up the environment. For example, if the loop is going to work through a set of records, you may ensure it goes to the first record of the found set before entering the loop.

Example: Loop creating new records from a list

Sometimes you have a list of values that you want to turn into a list of records.

In this example, we have a list of children’s names in a global field. A scripted button will create records related to the current parent.

The screenshot above shows the Parent layout. In the Children section, the names are in a global text field. Child records will display in a portal at the right. The relationship is a simple one to many from Parent to Child.

Why would you be doing this?

Good question. Sometimes it is easier for users to enter a list of data than to create new records. This may be by pasting from another source. Then you provide an easy process to create the required records.

The looping script will work through the child values creating a new record for each. The challenge here is that we do not know ahead of time how many children will be in the list. So the script will count the names to know how many times to repeat the loop.

The pseudocode for the looping script is:

Store the parent ID and the child list
Count the child records required
Set loop counter
Go to the child table 
Enter the loop
Create new record for nth child in list
Exit loop if last child processed
Else increment counter
Return to the parent table

Here is the script:

looping script

How does it work?

The first four steps are setting variables to store required values:

  1. primary key of the parent record
  2. list of children from the global field
  3. count of the children
  4. loop counter initialised to 1

Then we go to the Child layout to create new records in the loop.

Inside the loop, the first step is to create a new record. Two Set Field steps are used to set the foreign key (ID parent) and the child’s name. The child’s name is  extracted from the child list using the GetValue function and the $counter to know which one to take.

GetValue ( $children ; $counter )

The loop will then exit if the $counter is greater than or equal to the $childcount i.e. if the required number of child records has been created. If the loop does not exit, it continues on to increment (add 1 to) the $counter and then loop back to the start where it creates another new record.

When the loop exits, the script returns to the original layout (where it started).

Q. Moving script steps around

Consider what might be the positive effects of moving the Exit Loop If step to line 7 – being the first step in the loop.

[see the end of the article for a suggested answer]

Anything else needed?

This script is bare bones. At a minimum, you will probably also check that the user is happy to proceed and error trap to stop the script if no child names exist. You may also choose to clear the global field if the loop ends successfully.

dialog check record loop create

dialog error loop no children

The above example shows the simple structure of a looping process with:

  • setup before the loop
  • doing things in the loop
  • checking the exit condition
  • returning to the start
Loop alternatives

There are many times that a loop will be the right thing to do. However, you need to be aware of alternative methods and possible speed implications of looping scripts.

For example, if you need to loop through a found set of records and set fields according to some conditions, then consider using the Replace Field Contents script step.

If a looping script ‘touches’ every record on the way through, that may slow the process. Consider the methods to avoid opening and closing records, and even jumping to key records in the set. A Freeze Window script step before the loop may result in speed improvements.

A. Moving script steps around

If you move the Exit Loop If step to the start of the loop, this will have the positive effect that a new record would not be created unless needed. Since the $counter has been set to 1 before entering the loop, if there are no values in the children list ($childcount = 0), the loop will exit immediately.

What do you use loops for?

Feel free to leave a comment below.

The A-Z of FileMaker: I is for If statement

I is for If Statements

The word “if” indicates a condition and an action – in the event that the condition is met, the action will be performed. In real life, an example might be “If you eat your dinner, then you can have dessert”.

In the computer world, the condition is expressed in If statements as a Boolean test. We have previously explored Boolean values and expressions in another A-Z post.

FileMaker Pro uses if statements in both calculations and scripting. The structure of each varies slightly as we will explore below.

If function in a calculation

The If function is classified as a logical function. It has the following format:

If(test;result1{;result2})

If is the name of the function; test, result1 and result2 are parameters. The result2 parameter is optional and is shown in curly braces {} because it is optional.

When the test is evaluated, if the result is true the function returns result1; if the test is false the function returns result2. If the test is false and no result2 is specified, the function returns a null (empty) result.

Consider the following examples calculating a discount amount for VIP customers:

Example 1:  If ( IsEmpty (cust::VIP); ""; 0.2 )
Example 2:  If ( not IsEmpty (cust::VIP); 0.2; "")
Example 3:  If ( not IsEmpty (cust::VIP); 0.2 )

For VIP customers, the VIP field contains the string “VIP”. Each example uses the IsEmpty function to query the VIP field in the cust (customer) table.

Example 1 says that if the VIP field is empty, return a null result (“”); otherwise return 0.2 (20%).

Example 2 reverses the logic of the test – if the VIP field is NOT empty, return 0.2; otherwise return a null result.

Example 3 uses the not logic but does not explicitly state the false result so it will be null.

All three examples return the same result for any given data. Which is “best” is a matter of opinion. Some would say that #1 is easiest to read and understand – the not logic requires two steps to interpret. Others would prefer #2 over #3 for the explicit statement of the false result (even when it is null).

Did you know?

The FileMaker calculation engine also allows numeric results for the test where 0 (zero) is false and any non-zero numeric result is true.

Nested If functions

What if there were different levels of VIP customer – VIP1 and VIP2. How could you assign them different discount levels? You need to perform multiple tests with the following logic:

if the customer is VIP1 give them 10%
otherwise, if the customer is VIP2 give them 20%
otherwise, there is no discount

FileMaker Pro allows you to nest If statements so that result2 for the first If is another If statement.

If ( cust::VIP = "VIP1"; 
     0.1; 
     If ( cust::VIP = "VIP2"; 
          0.2; 
          "" ) 
    )

The parameters of each function above have been separated onto new lines to make it easier to read. However, even with one nested If, it is difficult to read and maintain. The Case function should be used instead.

Case function

The Case function simplifies the expression of progressive tests. The format is:

Case(test1;result1{;test2;result2;...;defaultResult})

The first test/result pair is required but there can be any number of following test/result pairs with an optional final default result (if all tests return false).

FileMaker Trivia

A calculation formula in FileMaker Pro allows a maximum of 30,000 characters. This places a technical limit on the number of tests in a Case statement.

The nested If statement above is more readable with a Case statement:

Case ( cust::VIP="VIP1"; 0.1; cust::VIP="VIP2"; 0.2; "")

This will also make it much easier to add new VIP levels in future.

The FileMaker calculation engine reads the expression and exits as soon as a test returns true. For efficiency, it is best to try to order tests with the most commonly expected results first.

Since most customers are not VIPs, the expression might be better written:

Case ( IsEmpty (cust:VIP); ""  ; 
       cust::VIP="VIP1";   0.1 ; 
       cust::VIP="VIP2";   0.2 ; 
       "")
What do you think?

It can be argued that there is no need to use If statements at all since any If statement can be written as a single test Case statement.

If script step

If statements are also used in scripting. The logic is the same as in calculations – perform a test, and act one way for a true result and another for false. The definition of ‘true’ is also the same.

Simple If script statement

Every If script step needs a matching End If step. The script shown above is the minimum structure required. There is no explicit action for when the test returns false. In that case, the script proceeds directly to the End If step and continues the script.

Else and Else If script steps

The Else and Else If script steps can be used within an If/End If structure. The screenshot below shows three possible structures for an If statement:

If statements in scripting

The Else script step is used in example 2 to provide a place for steps for when the test returns false. This is the standard If logic – “If the test is true then do this, else, do that”. When the If test returns false, the script skips to Else and then proceeds through the following script steps.

What do you think?

Advocates of explicit programming would argue that you should always include an Else step to be clear about what should happen when the test returns false. In this way, the developer is showing their intention for the code – even if they intend for nothing to happen.

There is no Case script step. Instead, you use Else If steps for progressive testing (as shown in example 3). At each test (If and Else If), when the test returns true the script performs the following script steps and then skips to End If. As for a Case statement in a calculation, it is most efficient to test for the most common results first.

What about this?

It is possible to write nested If statements with scripting (as below). But as with calculations, it is harder to read and harder to maintain. This was done before FileMaker introduced the Else If script step. You are well advised to avoid this type of scripting.

Nested if statements