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.

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:

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.