Calculations Explained - File Maker

Calculations are made up of a number of elements including perators, constants, field references, and functions. These elements are combined to create expressions, and expressions can be combined to create even more complex expressions.

An expression is anything that can be evaluated in a calculation. For example, the following are all expressions: 5, 6 + 3, ( 6 + 4 ) * 8, "FileMaker", "Text", and “Constant".

Expressions can be very simple or complex. Complex expressions are made up of simpler expressions. In fact, most of the above expressions are complex expressions. For instance, take the expression ( 6 + 4 ) * 8. The 6 is one expression, 4 is another, 6 + 4 is yet another, and 8 is one more. This doesn’t mean that everything in a calculation is an expression. In the same example, the space is not an expression in itself; neither is the asterisk (*).

As the name implies, operators perform an operation on one or two values. Operators are usually used in the form: expression operator expression. Most operators require two expressions to work with. However, the “not” requires only one expression, and the text constant operator encloses a string of text.

The operators can be broken into four categories: text, number, comparison, and logical. Each operator expects to have the correct kind of expression to work with. FileMaker will let you combine expressions of one type with operators of another, but the results you get may not be what you expect.

For example, the expression "File" + "Maker" doesn’t produce a result of “FileMaker” but of 0. This is because the plus sign is a number operator, and it expected to find numbers on either side of it, not text.

Number operators such as plus and minus also work with time and date fields. Adding a number to a date field adds that many days to the date, and subtracting does likewise. Adding a number to a time field adds that many seconds to the time. This makes sense when you consider how FileMaker stores dates and times. Dates are simply numbers, the number representing the number of days since January 1, 0001. Times are numbers representing the number of seconds since midnight.

Try this: Create a calculation field with a date result, and set the calculation to 1. Then look at what the field shows when placed on a layout.

Comparison and Logical Operators
The utility of text and number operators may be obvious from their name, but what are comparison and logical operators?

Comparison operators compare the values of two expressions and return a Boolean value based on whether the complex expression is true or false. A Boolean value is just a fancy word for “yes” or “no”; “true” or “false.” Boolean values in FileMaker resolve into either 1 for true or 0 for false. Keeping this in mind can be handy for simplifying calculations, as you’ll see later.

Hera below lists the available operators with a brief description of what they do. Remember, when the result is of a type Boolean (true or false), FileMaker returns a 1 for true and a 0 for false.



Operator Order
Expressions evaluate themselves based on a particular order of the operators. Take a look at the following two expressions:

1 + 2 * 3 3 – 1 ^ 4

You might expect FileMaker to evaluate the first expression to be equal to 9 (1 + 2 = 3 and 3 * 3 = 9) and the second to 16 (3 – 1 = 2 and 2 ^ 4 = 16). But FileMaker doesn’t always evaluate expressions from left to right. Some operators take precedence over others.

In the above two cases, the multiplication operator and the power operator evaluate first, and then the plus and minus operator evaluate. So the first expression evaluates to 7 and the second evaluates to 2. The order of precedence is to perform multiplication and division from left to right first, and then to perform addition and subtraction from left to right.

But don’t rely on the built-in operator precedence when building your calculations. Instead use another operator, the parentheses. By enclosing expressions in parentheses, you can override the order of precedence. Take a look at the following expressions:

(1 + 2) * 3 (3 – 1) ^ 4

The first expression evaluates to 9 and the second one to 16. This is because you have enclosed the addition operator in the first expression and the subtraction operator in the second expression in parentheses. This tells FileMaker to perform the addition and subtraction before performing the multiplication and power operations.

Never rely on FileMaker’s built-in precedence order when building your calculations. For instance, in the preceding example, 1 + 2 * 3, even if you want the multiplication to be performed before the addition, enter this calculation as 1 + (2 * 3). The reasoning behind this is that by making the precedence explicit with parentheses, there will be no misunderstanding when you or someone else comes back to the calculation later.

Parentheses are also important when building calculations based on Boolean values. Boolean values are expressed right to left, so take a look at the following expression:

(6 = 6) or (7 > 4) and (3 = 9)

Work through this one expression at a time. Remember, logical operators work from left to right. First of all, (6 = 6) returns 1 (true). (7 > 4) also returns 1. So the or operator returns 1 because both of the expressions on either side of it are true. So the expression becomes:

1 and (3 = 9)

(3 = 9) is false, so the and operator returns false, because it needs both of its expressions to be true for it to return true. The entire expression therefore results in 0 (false).

But what about this expression? It uses parentheses to alter the order of precedence:

(6 = 6) or ((7 > 4) and (3 = 9))

In this case you want to perform the and operator first, which resolves into 0, because one of the expressions is false. The expression therefore becomes:

(6 = 6) or 0

This resolves to 1 (true) because only one expression needs to be true for the or operator to return true. Which result do you want? That depends on your purposes and on what the numbers represent. The rule of thumb is, always use parentheses to specify the order in which operators should be performed.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

File Maker Topics