Operators and Functions in Automatic Calculation

This section explains operators and functions available for formulas for automatic calculation, and provides example formulas.

Operations and Functions Available for Numeric Calculation

Operator/Function Description Data type allowed as input Data type of output
+ Adds numbers. Number, Date and time For calculated fields: Number or Date and time.
For text fields: Text.
- Subtracts numbers.
The operator can also be used as unary operator for converting field values to plus or minus.		 Number, Date and time For calculated fields: Number or Date and time.
For text fields: Text.
* Multiplies numbers. For calculated fields: Number.
For text fields: Text.		  
/ Divides numbers. Number For calculated fields: Number.
For text fields: Text.
^ 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.		 Number For calculated fields: Number.
For text fields: Text.
& Concatenates strings. Number, Text Text
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.

The operator can function as '+' operator as well as sum values of fields in a table.		 Number Number
YEN

Displays the calculated result in "Yen" (￥). The calculated value is displayed with thousands separator. And the decimal part of the result value is rounded according to the specified number of decimal places.

The YEN function can only be used for "Text" fields.
To display the value in the "Number" field in yen, set the currency sign. For details on the number field settings, see the following page:
Number

The YEN function is used in the following format:
YEN (Value, Number of decimal places for rounding)
For Value, specify the code of a "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.

 Number Text
DATE_FORMAT Change the date and time format and the time zone.
Changing the Date and Time Format and Time Zone (DATE_FORMAT Function)
The DATE_FORMAT function can be used only in "Text" fields.		 Date and time, Number Text

For the data types of values that various fields provide, see the table below.

Fields That Can Be Used as Input Values of Formulas and Data Types of the Values

Fields Data Type
Text Text
Number Number
Calculated Number or Date and time.
This depends on the display format.
Date and time Date and time
Date Date and time
Time Date and time
Lookup The type of the key field
Record created datetime Date and time
Record updated datetime Date and time
Record created by Text
Record updated by Text

Example Formula

This section provides example formulas.

Operator/Function Expression Calculated Result
+ 3+2 5
- 3-2 1
* 3*2 6
/ 3/2 1.5
^ 3^2 9
& John&Jones JohnJones
SUM SUM(1, 2, 3) 6
YEN YEN(1100.5, 0) \1,101
DATE_FORMAT DATE_FORMAT(2018-04-06, "YYYY年M月d日", "system") 2018年4月6日

The following sections provide detailed examples. "Field X" indicates a value in the field that has the recode field of "Field X".

Numeric Calculation

Numeric calculation is performed using the "Calculated" or "Text" fields.

Sum values in fields.
Formula:
Field A + Field B
If Field A is "1" and Field B is "2", the calculated result is:
3
Subtract 1,000 from Field A, and multiply by 2.
Formula:
(Field A - 1000) * 2
If Field A is "1,500", the calculated result is:
1,000
Total the price of 5 items with the unit price of Field A and 10 items with the unit price of Field B.
Formula:
Field A * 5 + Field B * 10
If Field A is "20" and Field B is "10", the calculated result is:
200
Multiply Field A by 1.08, and round to the nearest whole number to display the result in "Yen" (￥).
Formula:
YEN(Field A * 1.08, 0)
If Field A is "9,980", the calculated result is:
¥10,778

The value of "9,980 × 1.08 = 10,778.4" is rounded to the nearest whole number.

Sum up values of fields in a table.

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: :

Formula:
SUM(Field A)
Result:
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".

Add an hour to the date and time.
Formula:
Field A + (60 * 60 * 1)
If Field A is "2014-01-01 09:00", the calculated 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.

Calculate the date and time difference.
Formula:
Field A - Field B
If Field A is "2014-01-01 09:00" and Field B is "2014-01-01 08:00", the calculated 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

"Text" fields are used to copy or concatenate strings.

Copy the value of Field A.
Formula:
Field A
If Field A is "John", the calculated result is:
John
Concatenate Field B to Field A with a space in between.
Formula:
Field A & " " & Field B
If Field A is "John" and Field B is "Jones", the calculated result is:
John Jones