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: |
| 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: |
| 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.
| 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.
- Example: IF(Quantity=0, "", Unit Price*Quantity)
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 | 5 |
| - | 3-2 | 1 |
| * | 3*2 | 6 |
| / | 3/2 | 1.5 |
| ^ | 3^2 | 9 |
| & | 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) | 6 |
| 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 | 5 | 350 |
| LAN cable | 350 | 2 | 700 |
| Power strip | 150 | 1 | 150 |
| A5 notebook | 50 | 10 | 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. |