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.

Operator/Function 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 "!=".
< Returns TRUE when the result value is smaller than the value shown on the left. If the result value is greater than the value shown on the left, returns FALSE.
<= Returns TRUE when the result value is smaller than or equals to the value shown on the left. Otherwise, it returns FALSE.
> Returns TRUE when the result value is greater than the value shown on the left. If the result value is smaller than the value shown on the left, returns FALSE.
>= Returns TRUE when the result value is greater than or equals to the value shown on the left. Otherwise, it returns FALSE.
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.

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)

Result Value of the Calculated Field

Result value is treated differently depending on whether it is for the Calculated field or the Text field.
The value is converted to a number when the calculated result in the Calculated field is a string.

Result value in the Calculated field
Type Show
Numeric type Displayed as a number
String type Converted to a number
* Error will be returned if the value cannot be converted (see "Errors Displayed When Entering Records").
Boolean type Show 1 if TRUE, and 0 if FALSE

Numeric type result and Boolean type result are displayed in the same way for the Calculated field and the Text field.

Empty Fields

Empty fields are considered as '0' or "null string".

  • Empty numeric type fields are considered as '0'. However, in the case of using the '&' operator, such fields are considered as "null string("")".

  • Empty string type fields are considered as "null string("")".

  • In the case of using IF function, if you enter "Calculated field = 0", the result becomes TRUE even when the calculated field is empty, not only when the calculated field value is '0'.

  • If you specify "Calculated field = """, decision will be made whether the field is empty or not. Returns FALSE when '0' is entered, and TRUE when the field is empty.

  • Described below is the way to make the field empty instead of displaying '0'.
    Specify "" as the result.

    • Example: IF(Quantity=0, "", Unit Price*Quantity)
      When the quantity is 0, the result will be empty. When the quantity is not 0, the result value of multiplication will be displayed.

Values of the Number Field and the Calculated Field

Values of the Number field and the Calculated field might be rounded, depending on the number of digits. You can change the number of digits and method of rounding.
For details, see the following pages.
Setting the Round Off, Round Down, or Round Up
Setting the Significant Digits of Numbers and the Rounding Method

Appendix: Example Uses of Operators and Functions

Operator/Function Expression Calculated Result
+ 3+2 May
- 3-2 1
* 3*2 Jun
/ 3/2 1.5
^ 3^2 Sep
& John&Jones JohnJones
= IF(A=100,B,C) If you specify 100 to A, 10 to B, and 5 to C: 10
!= IF(A!=100,B,C) If you specify 100 to A, 10 to B, and 5 to C: 5
<> IF(A<>100,B,C) If you specify 100 to A, 10 to B, and 5 to C: 5
< IF(A<100,B,C) If you specify 100 to A, 10 to B, and 5 to C: 5
<= IF(A<=100,B,C) If you specify 100 to A, 10 to B, and 5 to C: 10
> IF(A>100,B,C) If you specify 100 to A, 10 to B, and 5 to C: 5
>= IF(A>=100,B,C) If you specify 100 to A, 10 to B, and 5 to C: 10
SUM SUM(1, 2, 3) Jun
YEN YEN(1100.5, 0) \1,101
DATE_FORMAT DATE_FORMAT(1522972800, "YYYY MMM d", "system") 2018 Apr 6
IF IF(A<100,B,C) If the value of A is 90, the result is B. If the value of A is 110, the result is C.
AND IF(AND(A>10,B>=10),"1","0") If the values of A and B are 10: 0
OR IF(OR(A>10,B>=10),"1","0") If the values of A and B are 10: 1
NOT IF(NOT(A>10),"1","0") If the value of A is 10: 1
ROUND ROUND(A * 1.1) If the value of A is 8,875: 9,763
ROUNDDOWN ROUNDDOWN(A * 1.1) If the value of A is 8,875: 9,762
ROUNDUP ROUNDDOWN(A * 1.1) If the value of A is 8,875: 9,763

Numeric Calculation

Numeric calculation is performed using the "Calculated" or "Text" fields.
"Field A" is the value of the field of which field code is "Field A". "Field B" is the value of the field of which field code is "Field B".

Sum values: Field A + Field B

If Field A is 1, and Field B is 2, the result is 3.
Calculate using "( )": (Field A - 1000) * 2

If Field A is 1,500, the result is 1,000.
Calculate using * and +: Field A*5 + Field B*10

If Field A is 20, and Field B is 10, the result is 200.

Table Calculation

Assume that the following table is on the form:

Item Unit Price Quantity Sub Total
USB memory 1,500 1 1,500
Ball-point pen 70 May 350
LAN cable 350 2 700
Power strip 150 1 150
A5 notebook 50 Oct 500
      (Total the numbers)

If the field code of the "Sub Total" field is "Field A", the sub total can be obtained by the following formula: You can perform calculation even when Unit Price or Quantity is empty.

Formula: SUM(Field A)

The result is 3,200.

Time Calculation

The "Calculated" field can be used for the following calculations:

  • Date and time difference calculation
  • Addition and subtraction of date/time, and time

In formulas, enter time in seconds. For example, to specify 1 hour, type "3600" or "60*60*1". To specify 1 day, type "24*60*60*1".

Calculate by seconds: Field A + (60 * 60 * 1)

If Field A is "2014-01-01 09:00", the result is "2014-01-01 10:00".
This is the calculated result of when "Date and time (Example: 2012-08-06 2:03)" is set for the display format of the field.
Use Time for display format: Field A - Field B

If Field A is "2014-01-01 09:00" and Field B is "2014-01-01 08:00", the result is "1 hour 0 minute".
This is the calculated result of when "Time (Example: 26 hours 3 minutes)" is set for the display format of the field.

String Operation

Use the Text field to concatenate strings or display YEN function.

Display the value of other field: Field A

If Field A is "John", the result is "John".
Concatenate strings: Field A & " " & Field B

If Field A is "John" and Field B is "Jones", the result is "John Jones".
Example of YEN function: YEN(Field A * 1.1, 0)

If Field A is "8,875", the result is "¥9,763".
The value of "8,875 × 1.1 = 9762.5" is rounded to the nearest whole number.