List of Operators and Functions

Article Number:040499

This article describes operators and functions you can use when creating formulas.
The available operators and functions vary depending on whether you are performing a calculation on numeric values or string values.

Descriptions of operators

The following operators can be used.

Operator Description
+ This operator performs addition on numeric values.
- This operator performs subtraction on numeric values.
It can also be used as an unary operator to convert the sign of a field value from positive to negative and vice versa.
* This operator performs multiplication on numeric values.
/ This operator performs division on numeric values.
^ This operator performs exponentiation on numeric values. It can raise a numeric value to any power between -100 and 100.
If a decimal is set as the exponent, the decimal is rounded down to an integer when the calculation is performed.
Example: 3^2.5 is converted into 3^2, and the calculation result is 9.
& This operator concatenates numeric and/or string values.
If a numeric type or string type field that is referenced by the formula is empty, the value of the field is treated as an empty string when the concatenation is performed.
= This operator returns TRUE if numeric values or string values are equal, and FALSE otherwise. If values of different types are compared, this operator returns FALSE.
!= This operator returns the comparison result opposite to the result that would be returned by the '=' operator. If values of different types are compared, this operator returns TRUE.
<> This operator returns the same result as the "!=" operator.
< This operator returns TRUE if the value on the left is smaller than the value on the right, and FALSE otherwise.
<= This operator returns TRUE if the value on the left is smaller than or equal to the value on the right, and FALSE otherwise.
> This operator returns TRUE if the value on the left is greater than the value on the right, and FALSE otherwise.
>= This operator returns TRUE if the value on the left is greater than or equal to the value on the right, and FALSE otherwise.

Operator precedence

The following lists operators in order of precedence, with the highest precedence operator at the top.

  • + , - (Unary positive and negative operators [E.g., +1, -2])
  • ^ (Exponentiation operator)
  • * , / (Multiplication and division operators)
  • + , - (Addition and subtraction operators)
  • & (String operator)
  • =, !=, <>, <, >, <=, >= (Comparison operators)

Descriptions of functions

Both uppercase and lowercase characters can be used for entering functions.
The following functions can be used.

Function Description
SUM

This function sums Number field values, numeric values, or formulas that produce numeric values.
YEN

This function rounds off calculation results to a specified decimal place and displays them with thousands separator commas and the Japanese yen currency symbol (¥).
DATE_FORMAT This function changes the date and time format and the time zone.
IF This function returns different values depending on whether a specified condition is met.
AND This function returns TRUE if all of the conditions specified in the formula are met, and FALSE otherwise.
Up to 32 arguments can be specified.
OR This function returns TRUE if any of the conditions specified in the formula is met, and FALSE otherwise.
Up to 32 arguments can be specified.
NOT This function reverses a specified condition.
ROUND This function rounds off a numeric value.
ROUNDDOWN This function rounds down a numeric value.
ROUNDUP This function rounds up a numeric value.
CONTAINS This function evaluates whether a specified field meets a given condition (whether a specific option is selected for the field) or whether a specified table contains any row data that meets a given condition (whether the table contains a specific search string).