Skip to content

Basic Concepts

Arithmetic Functions

You can perform simple arithmetic, such as addition, subtraction, multiplication and division:

1 + 2 = 3  
3 - 2 = 1  
4 * 3 = 12  
12 / 3 = 4

Use of Brackets

You can use brackets to specify the order in which different operations are performed:

1 + (2 * 3) = 7  
(1 + 2) * 3 = 9

Brackets can be nested to any depth:

((1 + 3) / (1 + 1)) = 2

Positive and Negative Numbers

Numbers can positive or negative:

-1 + (2 * 3) = 5  
1 + (-2 * 3) = -5  
1 + (-2 * -3) = 7

Decimals

You can use whole numbers or decimals:

1.2 + 1.2 = 2.4

Rounding

The following rounding functions are provided for convenience:

Round to nearest whole number

ROUND(2.333333, 0) = 2

Round to fixed decimal places 

ROUND(2.333333, 2) = 2.33

Round down to whole number 

FLOOR(2.333333) = 2

Round up to whole number

CEILING(2.333333) = 3

Whitespace

Whitespace is ignored (Except in strings, see below):

1 + 3 = 4  
1+3 = 4

Referencing Existing Field Values

Existing fields can be referenced using square brackets:

["Net Amount"] + ["Tax Amount"]  
["Gross Amount"] + 100

Literal Strings

String values can be specified using double quotes and they can be joined using an ampersand:

"QUO-" & ["ID"] = "QUO-1234"

Whitespace contained in a string is an exception to the rule that whitespace is ignored:

"QUO: " & ["ID"] = "QUO: 1234"

The following characters are all supported:

Lower Case: a - z
Upper Case: A - Z
Digits: 0 - 9
Spaces:
Other Printable Characters: \!#$%&'()*+,-./:;<=>?@[ ]^_`{|}~

Note: Double quotes inside literal strings are not currently supported, but they can be contained in a field value and should act as expected in that case.

String Padding

You can pad a string with leading or trailing characters. This is often used for padding numbers with leading zeroes, but you can pad any string with any characters. The syntax is:

  • LPAD(string, length, padding)
  • RPAD(string, length, padding)

Examples:

LPAD("Z", 5, "A") => "AAAAZ"  
LPAD("YZ", 5, "A") => "AAAYZ"  
LPAD("XYZ", 5, "A") => "AAXYZ"  
RPAD("A", 5, "Z") => "AZZZZ"  
RPAD("AB", 5, "Z") => "ABZZZ"  
RPAD("ABC", 5, "Z") => "ABCZZ"

Practical Example:

"QUO-" & LPAD(STRING(["ID"]), 6, "0")' => "QUO-000001"