Published at: 2026-09-17

Formula

Overview

The Formula field uses preset formulas to automatically generate data results. It covers all scenarios including front-end creation, edit and save, API writes, and bulk data imports. Typical applications include calculating the sale price based on product quotes and discounts, or generating the base currency amount by multiplying the original currency amount by the exchange rate.

[!NOTE] Formula field results cannot be modified manually. If you need to allow manual editing after auto-generation, use a default value formula.

Specifications

Property	Description
Use cases	Order profit = Amount - Cost, exchange rate conversion, date calculation, and so on
Return value types	Number, Amount, Percent, Date, DateTime, Time, Text, Boolean
Insertable fields	Single-select, Single-line text, Multi-line text, Time, URL, Boolean, Phone, Number, Percent, Email, Personnel, Formula, Roll-Up Summary Field
Related object support	Supports formula fields and roll-up summary fields from lookup-related objects; roll-up summary fields from lookup-related objects must aggregate standard fields
Data import	Formula calculations apply to imported data
Function support	Functions are supported when configuring formulas for formula fields and default values on standard objects

Basic settings

When you create a formula field in Field Management, you must complete the following basic configurations:

Setting	Description
Field name	Customize as needed; must not duplicate existing field names on the same object
API Name	Auto-generated by the system; you can also click the AI button to generate a translated API Name
Description	Field description, displayed only in Setup, not on the front end
Help text	Supports hover tooltip or inline display (create/edit page only)
Thousands separator	When enabled, `11111.00` displays as `11,111.00` on the front end (print output does not include the thousands separator)
Masked display	When enabled, you can add a role whitelist. Roles not on the whitelist see field values as `**` on the list page, detail page, and create/edit page

Formula configuration

Click Calculation settings to configure the return value type, formula, and null value handling.

Return value type

The return value type affects how null values behave in calculations:

DateTime, Date, Time, Text, Boolean: When other formula fields, roll-up summary fields, or validation rules reference this field, the “When field is null” setting does not apply. Null values participate directly in calculations. Null values in mathematical operations or logical comparisons cause errors, resulting in a null output. Use null-check functions in these cases.
Number, Amount, Percent: The system determines whether to convert null values to 0 based on the “When field is null” setting.

Decimal places

After you set the decimal places, the calculation result is automatically rounded to the specified number of decimal places before being written to the database. For example, if the decimal places is set to 2 and the calculation result is 6.167, the database stores and the front end displays 6.17.

Basic mode and advanced mode

Basic mode: You can insert only fields from the current object and global variables.
Advanced mode: You can reference fields from lookup-related objects and master objects (lookup relationship multi-select fields are not supported). The selectable field scope for related objects is the same as for the current object.

[!WARNING] - Roll-up summary fields from related objects and formula fields from related objects referenced in a formula are both calculated asynchronously. Results may be delayed. - When a formula references global variables (current date, current time, current date-time), Personnel object fields, or uses NOW() or TODAY() functions, the formula field does not support filtering, cannot be referenced by other roll-up summary fields or formula fields, and cannot be referenced by validation rules.

Formula configuration notes

Date subtraction: When you configure “Date 1 - Date 2,” the result is in hours. To convert to days, you must perform the conversion in the formula.
& symbol: The & symbol is converted to + in formulas. We recommend that you do not use it directly. To display & in text, create a text-type global variable with the value & and reference that global variable in the formula.
CASE function: The return type of each branch must be identical. For example, CASE($field$, "Tier 1 City", 60.5, "Tier 2 City", 49.0, 0) produces an error because the final 0 has no decimal place. Change it to 0.0.

Null value handling

This setting is available only for fields that return numeric types. For non-numeric return types, null values always participate in calculations as null.

Option	Behavior
Default to zero	When the return type is Number, Amount, or Percent, null field values in the formula are treated as 0; other types still participate as null
Default to null	When the return type is Number, Amount, or Percent, null field values in the formula remain null

Historical data calculation on save

When you click Save on the field edit page and the data volume exceeds 10,000 records, the system displays a prompt:

Option	Description
Do not calculate existing data	Applies only to records created or edited after saving; does not recalculate historical data
Calculate all	Recalculates all existing data for this object (for large data volumes, formula field values may be empty for a period)
Calculate data matching the following scope	Configure conditions as needed; only data that meets the conditions is calculated

When the data volume does not exceed 10,000 records, no prompt appears and the system saves normally.

View calculation task progress

After you create or edit a formula field, the list page displays a Calculation task status button. Click it to view the task status, total data volume, completed data volume, and estimated completion time.

Object data trial calculation

If the trial calculation produces an error and the formula field has no value, the likely causes are:

A field value in the formula is null, but neither “Default to zero” nor a null check is configured.
Type conversion errors — text-to-number or text-to-date conversions typically fail due to incorrect formats.
If the default value or validation rule trial calculation result differs from the submitted result, check whether fields in the formula have changed. In particular, check whether cascaded fields referenced by formula fields and roll-up summary fields have changed, because changes to formula and roll-up summary fields are not recorded in modification history.

AI formula assistant

The Formula field supports AI-generated formulas:

When creating a formula field, go to Calculation settings.

Enter “/” or click AI formula assistant to open the assistant.

Enter a formula description. The AI assistant generates the formula automatically.

Click Copy or Apply to use the AI-generated formula.

Function reference

Logical functions

Function	Description	Example
`IF(logical_test, val_true, val_false)`	Parameters: 3; the first is Boolean, the second and third must be the same type. Returns the second parameter if the condition is true; otherwise returns the third. Supports nesting. Note: IF evaluates all branches during calculation, not just the matched branch. Any error in any branch (division by zero, null/text in arithmetic, type mismatch) causes the formula to fail	`34 = IF(true, 34, 52)`
`CASE(expr, val1, res1,..., else)`	Parameters: variable; `result(n)` and `else_result` must be the same type. Compares expression with `value(n)` and returns the corresponding `result(n)`. Returns `else_result` if no match. Supports up to 50 value checks; exceeding this limit causes a save error	`33 = CASE(3, 2, 2.0, 3, 33.0, 1.3)`
`NULLVALUE(expr, substitute)`	Parameters: 2; checks whether the first parameter is null. If null, returns the second parameter; otherwise returns the original value. The return type matches the second parameter	`1 = NULLVALUE(Null, 1)`

Numeric functions

Function	Description	Example
`+`, `-`, `*`, `/`	Parameters: 2; types: Number, Percent, Amount. Performs addition, subtraction, multiplication, and division	`6 = (3+2)*6/5`
`-` (date subtraction)	Parameters: 2; both dates; result in days	`a = 1982-04-13, b = 1982-03-20 → 24 = a - b`
`-` (datetime subtraction)	Parameters: 2; both datetime; result in hours	`a = 1982-04-13 17:00, b = 1982-04-12 15:00 → 26 = a - b`
`-` (time subtraction)	Parameters: 2; both time; result in hours	`a = 17:00, b = 15:00 → 2 = a - b`
`VALUE(string)`	Parameters: 1; text type. Converts a text-formatted number to a number. Supports negatives and decimals. Returns null if conversion fails	`-1982.04 = VALUE('-1982.04')`, `Null = VALUE('aaa')`
`MIN(n1, n2)`	Parameters: 2; both numeric. Returns the smaller of the two	`4 = MIN(4, 13)`
`MAX(n1, n2)`	Parameters: 2; both numeric. Returns the larger of the two	`13 = MAX(4, 13)`
`MIN(n1, n2,...)`	Parameters: variable; numeric. Returns the minimum of all parameters. Returns null if any parameter is null	`80 = MIN(100, 80, 120)`
`MAX(n1, n2,...)`	Parameters: variable; numeric. Returns the maximum of all parameters. Returns null if any parameter is null	`120 = MAX(100, 80, 120)`
`MULTIPLE(n1, n2)`	Multiplication	`52 = MULTIPLE(4, 13)`
`MOD(n1, n2)`	Modulo	`3 = MOD(13, 4)`
`ADDS(n1, n2)`	Addition	`17 = ADDS(13, 4)`
`SUBTRACTS(n1, n2)`	Subtraction	`9 = SUBTRACTS(13, 4)`
`ROUND(number, digits)`	Round	`1.5 = ROUND(1.45, 1)`
`ROUNDDOWN(number, digits)`	Parameters: 2; numeric. Rounds number down to the specified decimal places. Negative digits round toward zero	`1.4 = ROUNDDOWN(1.47, 1)`
`ABS(number)`	Absolute value	`1.5 = ABS(-1.5)`
`EXP(number)`	e raised to the power of n	`2.718... = EXP(1)`
`SQRT(number)`	Square root	`4 = SQRT(16)`
`LN(number)`	Natural logarithm	`3.219 = LN(25)`
`LOG(number)`	Base-10 logarithm	`2 = LOG(100)`
`SIN(number)`	Sine (radians)	`0.5 = SIN(PI()/6)`
`COS(number)`	Cosine (radians)	`0.5 = COS(PI()/3)`
`TAN(number)`	Tangent (radians)	`1 = TAN(PI()/4)`
`ASIN(number)`	Arcsine (-1 to 1)	`π/2 = ASIN(1)`
`ACOS(number)`	Arccosine (-1 to 1)	`π/3 = ACOS(0.5)`
`ATAN(number)`	Arctangent	`π/4 = ATAN(1)`
`ATAN2(y, x)`	Arctangent of y/x	`π/4 = ATAN2(1, 1)`
`PI()`	Returns π	`3.14159... = PI()`
`LEN(text)`	String length	`6 = LEN('xiaoke')`
`FIND(search, text [, start])`	Parameters: variable; text, numeric. Returns the starting position of the search string within the text. Note: ① The optional `start` parameter specifies the starting position (1-based); returns 0 if not found; ② If `start` is null or less than 1, the search starts from the first character; if `start` exceeds the text length, returns 0; ③ Returns 0 if search or text is null; ④ Decimal `start` values are truncated to integer	`1 = FIND("dog", "dog days and dog nights")`

Percent operators

Function	Description	Example
`+`, `-`, `*`, `/`	Parameters: 2; types: Number, Percent, Amount. Performs arithmetic operations and returns a percent-formatted result	—

Date extraction functions

Function	Description	Example
`YEAR(date)`	Parameters: 1; Date or DateTime. Returns the year	`1982 = YEAR(date)`
`MONTH(date)`	Parameters: 1; Date or DateTime. Returns the month	`4 = MONTH(date)`
`DAY(date)`	Parameters: 1; Date or DateTime. Returns the day	`13 = DAY(date)`
`HOUR(datetime)`	Parameters: 1; DateTime or Time. Returns the hour	`14 = HOUR(DATETIMEVALUE("2025-07-31 14:30:45"))`
`MINUTE(datetime)`	Parameters: 1; DateTime or Time. Returns the minute	`30 = MINUTE(DATETIMEVALUE("2025-07-31 14:30:45"))`
`SECOND(datetime)`	Parameters: 1; DateTime or Time. Returns the second	`45 = SECOND(DATETIMEVALUE("2025-07-31 14:30:45"))`
`MILLISECOND(datetime)`	Parameters: 1; DateTime or Time. Returns the millisecond	`0 = MILLISECOND(DATETIMEVALUE("2025-07-31 14:30:45"))`
`UNIXTIMESTAMP(datetime)`	Parameters: 1; DateTime, Date, or Time. Returns milliseconds since 1970-01-01, or the time’s millisecond offset within a day	`1753943445000 = UNIXTIMESTAMP(DATETIMEVALUE("2025-07-31 14:30:45"))`
`DAYOFYEAR(date)`	Parameters: 1; Date. Returns the day of the year (1-366)	`1 = DAYOFYEAR(DATE(2025,1,1))`
`WEEKDAY(date)`	Parameters: 1; Date. Returns the day of the week (1=Monday, 7=Sunday)	`1 = WEEKDAY(DATE(2025,7,28))`
`ISOYEAR(date)`	Parameters: 1; Date. Returns the ISO 8601 week-numbering year (week starts on Monday)	`2025 = ISOYEAR(DATE(2025,7,31))`
`ISOWEEK(date)`	Parameters: 1; Date. Returns the ISO 8601 week number (1-53); first week starts on Monday	`21 = ISOWEEK(DATE(2024,5,20))`

[!NOTE] UNIXTIMESTAMP, TIMENOW, FROMUNIXTIME, and other timezone-aware functions: tenants with multi-timezone enabled use the tenant timezone; otherwise, the default timezone (typically UTC+8) is used.

Date and datetime functions

Function	Description	Example
`DATE(year, month, day)`	Creates a date from numeric values	`1982-04-13 = DATE(1982, 4, 13)`
`DATEVALUE(string)`	Creates a date from a string	`1982-04-13 = DATEVALUE('1982-04-13')`
`DATETIMEVALUE(string)`	Creates a datetime from a string	`2001-08-24 15:45:25 = DATETIMEVALUE('2001-08-24 15:45:25')`
`DATETIMETODATE(datetime)`	Extracts the date portion from a datetime	`1982-04-13 = DATETIMETODATE(...)`
`DATETIMETOTIME(datetime)`	Extracts the time portion from a datetime	`17:02:40 = DATETIMETOTIME(...)`
`FROMUNIXTIME(ms)`	Converts milliseconds to datetime	`2025-07-31 14:30:45 = FROMUNIXTIME(1753943445000)`
`TODAY()`	Returns the current date	—
`NOW()`	Returns the current datetime	—
`TIMENOW()`	Returns the current time	`14:30:45 = TIMENOW()`
`YEARS(n)`	Sets a year offset	`TODAY() + YEARS(1)`
`MONTHS(n)`	Sets a month offset	`TODAY() + MONTHS(1)`
`DAYS(n)`	Sets a day offset	`TODAY() + DAYS(1)`
`HOURS(n)`	Sets an hour offset	`datetime + HOURS(4)`
`MINUTES(n)`	Sets a minute offset	`datetime + MINUTES(30)`

Date arithmetic rules:

Date ± offset: date + DAYS(4) returns the date 4 days later.
DateTime ± offset: datetime - DAYS(1) returns the datetime of the previous day.
Time ± offset: time - HOURS(1) returns the time one hour earlier (result stays within 00:00–23:59).
Date subtraction: The result of subtracting two dates is in hours. DateTime subtraction and time subtraction also produce results in hours.

Text functions

Function	Description	Example
`&` or `+`	Parameters: 2; both text. Concatenates strings. `&` is converted to `+` in formulas; we recommend that you do not use it directly	`"John" + "Doe"` → `JohnDoe`
`''`	Parameters: 1; string. Inserts a single-line text constant	`'Single-line text'`
`''''''`	Parameters: 1; string. Inserts a multi-line text constant	`'''Multi-line text'''`
`TEXT(value)`	Parameters: 1; variable type. Converts a value to text using the standard display format. Returns an empty string when `value` is null. Percent fields return the value divided by 100. Date, DateTime, and Time fields return formatted text	`2025-08-01 = TEXT(DATE(2025,8,1))`
`LEFT(text, n)`	Parameters: 2; text, numeric. Returns the specified number of characters from the left of the string. Returns empty string if `text` is null or `n` is null or less than 1	`dog days = LEFT("dog days and dog nights", 8)`
`RIGHT(text, n)`	Parameters: 2; text, numeric. Returns the specified number of characters from the right. Returns empty string if `text` is null or `n` is null or less than 1	`dog nights = RIGHT("dog days and dog nights", 10)`
`MID(text, start, n)`	Parameters: 3; text, numeric. Returns characters from the middle starting at the given position with the given length. `start` is 1-based. Returns empty string if `text` is null, `start` is null or exceeds text length, or `n` is null or less than 1	`and = MID("dog days and dog nights", 10, 3)`
`UPPER(text)`	Parameters: 1; text. Converts all letters to uppercase	`DOG = UPPER("doG")`
`LOWER(text)`	Parameters: 1; text. Converts all letters to lowercase	`dog = LOWER("DOg")`
`REVERSE(text)`	Parameters: 1; text. Returns the text in reverse order	`dog = REVERSE("god")`
`INITCAP(text)`	Parameters: 1; text. Converts text to title case (first letter of each word uppercase, rest lowercase). Returns empty string if `text` is null	`Dog Days And Dog Nights = INITCAP("DOG days and dog nights")`
`LPAD(text, len [, pad])`	Parameters: 2-3; text, numeric. Pads the left side with spaces or the optional pad string to reach the specified length. Uses spaces if `pad` is omitted or null. If `text` is null, it participates as empty string. Returns `text` if `len` is null or less than 1	`xxxxxDog = LPAD("Dog", 8, "x")`
`RPAD(text, len [, pad])`	Parameters: 2-3; text, numeric. Pads the right side with spaces or the optional pad string to reach the specified length. Uses spaces if `pad` is omitted or null. If `text` is null, it participates as empty string. Returns `text` if `len` is null or less than 1	`Dogxxxxx = RPAD("Dog", 8, "x")`
`SUBSTITUTE(text, old, new)`	Parameters: 3; text. Replaces all occurrences of `old` with `new` in the text. Case-sensitive. Returns empty string if `text` is null. Returns `text` if `old` is null. Uses empty string if `new` is null	`SUBSTITUTE("I love cats. Cats are cute.", "cats", "dogs")` → `I love dogs. Cats are cute.`
`NUMBERSTRING(number)`	Parameters: 1; Number, Amount. Converts a number to the locale-specific uppercase numeral format. The output follows the tenant locale.	`One hundred ninety-eight thousand two hundred four point one three = NUMBERSTRING(198204.13)`
`NUMBERSTRINGRMB(number)`	Parameters: 1; Number, Amount. Converts a number to the locale-specific uppercase currency format. The output follows the tenant locale and supports fractional currency units.	`One hundred ninety-eight thousand two hundred four yuan and thirteen fen = NUMBERSTRINGRMB(198204.13)`

Boolean functions

Function	Description	Example
`<`, `>`, `>=`, `<=`, `≠`	Parameters: 2; both numeric, percent, or amount. Compares numeric values; returns true or false	`true = 1 < 2`; `false = 2 < 1`
`AND(bool1, bool2,...)`	Parameters: multiple; Boolean. Logical AND; returns true if all conditions are true	`true = AND(2>1, 5>3)`
`OR(bool1, bool2,...)`	Parameters: multiple; Boolean. Logical OR; returns true if any condition is true	`true = OR(2>1, 5<3)`
`NOT(bool)`	Parameters: 1; Boolean. Returns false if the condition is true, and vice versa	`false = NOT(2>1)`
`ISNULL(expr)`	Parameters: 1; any type. Returns true if the expression is null; otherwise false	`false = ISNULL(5)`
`ISNUMBER(string)`	Parameters: 1; string. Returns true if the string can be converted to a number	`true = ISNUMBER('5')`
`STARTWITH(s1, s2)`	Parameters: 2; string. Returns whether the string starts with the query string	`true = STARTWITH('abcdef', 'ab')`
`ENDWITH(s1, s2)`	Parameters: 2; string. Returns whether the string ends with the query string	`true = ENDWITH('aecdab', 'ab')`
`EQUALS(s1, s2)`	Parameters: 2; string. Returns whether the strings are equal (case-sensitive)	`true = EQUALS('aecdab', 'aecdab')`
`CONTAINS(s1, s2)`	Parameters: 2; string. Returns whether the query string exists in the string	`false = CONTAINS('abcdef', 'kl')`

[!WARNING] AND and OR functions evaluate every expression during execution. Even if a previous condition already determines the result, errors in subsequent expressions (such as null values in operations) still cause the entire formula to fail. Wrap potentially null fields with ISNULL() and use nested IF to isolate branches.

Filtering and sorting

Filtering

Formula fields support use as filter conditions on the data list page. The comparison operators available depend on the return value type.

[!WARNING] When a formula references global variables (current date, current time, current date-time), Personnel object fields, or uses NOW() or TODAY() functions, the formula field does not support filtering and cannot be referenced by roll-up summary fields, formula fields, or validation rules.

Sorting

Formula fields of all return types support sorting on the list page, including Number, Amount, Percent, DateTime, Date, Time, Text, and Boolean types. Only filterable formula fields are supported.

Cross-calculation between formula and roll-up summary fields

Scenario

Calculate the cost of each product under the shipping note products, and aggregate the total cost in the shipping note for profit analysis.

Logic

Roll-Up Summary Fields support aggregation and calculation on the master object or related objects.

Business examples (formula and roll-up summary field use cases):

Hierarchy model

The system enforces layer restrictions on cross-object references between formula fields and roll-up summary fields:

Layer	Description
Upper	Master object or lookup-related object fields
Current	Current object fields
Lower	Detail object or related object fields
Parent object	Master object or lookup-related object

[!NOTE] “Related object” refers to the current object in a lookup relationship. For example, if [Sales Order] has a lookup to [Customer], then [Sales Order] is a related object of [Customer]. We recommend that you start reviewing the hierarchy from the node that triggers the validation.

Hierarchy restriction rules

If a formula field on the current object is referenced by an upper-level roll-up summary field, that formula field cannot reference upper-level roll-up summary fields, default values, formula fields, or standard fields. It also cannot reference lower-level roll-up summary fields. It can reference default values and formula fields on the current object.
If a default value on the current object is referenced by an upper-level roll-up summary field, and that roll-up summary field is further referenced by a higher level, the default value on the current object can reference default values and formula fields on the current object but cannot reference roll-up summary fields on the current object. It can reference formula fields and default values of the parent object but cannot reference roll-up summary fields of the parent object.
Default values on the current object follow the same validation logic.

Hierarchy restrictions triggered by formula fields or default values

Refer to the hierarchy restriction rules and examples above.
Editing a field referenced in a default value formula on the create/edit page triggers recalculation of that default value.

Configuration page error messages

When the configuration violates the rules, the system displays:

“Multi-level formula/roll-up cross-calculation is not currently supported. You have configured [ObjectName.FieldName → ObjectName.FieldName → ObjectName.FieldName] (→ means reference)”

For example, if [Shipping Note] already has a “Store Credit” field that calculates the credit value from the related [Store] object, adding a “Discount Amount” field that references “Store Credit” on the same object produces an error on save.

Default value vs formula field

Use case comparison

Aspect	Default value	Formula
Purpose	Auto-fills an initial value; can be modified manually	Auto-generates a result; cannot be modified manually
Typical scenario	Set a default selling price based on cost plus margin; actual price is adjustable	Generate base currency amount from original currency × exchange rate at transaction time; not editable
Historical data	Historical data is not recalculated after creating or editing the default value formula	Historical data is recalculated after creating or editing the formula (with delay)
Field change response	Recalculates only when formula fields are modified on the create/edit page	Formula field always updates when referenced field values change
Other operations	UI functions and button actions do not trigger recalculation	The formula field recalculates regardless of how the referenced field is modified

[!TIP] If the calculation result must update whenever referenced fields change, or if you want to recalculate historical data after creating a new field, use a formula field.

Common default value calculation issues

Incorrect formula content:
1. The field value used in the calculation is null or incorrect. Substitute actual data values into the formula to test: - Division by zero causes the formula to fail. - Null values in arithmetic operations (addition, subtraction, multiplication, division, comparison) cause the formula to fail. - Check the “When field is null” setting in the formula. - Type conversion errors — text-to-number, text-to-date — typically fail due to incorrect formats. - If the formula uses the created time field, it cannot calculate on the create page because the created time field has a value only after the record is saved.
2. Incorrect formula logic:
- Verify which branch the formula follows during calculation. Check whether single-select/multi-select fields use value or label. If using label, verify whether multi-language settings affect the comparison.
- For single-select/multi-select default value issues, refer to the “Single-select/multi-select default value not working” section.

Default value field limitations:

Default value fields do not apply to historical data. Recalculation occurs only when referenced fields change on the create/edit page. Field changes outside the create/edit page do not trigger recalculation — for example, related data changes, button modifications, data imports, or function-based creation. For instance, if a roll-up summary field changes after data is saved, or if a lookup field value changes after the current object is saved, the default value does not recalculate.
Using the Personnel field as a default value on a detail object does not calculate on the create page.
Using the owner’s primary department as a default value does not calculate on the create page. On the edit page, recalculation occurs only if a formula-referenced field is modified.

UI events/functions:

UI event functions configured before May 1, 2024 that add or edit detail object data do not auto-calculate the default value formula (requires doCalculate(true) configuration).
UI event functions configured after that date auto-calculate.
Post-save action functions that modify default value fields may cause the submitted value to differ from the page preview.

Permission-related:

When copying data from the detail page: if the current user has read-only or invisible permission for the field, it is not copied.
If the user has permission for the default value field, only the original value is copied without recalculation.
If the field is visible and editable, the user can modify it, but consistency with the calculation formula is not guaranteed.
During data mapping: default value fields are mapped by original value without recalculation. Mapping fills the value even if the layout is read-only/hidden or the permission is read-only/invisible.
To check how data was created: view the creation record in the modification history. Click the primary attribute in the record. The data source is displayed above the creation snapshot.

Single-select/multi-select default value not working

The formula result must equal the value (API Name) of the target option, not the label.

Matching rule: The default value of Option 2 = the label + value of Option 1. That is, the option used by the default value formula (Option 1) has a value corresponding to the option API Name (label maps to value: a→1, b→2, c→3). The default value formula on Option 2 matches Option 1’s label + value. If the front end selects a for Option 1 and a1 is not found in Option 2’s values, the default value for Option 2 is not populated.

Default value recalculation when copying detail data on master-detail create/edit page

If the default value field directly or indirectly uses a roll-up summary field from a related object that aggregates fields of the current operation object, all involved data is recalculated. The roll-up summary result of the related object includes the current record.

Formula configuration examples

The following examples apply to formula fields, validation rules, and default value formulas.

Handling null values in calculations

Scenario: field1 is a number field. When the value exceeds 100, return “Yes”; otherwise return “No.”

Incorrect example:

IF($field1$>100,"Yes","No")

When field1 is null, the null value cannot be compared with 100, and the result is null.

Correct examples:

// Method 1: ISNULL for early return
IF(ISNULL($field1$),"No",IF($field1$>100,"Yes","No"))

The IF function evaluates conditions in order. When ISNULL is true, it returns “No” directly. Otherwise, it checks whether field1 exceeds 100.

// Method 2: Nested IF to isolate null check
IF(IF(ISNULL($field1$),false,$field1$>100),"Yes","No")

Combines the null check and the comparison into a single IF function. The comparison runs only when field1 is not null.

// Method 3: Null check first in AND
IF(AND(NOT(ISNULL($field1$)),$field1$>100),"Yes","No")

Uses AND to check both conditions simultaneously. The null check must be the first argument in AND.

[!NOTE] Date, single-select, and similar fields are not controlled by the “When field is null” option. They always participate as null when no value exists.

Checking whether a field is null in a formula

The page may show a field as empty, but the condition does not take effect. A field that appears empty may have a value of null or "". Using a single check method is not reliable. Use the ISNULL() function to determine whether a field value is null.

The special & symbol

The & symbol is converted to + in formulas. We recommend that you do not use it. It cannot be displayed when concatenated into text and does not work correctly in validation rule checks.

To display & in text, do not concatenate it directly in the formula. Instead, create a global variable (text type, value &) and reference the global variable in the formula.

Text concatenation

Scenario: A formula field on the Sales Order object should display “Account Name — Order Number.”

Correct example: Field1 + "-" + Field2

Incorrect example: Using &, -, or other operators for concatenation

Scenario: Build a datetime field where the date comes from a date field and the time is the current time.

Correct example:

DATETIMEVALUE(''+$field_rBj2I__c$+' '+$currentTime__g$)

Type handling for date fields in calculations

For formula fields that return DateTime, Date, or Time, the most common issues involve type conversion — such as converting datetime to date or modifying the time portion of a datetime.

Scenario: Create a formula field to calculate the creation date of a record.

Incorrect example:

DATEVALUE(YEAR($create_time$)+"-"+MONTH($create_time$)+"-"+DAY($create_time$))

When the month or day is less than 10, leading zeros are missing (e.g., 2023-7-6). The date format is invalid, and DATEVALUE returns null.

Correct examples:

// Method 1: Use the standard function directly
DATETIMETODATE($create_time$)

// Method 2: Manual zero-padding (correct logic but more complex)
DATEVALUE(YEAR($create_time$)+"-"+IF(MONTH($create_time$)<10,"0"+MONTH($create_time$),MONTH($create_time$))+"-"+IF(DAY($create_time$)<10,"0"+DAY($create_time$),DAY($create_time$)))

Scenario: Add 30 minutes to a datetime.

Incorrect example: $field_datetime__c$+HOURS(0.5) — HOURS() accepts only integers.

Correct example: $field_datetime__c$+MINUTES(30)

Scenario: Calculate the number of days between the current date and the creation date.

Incorrect example: TODAY()-$create_time$ — TODAY() returns a Date, but $create_time$ is DateTime. They cannot be subtracted directly.

Correct example: TODAY()-DATETIMETODATE($create_time$)+1

Use TODAY() for the current date, DATETIMETODATE to convert the datetime to a date, then subtract the two dates.

[!NOTE] - Check whether the date field is null and add null handling if needed. - “Date 1 - Date 2” produces a result in hours. To display days, convert in the formula. If the datetime difference is less than 1 hour and no decimal places are configured, the result shows 0.

Null value pitfalls in AND function

Scenario: Check whether a field value does not exceed 180 and a related object field is not null.

Incorrect example:

IF(AND($field_n22pu__c$!=null,$field_n22pu__c$!="",$field_n22pu__c$<=180,$account_id__r.field_cO63Q__c$!=null,$account_id__r.field_cO63Q__c$!=""),"Acquired","Self-sourced")

When field_n22pu__c is null, the formula fails because AND evaluates all expressions. Even though a null check precedes the comparison, field_n22pu__c<=180 still executes, producing a null/""<=180 error.

Correct examples:

IF(AND(IF(OR($field_n22pu__c$==null,$field_n22pu__c$==""),false,$field_n22pu__c$<=180),$account_id__r.field_cO63Q__c$!=null,$account_id__r.field_cO63Q__c$!=""),"Acquired","Self-sourced")

IF(OR(ISNULL($field_5t3I8__c$),ISNULL($name1__c$)),"Self-sourced",IF($field_5t3I8__c$<=180,"Acquired","Self-sourced"))

Text concatenation

Use + for text concatenation, not & or -. See the “Text concatenation” examples above.

Null handling for date and single-select fields in formulas

Requirement: field1 is a single-select field, field2 is a date field. When field1 contains “COVID” and field2’s year is 2023 or later, return 1; otherwise return 0.5.

Incorrect example 1:

IF(AND(CONTAINS($field1$,"COVID"),YEAR($field2$)>=2023),1,0.5)

When field2 is null, the YEAR function does not support null values and the formula fails. (The CONTAINS function handles null values, so field1 being null does not cause an error.)

Incorrect example 2:

IF(AND(NOT(ISNULL($field2$)),CONTAINS($field1$,"COVID"),YEAR($field2$)>=2023),1,0.5)

Although field2 has a null check, AND evaluates every expression, so YEAR still fails.

Incorrect example 3:

IF(OR(ISNULL($field2$),NOT(CONTAINS($field1$,"COVID")),YEAR($field2$)<2023),0.5,1)

Reverse logic, but OR also evaluates every expression, so YEAR still fails.

Correct example 1:

IF(ISNULL($field2$),0.5,IF(AND(CONTAINS($field1$,"COVID"),YEAR($field2$)>=2023),1,0.5))

Extract the field2 null check into a separate IF layer.

Correct example 2:

IF(ISNULL($field2$),0.5,IF(OR(NOT(CONTAINS($field1$,"COVID")),YEAR($field2$)<2023),0.5,1))

Reverse logic, also extracting the field2 null check separately.

Troubleshooting

Formula field value is empty (calculation delay)

Steps:

Check the audit log or confirm with the administrator whether the formula field was recently created or re-edited. Historical data is being recalculated. For large data volumes, the delay can be significant. Wait several hours or a day.
Check the modification history for bulk imports or button-triggered batch updates. Incremental calculations also have delays. If modification history is not configured for the object, ask the administrator to check the CRM logs.

Formula field value is incorrect (overwritten by function)

The calculation service produces correct results, but subsequent concurrent updates from workflows or UI functions may overwrite the value.

Steps: Check the modification history for post-action functions or UI functions that modified the field. Functions should use incremental updates:

Fx.object.batchUpdate(<api-name>, <objects>, <fields>)

When the fields parameter is omitted, a full update occurs, which may overwrite other fields.

Formula field value is incorrect (imported data decimal places)

The front end sets decimal places to 2, but data imported or written by functions may have more than 2 decimal places. The database stores higher precision than the front end displays, causing a mismatch between displayed values and calculation results.

Steps: Check import/export records or function batch update records to verify the actual data precision.

Formula field display issues

Verify that the formula content is correct.

Different values for administrators and regular users

Possible cause: Regular users enter the create/edit page via copy. Administrators have edit permission for formula-referenced fields (manually entered values participate in calculation), while regular users do not have edit permission (UI event backfill values do not trigger recalculation).

Steps: Verify whether regular users have edit permission for the formula field and whether referenced fields are populated by UI events.

Identifying formula field changes

Go to Setup > Enterprise Logs > Audit Logs, select Custom Object Management as the operation module, and use Ctrl+F to search for the object name. You can view create, edit, and delete records for formula fields.

Value exists on create/edit page but is empty or inconsistent after submission

Check the following:

The formula field directly or indirectly uses a roll-up summary field.
The roll-up summary field aggregates data from the currently submitted object.
The roll-up summary field has filter conditions, and field changes after submission cause the aggregation result to change.

Related issue: Roll-up summary field value inconsistency after submission.

Default value and formula are identical but produce different values after submission

Check the following:

Cause 1: Same as above — formula fields recalculate after submission while default values do not.
Cause 2: A pre-create validation function modified field values.
Cause 3: If the create page already shows different values, check for UI event function modifications or data from copy/mapping.

Formula field return text is truncated

Formula fields do not limit text length. However, if the formula is configured in a display field, the default length is 128.

Single-select field default value does not display

Troubleshooting:

Check whether the calculation result exists in the option values (API Name). If not, the create page cannot display it, and after submission it shows “Option deleted.”
Check whether the result complies with dependency rules. Record Type dependency rules are not visible on the create page. Check the dependency configuration in Setup.

Using formula fields in BI reports

When a formula references global variables, Personnel object fields, or uses NOW() or TODAY() functions, the formula field does not support use as a BI report dimension.

Other formula fields can be used by clicking Settings in the report Dimension area and selecting the formula field.

Can formula fields directly aggregate other field content

Formula fields cannot directly aggregate other field content. You can insert a roll-up summary field as a variable in the formula and configure the calculation based on the aggregation result. For master-detail and lookup relationship references, see the “Cross-calculation hierarchy restrictions” section above.

Counting text characters

Use the LEN function to count the number of characters in a field. For single-select fields, use the API Name that includes the label.

When formula fields cannot be referenced

Not persisted: When the formula references global variables, Personnel object fields, or uses NOW() or TODAY() functions, the result is not stored in the database. Filtering is not supported, BI dimension use is not supported, and reference by roll-up summary fields or formula fields is not supported.
Persisted but subject to hierarchy restrictions: When the formula does not reference the above content, the result is stored in the database. If it still cannot be referenced, the issue involves the cross-calculation hierarchy restrictions.

Whether formula fields update in real time

Non-persisted formula fields calculate in real time when data is queried.
Persisted formula fields calculate asynchronously in the back-end queue. They do not update in real time, so some delay exists. The delay depends on the number of queued tasks. Tenants with a dedicated database receive a dedicated calculation queue.

Timeliness for multiple records vs single record

If the formula field is newly created or re-edited, historical data is recalculated. For large data volumes, the delay can be significant.
Bulk imports or button-triggered batch updates also have incremental calculation delays.
For a single record, the formula field begins calculating as soon as the referenced field starts being edited.

Formula field timezone

Tenants with multi-timezone enabled use the tenant timezone. Tenants without multi-timezone use the server default timezone (typically UTC+8).

Return data length limits

Text type: recommended not to exceed 5,000 characters.
Number and Amount types: recommended not to exceed 50 characters (including decimals). Decimal places return up to 9 digits.

Overview

Specifications

Basic settings

Formula configuration

Return value type

Decimal places

Basic mode and advanced mode

Formula configuration notes

Null value handling

Historical data calculation on save

View calculation task progress

Object data trial calculation

AI formula assistant

Function reference

Logical functions

Numeric functions

Percent operators

Date extraction functions

Date and datetime functions

Text functions

Boolean functions

Filtering and sorting

Filtering

Sorting

Cross-calculation between formula and roll-up summary fields

Scenario

Logic

Hierarchy model

Hierarchy restriction rules

Hierarchy restrictions triggered by formula fields or default values

Configuration page error messages

Default value vs formula field

Use case comparison

Common default value calculation issues

Single-select/multi-select default value not working

Default value recalculation when copying detail data on master-detail create/edit page

Formula configuration examples

Handling null values in calculations

Checking whether a field is null in a formula

The special & symbol

Text concatenation

Type handling for date fields in calculations

Null value pitfalls in AND function

Text concatenation

Null handling for date and single-select fields in formulas

Troubleshooting

Formula field value is empty (calculation delay)

Formula field value is incorrect (overwritten by function)

Formula field value is incorrect (imported data decimal places)

Formula field display issues

Different values for administrators and regular users

Identifying formula field changes

Value exists on create/edit page but is empty or inconsistent after submission

Default value and formula are identical but produce different values after submission

Formula field return text is truncated

Single-select field default value does not display

Using formula fields in BI reports

Can formula fields directly aggregate other field content

Counting text characters

When formula fields cannot be referenced

Whether formula fields update in real time

Timeliness for multiple records vs single record

Formula field timezone

Return data length limits

Related topics