List of Operators and Functions

This section describes operators and functions that you can use when creating formulas.
Available operators and functions vary depending on whether you calculate numbers or texts.

Description of Operators

You can use the following operators.

Operator Description
+ Adds numbers.
- Subtracts numbers.
This operator can also be used as an unary operator for converting field values to plus or minus.
* Multiplies numbers.
/ Divides numbers.
^ Raises a number to a power. From the minus power of 100 to the power of 100 can be calculated.
When a decimal is specified for the exponential power, the decimal number is rounded down to the integer.
Example: 3^2.5 is converted to 3^2 and the result is 9.
& Concatenates strings or numbers.
If a numeric type or string type field is referred but is empty, that field will be considered as a null string and will be concatenated.
= Returns TRUE when the text or number matches, and FALSE when the text or number does not match. Returns FALSE when comparing different types.
!= Inverts the comparison result of "=". Returns TRUE when comparing different types.
<> Same result as "!=".
< Compares numeric values and returns TRUE if the value on the left is smaller than the value on the right. Returns FALSE if the value on the left is greater than the value on the right.
<= Compares numeric values and returns TRUE if the value on the left is smaller than or equals to the value on the right. Returns FALSE if the value on the left is greater or equals to the value on the right.
> Compares numeric values and returns TRUE if the number value on the left is greater than the value on the right. Returns FALSE if the value on the left is smaller than the value on the right.
>= Compares numeric values and returns TRUE if the value on the left is greater than or equals to the value on the right. Returns FALSE if the value on the left is smaller than or equals to the value on the right.

Priory Order of Operators

Operators are applied in the following priority order:

  • + , - (Plus and minus unary operators: +1, -2, etc.)
  • ^ (Exponentiation)
  • * , / (Multiplication and division)
  • + , - (Addition and subtraction)
  • & (String operator)
  • =, !=, <>, <, >, <=, >= (comparison operators)

Description of Functions

You can enter functions using uppercase characters or lowercase characters.
You can use the following functions.

Function Description
SUM

Adds numbers. The following format is to be used:
SUM (Value1, Value2, Value3, ...)
For Value, specify the code of a "Number" field, a formula that results in a numerical value, or a number.

This function can perform the same thing as the '+' operator. It can also sum up values of fields in a table.
YEN

Displays the calculated result in "Yen" using thousands separator. And the decimal part of the result value is rounded according to the specified number of decimal places.

The YEN function is used in the following format:
YEN (Value, Number of decimal places for rounding)
For "Value", specify the field code of the Number field, a formula that results in a numerical value, or a number.
For "the number of decimal places for rounding", specify the number of decimal places to be displayed after rounding. Specifying a negative number will round the value left of the decimal point to the nearest ten, hundred, thousand and so on depending on the negative number specified. You must specify the number of decimal places for rounding.
The result value is a string type.

* In the screen to set fields, you can choose to use thousands separators for the Number field and the Calculated field.
Using Thousands Separators
DATE_FORMAT Change the date and time format and the time zone.
For details, see the "DATE_FORMAT Function: Usage Examples" page.
IF Specifies conditions, and returns value depending on whether such conditions are met.
AND Returns TRUE when all the conditions specified in the formula are met. Otherwise, returns FALSE.
You can specify up to 32 arguments.
OR Returns TRUE when any of the conditions specified in the formula is met. Otherwise, returns FALSE.
You can specify up to 32 arguments.
NOT Inverts conditions.
ROUND Rounds off the value.
ROUNDDOWN Rounds down the value.
ROUNDUP Rounds up the value.
CONTAINS Determines if the specified field matches the condition (selection), or if the table has any row data that matches the condition (a search string).