Basics of Using Formulas
Setting formulas in apps lets you do things like calculate the sum of multiple values or perform calculations based on the values and selections of other fields.
Formula basics
-
Formulas are entered on the settings screens of "Calculated" and "Text" fields.
"Calculated" and "Text" fields are the only fields with which formulas can be used.
Use a "Calculated" field if you want the calculation result to be treated as a numeric value, or use a "Text" field if you want the result to be treated as a string value. -
Use field codes to refer to fields in a formula.
A field code is a string used for identifying a field. It is used for calculation.
For details, refer to the following pages.
Overview of Field Codes
Fields That Can/Cannot Be Referenced in Formulas -
Use functions and operators.
In Kintone formulas, you can use half-width operators (e.g., "+", "-", "*", "/") and functions.
For details on the operators and functions that can be used, refer to List of Operators and Functions.
You can use multiple operators and functions together in a single formula.
How to set formulas
As an example of how to set a formula, this section shows the steps to set a formula for multiplying numeric values.
You will use "Number" fields for entering numeric values, and a "Calculated" field for displaying the calculation result.
Image of the app with configured fields:
-
Click the App Settings icon (the gear-shaped icon at the upper right of the app's "View" screen), then select the Form tab.
-
Place two "Number" fields on the app form by dragging the "Number" field displayed in the list of fields on the left side of the screen and then dropping it onto the right area of the screen.
-
Open the settings screen of each "Number" field and set the field name and field code for each field.
As for the first "Number" field, set "Unit Price" and "Unit_Price" as the field name and field code respectively, and click Save. As for the second "Number" field, set "Quantity" as both the field name and field code, and click Save.
-
Place a "Calculated" field on the app form.
-
Open the settings screen of the "Calculated" field and set "Subtotal" as the field name.
-
Set a formula. In the formula, use the field codes of the fields that you want to use in your calculation.
Since you want to multiply the unit price by the quantity, enter the following: Unit_Price*Quantity
-
Click Save on the field setting screen of the "Calculated" field.
-
Click Save Form on the upper left side of the screen.
-
If you are creating a new app, click Activate App at the upper right of the screen and click OK on the confirmation dialog.
If you are changing the settings of an existing app, click Update App at the upper right of the screen and click Update App on the confirmation dialog.
Now you have completed setting the formula.
When you enter a unit price and quantity on the screen for adding or editing a record, the calculation result will appear in the "Subtotal" field.
If an error message appears
If an error message appears when setting a formula or entering data on a record, refer to the following pages for solutions.
Calculations that can be performed on Kintone
In Kintone, you can perform the following calculations in apps.
-
Four arithmetic operations: Perform addition, multiplication, subtraction, and division
Steps to set a formula (for multiplication)
Calculating Numeric Value
-
Concatenate strings and numbers: Concatenating the strings and numbers located in different fields
& Operator: Concatenating Strings and Numbers
-
Round off, round up, and Round down: Perform round off, round up, and round down calculations on numeric values
ROUND Function: Round Off, Round Down, or Round Up Numbers
Setting the Round Off, Round Down, or Round Up
-
Conditional branch: Change the values to display based on the conditions
- Change discount rates based on the total amount
IF Function: Change the Value to Display Depending on a Condition - Display pass or fail based on the score
AND, OR, and NOT Functions: Combine Conditions - Display values (for example, "Selected") based on the selected option
CONTAINS Function: Calculation using Check Boxes and Multi-Choices
- Change discount rates based on the total amount
-
Calculations in tables: Calculate the total of numeric values in a table, or determining if the table has any row data that matches the specified text string
Calculating Values in a Table
-
Calculation of date, time, and date & time: Calculate the future date that is N days from the specified date, or working hours
Calculation of Date, Time, and Date and Time
DATE_FORMAT Function: Specifying the Display Format of Date and Time