Expression Editor

It is possible to use dynamic field expressions in place of field name specifications in a form set. You can use these expressions to perform conditional calculations or create conditional labels and filters.

To open the Expression Editor, right click and select Edit Expression from the right-click menu.

Click the Check button to check the syntax of the expression you have entered.

For examples of the expressions you can write in the Expression Editor and elsewhere, refer to: Getting Started: Dynamic Field Expressions.

Variables, constants, arithmetic operators, comparison and logical operators can be included in an expression. The operators used in an expression are evaluated in the following order of precedence:

Constants

Literal constants can be numeric, character, or Boolean. A number of special constants are also provided:

Variables

A [#record] expression variable provides direct access to the record number of the current record. To use every 25th record in a calculation or for labelling:

=if([#record] % 25) = 0 then
    "Label this" 
else 
    "" 
endif

One potential trap for the unwary is that record IDs start at 1, which means the first record will not be chosen. If this is a problem, just subtract 1 from the record ID:

=if(([#record]-1) % 25) = 0 then
    "Label this" 
else 
    "" 
endif

Type conversions

Type Numeric String Boolean
Numeric - String containing the numeric value, i.e. “5" for 5 -
String If possible, numeric value from the string. Otherwise NIL. - -
Boolean 1, if TRUE. 0, if FALSE. “1" if TRUE. “0" if FALSE. -

Boolean conversion rules

When writing Boolean {TRUE} or {FALSE} expression results to a file, {TRUE} will be implicitly converted to the number 1 or the character "1", and {FALSE} will be implicitly converted to the number 0 or the character "0", depending on the destination field type.

There is no corresponding implicit conversion from numerical or character values to Boolean when reading from a file, so you must explicitly write the conversion yourself.

Depending on the input data, the expression should look something like this for character fields with text labels:

if [FLAG]="True" then
    handle the good stuff
elseif [FLAG]="False" then
    handle the bad stuff
else
    handle the unknown stuff
endif

or, for character fields with numerical labels:

if [FLAG]="1" then
    handle the good stuff
elseif [FLAG]="0" then
    ...

or, for numerical fields:

if [FLAG]=1 then
    handle the good stuff
elseif [FLAG]=0 then
    ...

Type conversion operators

The result type of an expression is determined by the type of the first operand. However, this is not always convenient since something like:

=[RL] + "m"

will give an unexpected result.

A unary operator “#" that can be used to convert an operand, clause, or expression to a string is handy in these situations. So:

=#[RL] + "m"

gives the correct result.

A few examples are shown in the following table:

=3 + 4 produces the number 7, whereas:
=#(3 + 4) produces the text “7", and:
=#3 + 4 produces the text “34", which is simply the concatenation of 3 and 4.

Note: Unless otherwise stated, the type of the right expression will be converted to the type of the left expression before executing the operation.

i.e. "5.05" will be the (concatenated) result of “5.0" + 5, and 10 will be the (arithmetic) result of 5.0 + “5".

Note: Literal constants take precedence when determining the context type. For example:

=2 + "3"

evaluates to 5, but if A is a variable with value 2,

=A + “3”

evaluates to 23.

Arithmetic operators

Operator Effect for numbers Effect for strings Effect for Boolean
+ Addition Concatenation Logical OR
- Subtraction Invalid operation Logical OR inverted
* Multiplication Invalid operation Logical AND
/ Division Invalid operation Logical AND inverted
% Modulo1 Invalid operation *
^ Power *1 *

1The % (modulo or modulus) operator calculates the remainder after division. For example, 12 % 10 is 2. At school you might have said it as “12 divided by 10 is 1, with remainder 2". Similarly, 20 % 10 is zero because there is no remainder.

Comparison operators

Sign Operator
> Greater than
< Less than
>= Greater or equal
< Less or equal
= Equal
!= Not equal

Note: If the types of the left and right expressions are different, the right expression will be converted to the type of left expression before making the comparison. Comparison operations produce a Boolean result.

Logical operators

Sign Operator
& Logical AND
| Logical OR
! Logical NOT

In the following example the Logical OR (pipe) operator is used in a field expression to define a filter. If a record contains either 1, 2 or 3 in its BLOCK field, then the record passes the filter:

=[BLOCK] = "1" | [BLOCK] = "2" | [BLOCK] = "3"

The same expression can be written more succinctly using the IN function:

=IN([BLOCK], "1", "2", "3")

Conditional operators

The expression parser has a conditional operator 'if'. It has the following syntax:

if <predicate> then
    <expression_true>
elseif|elif <predicate> then
    <expression_true>
else
    <expression_false>
endif|fi

The elsif|elif part of the operator can be repeated, or be skipped completely. The logic is to evaluate predicates until the one that evaluates to TRUE is found, and then evaluate the corresponding <expression_true>. If no predicate evaluates to TRUE, then <expression_false> is evaluated and this becomes the result.

You can use the 'if' operator in the following way:

MCAF + if RECOVERY > 0.5 then MCAF * 2 else 0 fi

In other words, if the RECOVERY is greater than 0.5, then add MCAF * 2 to MCAF, otherwise add 0 (nothing).

Functions

The general form of calling a function is:

FUNCTION (<param1>, <param2>, ... <paramN>)

For a list of currently defined functions, see: Functions