Formula Errors
There are two types of formula errors: errors that are displayed when you are setting a formula, and errors that are displayed when you are entering record data.
Errors displayed when setting a formula
This section describes errors you may encounter when you enter or save a formula. An error message appears in the following cases:
- A full-width symbol is entered
- An invalid operator is entered
- An invalid field code is entered
- The field code of a field that cannot be referenced is specified
- A field reference is causing an endless loop
- A function does not have the required number of arguments
- A nonexistent function is specified
- The number of arguments that can be specified for a function has been exceeded
- The format of a formula is invalid and cannot be interpreted
- An invalid function is used for referencing an array type field
- An operator is used with an incompatible data type
- An argument's data type is incompatible with a function
A full-width symbol is entered
Error message
You have entered a full-width symbol "***". Use a half-width symbol "***" or check that the entered value is correct.
Cause and solution
This error message appears when you entered a full-width symbol in the formula wrongly, instead of a half-width symbol.
For example, this error is displayed in the following formula because it contains a full-width quotation mark (”).
However, note that this error message will not appear in the following cases. (In the examples below, full-width parentheses are used before and after "Buffet".)
-
Full-width symbols are used correctly within a formula
IF=(Option="Breakfast(Buffet)",1500,0) -
Full-width symbols are used, but an opening quotation mark (") is entered before the first full-width symbol, and no closing quotation mark (") has been entered yet.
IF=(Option="Breakfast(Buffet)
An invalid operator is entered
Error message
- "***" needs correction. If the "***" operator is what you want to use, enter "***" instead.
- "***" needs correction. If the *** is what you want to use, enter "***" instead.
Cause and solution
This error message appears when you enter into a formula input field a specific string that is recognized as a mistyped operator.
Check the displayed error message and correct the operator. For details on the operators that can be used in kintone, refer to the following page.
Description of Operators
An invalid field code is entered
Error message
The field code (***) in the formula does not exist.
Cause and solution
This error message may appear in the following cases.
- The formula contains a field code that does not exist.
Check the field code of the field you want the formula to reference, and make sure that the correct field code is specified in the formula. - Operators (e.g., "&", "+", "-", "*", "/") are entered with full-width characters.
These operators may have been recognized as part of a field code. Make sure to use half-width symbols for these operators. - The formula contains the field code of the "Created datetime", "Updated datetime", "Created by", or "Updated by" field that is not placed on the app form.
Place the relevant field on the app form first, and then specify its field code in the formula.
The field code of a field that cannot be referenced is specified
Error message
Specified field (***) is the field type that cannot be used in formulas. The following items cannot be used in formulas: Record number, Label, Rich text, Text area, Attachment, Link, User selection, Department selection, Group selection, Related records, Assignee, Status, and Category.
Cause and solution
This error message appears when a formula contains the field code of a field that cannot be referenced in a formula.
Check the following page and make sure to specify a field code of a field that can be referenced in a formula.
Fields That Can/Cannot Be Referenced in Formulas
Field references cause an endless loop
Error message
Field references cause an endless loop.
Cause and solution
This error message occurs when a formula is set for a field in a way that results in the field referencing itself directly or indirectly. Modify such a formula so that a field code does not reference its own field.
This error message may appear for the following reasons.
- A field is referencing itself directly:
For example, if the field code of the "Calculated" field "A" is specified in a formula entered for the "Calculated" field "A" - A field code is referencing its own field indirectly.
For example, if the formula of the "Calculated" field "A" includes the field code for the "Calculated" field "B", and the formula of the "Calculated" field "B" includes the field code for the "Calculated" field "A"
A function does not have the required number of arguments
Error message
- Function *** requires *** arguments.
- Function *** requires *** or more arguments.
- Function ROUND (or ROUNDUP/ROUNDDOWN) requires 1 or 2 arguments.
Cause and solution
The error message "Function *** requires *** arguments." appears when the required number of arguments is not specified for the following functions.
- YEN
- DATE_FORMAT
- NOT
- IF
- CONTAINS
The error message "Function *** requires *** or more arguments." appears when the required number of arguments is not specified for the following functions.
- AND
- OR
- SUM
The error message "Function ROUND (or ROUNDUP/ROUNDDOWN) requires 1 or 2 arguments." appears when the required number of arguments is not specified for the following functions.
- ROUND
- ROUNDUP
- ROUNDDOWN
The number of arguments required varies for each function. Refer to the following pages and make sure to enter your formula with the necessary number of arguments.
SUM Function: Calculate the Total of Numeric Values
YEN Function: Display Calculation Results Rounded and in Japanese Yen
DATE_FORMAT Function: Specifying the Display Format of Date and Time
IF Function: Change the Value to Display Depending on a Condition
AND, OR, and NOT Functions: Combine Conditions
ROUND, ROUNDUP, and ROUNDDOWN Functions: Round Off, Round Up, and Round Down Numeric Values
CONTAINS Function: Determine If a Field Contains an Exact Match for a Specified Character String
The entered function cannot be used in kintone
Error message
Function *** does not exist.
Cause and solution
This error message appears when you specify a function which cannot be used in kintone.
See the following page and check whether the function you are using is specified correctly or is available in kintone.
List of Operators and Functions
The number of arguments that can be specified for a function has been exceeded
Error message
Function *** accepts up to *** arguments.
Cause and solution
This error message appears when the number of arguments that can be specified for a function in a formula has been exceeded.
The number of arguments required varies for each function. Refer to the following pages and make sure to enter your formula without exceeding the number of arguments that can be specified.
SUM Function: Calculate the Total of Numeric Values
YEN Function: Display Calculation Results Rounded and in Japanese Yen
DATE_FORMAT Function: Specifying the Display Format of Date and Time
IF Function: Change the Value to Display Depending on a Condition
AND, OR, and NOT Functions: Combine Conditions
ROUND, ROUNDUP, and ROUNDDOWN Functions: Round Off, Round Up, and Round Down Numeric Values
CONTAINS Function: Determine If a Field Contains an Exact Match for a Specified Character String
The formula cannot be interpreted by kintone
Error message
Invalid formula format.
Cause and solution
This error message appears when a formula cannot be interpreted. This error message may appear for the following reasons.
- Parentheses are not paired.
- An operator is placed in an incorrect position.
- A line break is inserted in the middle of a string enclosed in double quotation marks ("").
- A line break is inserted in the middle of a numeric value.
An invalid function is used for referencing an array type field
Error message
Relevant function is not used for the value of the array type. As for values of array types and their usage, refer to the help.
Cause and solution
This error message may appear in the following cases.
- A formula is referencing a "Check box" field or a "Multi-choice" field.
"Check box" and "Multi-choice" fields are array type fields. - A formula outside a table is referencing a field within the table.
When a formula outside a table references a numeric or string type field within the table, the data type of such a field is converted into an array type.
For information on data types of values referenced in a formula, refer to the following page.
Data Types of Values Referenced in Formulas
To reference an array type field, use SUM or CONTAINS functions.
An operator is used with an incompatible data type
Error message
The "***" operator is being used with an incompatible data type.
Cause and solution
The data types that are accepted as arguments vary for each operator. An error occurs if the data types of arguments are not compatible with the operators used. Refer to the following page for valid combinations of operators and data types. Based on this information, make any necessary changes to your formula.
Data Types of Values Referenced in Formulas
The following is an example of a formula that causes the error. The formula references two "Text" fields.
There are two solutions to solve the error in this example.
- Change the arguments:
If you want to keep using the current operator, specify arguments that are compatible with it.
In the example shown above, use numeric type fields with the "+" operator. - Change the operator:
If you want to keep using the current arguments, specify an operator that is compatible with them.
In the example shown above, using the operator "&" may resolve the error. For information on the operator "&", refer to the following page.
& Operator: Concatenating Strings and Numbers
An argument's data type is incompatible with a function
Error message
An argument of the *** function is using an incompatible data type.
Cause and solution
Allowed data types for arguments depend on the function you use. An error occurs if the data type of an argument is not compatible with the function used. Refer to the following page for valid combinations of functions and data types. Based on this information, make any necessary changes to your formula.
Data Types of Values Referenced in Formulas
The following is an example of a formula that causes the error. The formula references two "Text" fields.
There are two solutions to solve the error in this example.
- Change the argument:
If you want to keep using the current function, specify an argument that is compatible with the function.
In the case of the example shown above, use a string array type field for the first argument of the CONTAINS function. Note, however, that the string array type "Check box" and "Multi-choice" fields cannot be specified as arguments if they are located in a table and referenced by a formula outside of the table. - Change the function:
If you want to keep the current argument, specify a function that is compatible with it.
In the example shown above, you may be able to resolve the error by using the IF function. For information on the IF function, refer to the following page.
IF Function: Change the Value to Display Depending on a Condition
Errors displayed when entering record data
This section explains the errors displayed in fields with formulas when you are entering record data. The following errors may be displayed in a field.
#CONVERT!
This error message appears when the calculation result of a "Calculated" field is a string type value, which cannot be processed by the field.
The resulting value in a "Calculated" field must be a number, date and time, date, time, or hour.
To display the calculation result, use a "Text" field for the formula.
Select Calculate automatically for the "Text" field, and then enter the formula.
Now, the calculation result "Pass" is successfully displayed.
#PRECISION!
This error message appears when a calculation result exceeds the allowed number of digits.
On the "Misc Settings" screen, check the value for Total Number of Digits under Precision of numbers and calculations. For details on how to set the value, refer to the following page.
Setting the Significant Digits of Numbers and the Rounding Method
#VALUE!
This error message appears when the data type of a value to be calculated is invalid or the use of an operator is incorrect. This error message may appear for the following reasons.
- A string was added to a numeric value
For example, a number entered in a drop-down is treated as a string. It cannot be calculated as a number. - The "+" operator was used to concatenate strings
Refer to the following page for valid combinations of operators and data types.
Data Types of Values Referenced in Formulas
#ERROR!
Functions other than DATE_FORMAT
This error message appears when a formula cannot perform a calculation. This error message may appear for the following reasons.
- A number is divided by zero
- The exponent of a number is greater than 100
- The exponent of a number is less than -100
DATE_FORMAT function
This error message may appear in the following cases.
- A field value or calculation result is either of the following date and time combinations in UTC (Coordinated Universal Time):
- A date and time before January 1, 1000 00:00 (UNIX time: -30610224000)
- A date and time after December 31, 9999 23:59 (UNIX time: 253402300799)
- When the number of characters in the formula exceeds 64 characters.
- When invalid time zone is specified as the third argument.
Example: DATE_FORMAT (Date_and_time, "YYYY/MM/DD a hh:mm", "Tokyo")