Formulas
Specify a formula as part of validation options for a selected field or table column. Use basic arithmetic, comparative, logical, and functional operations.
- All function names must be in capital letters.
- All formulas must result in either a true or false validation.
- Field and column names are case sensitive when used within formulas. If field is defined as Qty in design, using qty or QTY in formula results in an invalid formula.
- For variable declaration or manipulation, ensure not to use certain keywords that are reserved for formulating an expression, including SUM, SUB, DIV, MUL, COLSUM, IF.
Mathematical Operators
| Operations | Description | Syntax |
|---|---|---|
| + | Addition | Field/Column_Name1 + Field/Column_Name2 |
| - | Subtraction | Field/Column_Name1 - Field/Column_Name2 |
| * | Multiplication | Field/Column_Name1 * Field/Column_Name2 |
| / | Division | Field/Column_Name1 / Field/Column_Name2 |
Comparative Operators
| Operations | Description | Syntax |
|---|---|---|
| == | Equal To |
[Current Field/Column Name] == [expression comprised of one or more field/column name or fixed numeric values] For example, AMOUNT == MUL(QUANTITY, UNIT_PRICE) |
| >= | Greater than or Equal To |
[Current Field/Column Name] >= [expression comprised of one or more field/column name or fixed numeric values] For example, TOTAL_BILL_AMOUNT >= AMOUNT_PAID |
| <= | Less than or Equal To |
[Current Field/Column Name] <= [expression comprised of one or more field/column name or fixed numeric values] For example, AMOUNT_PAID <= TOTAL_BILL_AMOUNT |
| > | Greater Than |
[Current Field/Column Name] > [expression comprised of one or more field/column name or fixed numeric values] For example, BILL_AMOUNT > 0 |
| < | Less Than |
[Current Field/Column Name] > [expression comprised of one or more field/column name or fixed numeric values] For example, DISCOUNT_PERCENTAGE < 100 |
| != | Not Equal To |
[Current Field/Column Name] != [expression comprised of one or more field/column name or fixed numeric values] For example, DEBIT_AMOUNT != 0 |
Logical Operators
| Operations | Description | Syntax |
|---|---|---|
| && |
And: Field/Column is valid if all conditions are true |
<Current Field/Column Name> <operator 1> <expression 1> && <Current Field/Column Name> <operator 2> <expression 2> For example, DISCOUNT_PERCENTAGE >= 0 && DISCOUNT_PERCENTAGE <=100 |
| || |
Or: Field/Column is valid if any one of the given conditions is true |
<Current Field/Column Name> <operator 1> <expression 1> || <Current Field/Column Name> <operator 2> <expression 2> For example, DISCOUNT_PERCENTAGE >= 0 || DISCOUNT_PERCENTAGE == 'Net' |
| ! |
Not: Converts a true expression to false and also the other way round |
!<expression> For example, !(AGE < 18) ==> valid when AGE is not less than 18 |
Functional Operators
| Operations | Description |
|---|