Using Conditional Functions

Conditional functions are used when you want to return a different result based on certain conditions. The most basic and essential conditional function is the If function. If takes three parameters: a test, a true result, and a false result. The test needs to be a full equation or expression that can be evaluated as true or false.

Let's look at an example. Suppose you have a set of records containing data about invoices. You'd like to display the status of the invoice ”"Paid" or "Not Paid" ”based on whether the AmountDue field has a value greater than zero. To do this, you'd define a new field, called InvoiceStatus , with the following formula:

 If ( AmountDue > 0, "Not Paid", "Paid") 

For each record in the database, the contents of the InvoiceStatus field will be derived based on the contents of that record's AmountDue field.

The test can be a simple equation, as in the preceding example, or it can be a complex test that uses several equations tied together with "and" and "or" logic.

 If ( A and B;"something"; "something else") - both A and B have to be true to return the  true result If ( A or B; "something"; "something else") - if either A or B is true, it will return the  true result 

The true or false result arguments can themselves be If statements, resulting in what's known as a nested If statement. This allows you to test multiple conditions and return more than two results. For instance, let's revise the logic of the InvoiceStatus field. Say that we wanted invoices with a negative AmountDue to evaluate as "Credit Due" . We could then use the following field definition:

 If ( Amount Due > 0; "Not Paid"; If (Amount Due < 0; "Credit Due"; "Paid")) 

The other commonly used conditional function is the Case statement. The Case statement differs from the If statement in that you can test for multiple conditions without resorting to nesting. For instance, say that you have a field called GenderCode in a table that contains either "M" or "F" for a given record. If you wanted to define a field that would display the full gender, you could use the following formula:

 Case(GenderCode = "M"; "Male"; GenderCode="F"; "Female") 

A Case statement consists of a series of tests and results. The tests are conducted in the order they appear; if a test is true, then the following result is returned; if not, then the next test is evaluated. FileMaker stops evaluating tests after the first true one is discovered . You can include a final optional result that is returned if none of the tests come back as true. The gender display formula could be altered to include a default response as follows :

 Case(GenderCode = "M"; "Male"; GenderCode="F"; "Female"; "Gender Unknown") 

Without the default response, if none of the tests are true then the Case statement returns a null value.