Using expressions

ACL expressions are combinations of values and operators that perform a calculation and return a result. Expressions are a valuable and flexible tool. You can use them to perform a wide range of calculations, to create filters, to prepare data for analysis, and, as the basis for computed fields, to create values that do not exist directly in the data.

Expressions can include data fields, functions, literals, constants, and variables, which can be combined using arithmetic or logical operators. You can enter expressions manually, or you can build them using the Expression Builder, which provides a standard utility throughout ACL for creating expressions.

Expressions can be as simple or as complex as needed. A simple expression could return the result of a basic arithmetic operation – for example, Quantity * Cost. A more complex expression could reference a number of fields and functions and use operators to combine the parts of the expression. For example:

PROPER(first_name) + " " + PROPER(last_name)

converts all the names in the first_name and last_name fields to proper case (initial capital letter followed by lowercase), and joins the first and last names with a single space between them.

Types of expressions

There are four types of expressions in ACL: character, numeric, datetime, and logical. For example, Amount + 1 is a numeric expression because it performs an operation on numbers and returns a numeric result, whereas Amount > 1 is a logical expression because it makes a comparison and returns a logical result of True or False.

When you create an expression, ACL must be able to evaluate it as one of the following data types:

How ACL evaluates expressions

ACL evaluates expressions from left to right, according to the following rules:


You can use the SET MATH command to change the number of decimal places that result from a mathematical operation.

Section contents

(C) 2013 ACL Services Ltd. All Rights Reserved. | Send feedback