Change the value of a field using functions or operators
You can change the value of the field when you open the record add screen or edit screen or edit the field. Creation is done from the "actions" tab of the app settings screen.
Configuring an Action
-
Select either a button trigger or a value change trigger and click the "Add new action" button.
-
Enter the Action Name, select the field to substitute the execution result from the "Formula" section and enter the value to be assigned (fixed value, function, calculation formula).
If you enter a fixed value, enclose it in double quotes.
The following fields cannot be assigned values.
- The number field specified for the latitude and longitude of the address field
- file, canvas, id, autonumber, creator, createdtime, modifier, modifiedtime, box, spacer, table
For the functions that can be used in the formula, see "List of available functions" at the bottom of the page.
If you specify an in-table field in the condition of the record in which the action is executed and the in-table field is targeted for execution, the range is limited to the table row that meets the condition.
If multiple formulas are set, those formulas will be executed from the top of the setting order.
Also, when targeting a field in a table, the formula is executed in order from the first row of the target table, and then the next formula is executed.
-
After completing the settings, click the "OK" button at the bottom of the screen, and then click the "Update App" button on the app settings screen.
- References:
Configure conditions for records that perform actions
If you configure a condition, you can execute the action only when the record meets the set condition. Click the filter button to set the conditions for the record for which you want to perform the action.
- References:
-
Notes when specifying the date period in the record filtering condition
Notes when specifying the dropdown field in the record filtering condition
Notes when specifying the lookup field in the record filtering condition
Notes when entering double quotation in the record filtering condition
Configure users who can perform actions
If you configure the execute users, you can execute the action only when the logged-in user matches those users. Click the user button to set the user who has the permission to perform the action.
Execute an Action
In the case of a value change trigger action, the action is executed when the record add screen or edit screen is opened or the field is edited.
- References:
In the case of a button trigger action, the action is executed by clicking the button placed on the record add screen, edit screen, or read-only list view.
Both the button trigger and the value change trigger have a "Formula" section, but if the button trigger and the value change trigger have actions on the same field, the value change trigger's result will overwrite it.
List of available functions
This topic describes operators and functions that you can use when creating formulas.
- Operators:
-
Operator Description + Adds numbers. - Subtracts numbers. * Multiplies numbers. / Divides numbers. ** Raises a number to a power. = Returns TRUE when the text or number matches, and FALSE when the text or number does not match. != Inverts the comparison result of "=". < Returns TRUE if the value on the left is smaller than the value on the right. <= Returns TRUE if the value on the left is smaller than or equal to the value on the right. > Returns TRUE if the value on the left is greater than the value on the right. >= Returns TRUE if the value on the left is greater than or equal to the value on the right. - Functions:
-
Function Description FORMAT Format a number with grouped thousands.
Input format:
FORMAT(field ID or calculation result, the number of decimal digits)
If you omit the number of decimal digits, only the integer part is formatted.
FORMAT Returns a string converted according to the specified date format.
If you specify a formula, it returns a string converted from the date generated after the calculation.
Input format:
FORMAT(field ID or calculation result, format, formula)
The arguments "format" and "formula" must be enclosed in double quotes.
format:
format Description d Days with leading zeros m Months with leading zeros Y Year in 4-digit y Year in 2-digit H 24-hour format of an hour with leading zeros h 12-hour format of an hour with leading zeros i Minutes with leading zeros s Seconds with leading zeros formula:
formula Description n day Add n days. If you specify a negative number for n, subtract it. n month Add n months. If you specify a negative number for n, subtract it. n year Add n years. If you specify a negative number for n, subtract it. n hour Add n hours. If you specify a negative number for n, subtract it. n minute Add n minutes. If you specify a negative number for n, subtract it. n second Add n seconds. If you specify a negative number for n, subtract it. first-of-year Returns the beginning of the year. first-of-month Returns the beginning of the month. Complicated calculations can be performed by separating the calculation formulas with commas.
To calculate the date of the last day of the month of the date entered in the date field whose field ID is "field_1_", do as follows.
FORMAT(field_1_, "Y-m-d", "first-of-month, 1 month, -1 day")
DIFF Returns a string converted from the number of days elapsed from the start date to the end date according to the specified date format.
Input format:
DIFF(startdate, enddate, format)
"startdate" and "enddate" are field ID or calculation result.
The arguments "format" must be enclosed in double quotes.
format:
format Description D The number of remaining days past the last month FD The number of full days M The number of remaining months past the last full year FM The number of full months Y The number of full years TODAY Returns today's date as a string in "Y-m-d" format.
Input format:
TODAY()
WEEK Returns the day of the week.
Input format:
WEEK(field ID or calculation result)
Sunday Sun Monday Mon Tuesday Tue Wednesday Wed Thursday Thu Friday Fri Saturday Sat CEIL Rounds up the value.
Input format:
CEIL(field ID or calculation result)
FLOOR Rounds down the value.
Input format:
FLOOR(field ID or calculation result)
ROUND Rounds off the value.
Input format:
ROUND(field ID or calculation result)
SUM Returns the total value of the number fields added to the table.
Input format:
SUM(field ID)
If you specify an in-table field in the condition of the record in which the action is executed and the in-table field is targeted for execution, the range is limited to the table row that meets the condition.
SUMIF Returns the total value of the number fields in the table row filtered by the specified search query.
Input format:
SUMIF(field ID, search query)
See below for how to write a search query.
- References:
If you want to return the total value of the number field whose field ID is "field_8_" in the table row where the value of the dropdown field whose field ID is "field_5_" is "OK", do as follows.
SUMIF(field_8_, "field_5_ in ('OK')")When entering a search query, enclose it in double quotation marks, and when specifying further strings, enclose it in single quotation marks.
Even if you specify a field in the table as the condition of the record to execute the action and the target field belongs to the narrowed table row, the search target is all the rows in the table.
AVG Returns the average value of the number field added to the table.
Input format:
AVG(field ID)
If you specify an in-table field in the condition of the record in which the action is executed and the in-table field is targeted for execution, the range is limited to the table row that meets the condition.
AVGIF Returns the average value of the number field in the table row filtered by the specified search query.
Input format:
AVGIF(field ID, search query)
See below for how to write a search query.
- References:
If you want to return the average value of the number field whose field ID is "field_8_" in the table row where the value of the dropdown field whose field ID is "field_5_" is "OK", do as follows.
AVGIF(field_8_, "field_5_ in ('OK')")When entering a search query, enclose it in double quotation marks, and when specifying further strings, enclose it in single quotation marks.
Even if you specify a field in the table as the condition of the record to execute the action and the target field belongs to the narrowed table row, the search target is all the rows in the table.
MAX Returns the maximum value of the field added to the table.
Input format:
MAX(field ID)
If you specify an in-table field in the condition of the record in which the action is executed and the in-table field is targeted for execution, the range is limited to the table row that meets the condition.
MAXIF Returns the maximum value of the field in the table row filtered by the specified search query.
Input format:
MAXIF(field ID, search query)
See below for how to write a search query.
- References:
If you want to return the maximum value of the number field whose field ID is "field_8_" in the table row where the value of the dropdown field whose field ID is "field_5_" is "OK", do as follows.
MAXIF(field_8_, "field_5_ in ('OK')")When entering a search query, enclose it in double quotation marks, and when specifying further strings, enclose it in single quotation marks.
Even if you specify a field in the table as the condition of the record to execute the action and the target field belongs to the narrowed table row, the search target is all the rows in the table.
MIN Returns the minimum value of the field added to the table.
Input format:
MIN(field ID)
If you specify an in-table field in the condition of the record in which the action is executed and the in-table field is targeted for execution, the range is limited to the table row that meets the condition.
MINIF Returns the minimum value of the field in the table row filtered by the specified search query.
Input format:
MINIF(field ID, search query)
See below for how to write a search query.
- References:
If you want to return the minimum value of the number field whose field ID is "field_8_" in the table row where the value of the dropdown field whose field ID is "field_5_" is "OK", do as follows.
MINIF(field_8_, "field_5_ in ('OK')")When entering a search query, enclose it in double quotation marks, and when specifying further strings, enclose it in single quotation marks.
Even if you specify a field in the table as the condition of the record to execute the action and the target field belongs to the narrowed table row, the search target is all the rows in the table.
COUNT Returns the number of rows in the table.
Input format:
COUNT(field ID of a table field)
If you specify an in-table field in the condition of the record in which the action is executed and the in-table field is targeted for execution, the range is limited to the table row that meets the condition.
COUNTIF Returns the number of rows in the table row filtered by the specified search query.
Input format:
COUNTIF(field ID of a table field, search query)
See below for how to write a search query.
- References:
If you have a table with field ID "field_3_" and you want to extract only the rows where the value of the dropdown field with field ID "field_5_" is "OK" and return the number of rows, do as follows.
COUNTIF(field_3_, "field_5_ in ('OK')")When entering a search query, enclose it in double quotation marks, and when specifying further strings, enclose it in single quotation marks.
Even if you specify a field in the table as the condition of the record to execute the action and the target field belongs to the narrowed table row, the search target is all the rows in the table.
LOOP Repeats the generation of values (fixed value, function, calculation formula) for each row of the specified table and returns the results as a multi-line string.
Input format:
LOOP(field ID of a table field, /*formula*/)
To generate a string using the values of field IDs "field_6_" and "field_7_" for each row of a table with field ID "field_3_", do as follows.
LOOP(field_3_, /*"Item: "+field_6_+"\\nQuantity: "+STR(field_7_)+"\\n"*/)
»
Item: Orange
Quantity: 10
Item: Apple
Quantity: 15
Item: Kiwi
Quantity: 5Please enclose formula within "/*" and "*/".
When breaking lines within a formula, insert "\\n" at the break point.
If you specify an in-table field in the condition of the record in which the action is executed and the in-table field is targeted for execution, the range is limited to the table row that meets the condition.
LOOPIF Repeats the generation of values (fixed value, function, calculation formula) for each row of the table filtered by the specified search query, and returns the results as a multi-line string.
Input format:
LOOPIF(field ID of a table field, /*formula*/, search query)
See below for how to write a search query.
- References:
For the row whose dropdown field value is "OK" with field ID "field_5_" that belongs to the table with field ID "field_3_", to generate a string using the values of field IDs "field_6_" and "field_7_" for each row, do as follows.
LOOPIF(field_3_, /*"Item: "+field_6_+"\\nQuantity: "+STR(field_7_)+"\\n"*/, "field_5_ in ('OK')")»
Item: Orange
Quantity: 10
Item: Apple
Quantity: 15Please enclose formula within "/*" and "*/".
When breaking lines within a formula, insert "\\n" at the break point.
When entering a search query, enclose it in double quotation marks, and when specifying further strings, enclose it in single quotation marks.
Even if you specify a field in the table as the condition of the record to execute the action and the target field belongs to the narrowed table row, the search target is all the rows in the table.
CONCAT Concatenates strings or numbers.
Input format:
CONCAT(value1, value2, value3, ...)
"value(n)" can be a field ID or a calculation result or a fixed value.
LEN Returns the length of the string.
Input format:
LEN(field ID or calculation result or fixed value)
If the checkbox field, user field, department field, and group field are targeted, the number of their selections is returned.
LEFT Returns the specified number of characters from the beginning of the string.
Input format:
LEFT(field ID or calculation result or fixed value, number of chars)
RIGHT Returns the specified number of characters from the end of the string.
Input format:
RIGHT(field ID or calculation result or fixed value, number of chars)
MID Returns the specified number of characters from the specified position in the string.
Input format:
MID(field ID or calculation result or fixed value, starting position, number of chars)
LINE Returns the string of the specified line from a multi-line string.
Input format:
LINE(field ID or calculation result or fixed value, line position)
LPAD Adds embedded characters from the beginning of the string and returns a justified string.
Input format:
LPAD(field ID or calculation result or fixed value, number of chars, embedded character)
RPAD Adds an embedded character from the end of the string and returns a justified string.
Input format:
RPAD(field ID or calculation result or fixed value, number of chars, embedded character)
REPLACE Replaces the specified character in the string with another character.
Input format:
REPLACE(field ID or calculation result or fixed value, search string, replacement string)
If you enter a fixed value in the search string or replacement string, enclose it in double quotes.
LOOKUP_DEPT Returns the department to which the specified user belongs.
Input format:
LOOKUP_DEPT(field ID or calculation result)
The fields that can be specified in the first argument are only the user field, creator field, and modifier field.
LOOKUP_DEPT_USER Returns the users who belong to the specified department.
You can also specify a search query to return only those users who meet your criteria.
Input format:
LOOKUP_DEPT_USER(field ID or calculation result, search query)
The field ID of the user app used in the search query is the same as the field name of each field.
See below for how to write a search query.
- References:
If you want to return only the users whose "authority" field value is "Manager" among the users who belong to the department entered in the department field whose field ID is "field_1_", do as follows.
LOOKUP_DEPT_USER(field_1_, "authority in ('Manager')")Only the department field can be specified as the first argument.
When entering a search query, enclose it in double quotation marks, and when specifying further strings, enclose it in single quotation marks.
LOOKUP_GROUP Returns the group to which the specified user belongs.
Input format:
LOOKUP_GROUP(field ID or calculation result)
The fields that can be specified in the first argument are only the user field, creator field, and modifier field.
LOOKUP_GROUP_USER Returns the users who belong to the specified group.
You can also specify a search query to return only those users who meet your criteria.
Input format:
LOOKUP_GROUP_USER(field ID or calculation result, search query)
The field ID of the user app used in the search query is the same as the field name of each field.
See below for how to write a search query.
- References:
If you want to return only the users whose "authority" field value is "Manager" among the users who belong to the group entered in the group field whose field ID is "field_1_", do as follows.
LOOKUP_GROUP_USER(field_1_, "authority in ('Manager')")Only the group field can be specified as the first argument.
When entering a search query, enclose it in double quotation marks, and when specifying further strings, enclose it in single quotation marks.
NUM Converts a string to a number.
Input format:
NUM(field ID or calculation result or fixed value)
If it is an empty value, "0" is returned.
STR Converts a number to a string.
Input format:
STR(field ID or calculation result or number)
IF Specifies conditions, and returns value depending on whether such conditions are met.
Input format:
IF(conditional expression, when TRUE, when FALSE)
AND Returns TRUE when all the conditions specified are met. Otherwise, returns FALSE.
Input format:
AND(conditional expression1, conditional expression2, ...)
OR Returns TRUE when any of the conditions specified is met. Otherwise, returns FALSE.
Input format:
OR(conditional expression1, conditional expression2, ...)
CONTAIN Returns TRUE if the specified substring is included in the search target string. Otherwise, returns FALSE.
Input format:
CONTAIN(field ID to be searched or calculation result or fixed value, search string)
If you search for the checkbox field, the search string must be an exact match, not a substring.
If you search for user field, department field, and group field, the search string must be the ID of each.
If you enter a fixed value in the search string, enclose it in double quotes.
If you enter a fixed value in the argument of the function, enclose it in double quotes.