Operators specify the type of calculation that you want to perform on the elements of a formula. There are four different types of calculation operators: mathematical, comparison, logical, and concatenation (combining text). In addition, there is a default order in which calculations occur (this follows general mathematical rules), but you can change this order of precedence by using brackets.
To perform basic mathematical operations such as addition, subtraction, multiplication, division; combine numbers; and produce numeric results, use the following operators in a formula. When constructing your formula these can be typed manually in the Formula Pane using your keyboard or inserted by using the Operator buttons.
|
Mathematical Operators |
|||
|---|---|---|---|
|
Operator |
Meaning |
Example |
Result |
|
Plus sign (+) |
Addition |
6+6 |
12 |
|
Minus sign (-) |
Subtraction |
6-2 |
4 |
|
Asterisk (*) |
Multiplication |
6*6 |
36 |
|
Forward slash (/) |
Division |
36/6 |
6 |
You can compare two values with the following operators. When two values are compared by using these operators, the result is a logical value either TRUE or FALSE.
|
Comparison Operators |
||
|---|---|---|
|
Operator |
Meaning |
Example |
|
= |
Equal to |
CreditLimit=1500 |
|
> |
Greater than |
CreditLimit>1500 |
|
< |
Less than |
CreditLimit<1500 |
|
>= |
Greater than or equal to |
CreditLimit>=1500 |
|
<= |
Less than or equal to |
CreditLimit<=1500 |
|
<> |
Not equal to |
CreditLimit<>1500 |
These operators perform basic logical operations. As with comparison operators, they are used to test for TRUE or FALSE. The different logical operators are listed in the table below.
|
Comparison Operators |
||
|---|---|---|
|
Operator |
Meaning |
Example |
|
AND |
Specifies a combination of conditions |
(OpenDate=01/06/2012)AND |
|
OR |
Specifies a list of alternative conditions |
(OpenDate=01/06/2012)OR |
|
XOR |
Makes conditions mutually exclusive |
(OpenDate=01/06/2012)XOR |
|
NOT |
Excludes a condition |
NOT(OpenDate=01/06/2012) |
You can concatenate (combine) one or more text strings to produce a single piece of text using the following operator.
|
Text Concatenation |
|||
|---|---|---|---|
|
Operator |
Meaning |
Example |
Result |
|
Plus sign (+) |
Connects two values to produce one continuous text value. This could be text plus text or text plus a database variable. |
Credit Limit £+CreditLimit |
Credit Limit £1500 |
When you construct complex formulas, the order in which calculations are performed can affect the value returned by the formula, so it's important to understand how the order is determined and how you can change the order to obtain the desired results. Designer Studio calculates the formula from left to right, according to a specific order for each operator in the formula. For example, 5+5*3 produces 20 because Designer Studio calculates multiplication and division before addition and subtraction.
However, it is possible to change the order of precedence by using brackets to enclose parts of the formula, e.g. around the part to be calculated first. Take a look at the examples below:
|
Use of Brackets |
||
|---|---|---|
|
Description |
Example |
Result |
|
This formula produces 20 because Designer Studio calculates multiplication before addition. It multiplies 5 by 3 and then adds 5 |
5+5*3 |
20 |
|
This formula produces 30 as the brackets change the order. Designer Studio adds 5 and 5 together and then multiplies the result by 3 |
(5+5)*3 |
30 |
|
This formula produces 60 as the brackets force these parts to be calculated separately. Designer Studio adds 5 and 5 first and then multiplies the result by the sum of 3 and 3 |
(5+5)*(3+3) |
60 |
|
This formula forces the embedded part to calculate and not concatenate. Designer Studio adds the text to the result of the formula inside the brackets. For the purpose of this example, <InvoiceDate> is 08/06/2012. |
The invoice due date is: + (InvoiceDate+22) |
The invoice due date is: 30/06/2012 |
This process uses the standard arithmetic order of precedence known as the BODMAS rule. This is the secret code which enables Designer Studio to know exactly the right sequence of doing things mathematically.
(B)rackets (O)rder (D)ivision (M)ultiplication (A)ddition (S)ubtraction
Using this rule, all the maths contained in brackets are calculated first, then any orders (i.e. square roots, powers, and anything else not listed next), then it's the divisions and multiplications (these rank equally and therefore go left to right), and finally it's the additions and subtractions (which also rank equally and go left to right).