Published at: 2025-10-30
Calculated Fields
1. Calculation Field Use Cases
Users of ShareCRM often need to perform arithmetic operations—such as deriving the selling price from the official product price and a discount. Calculation fields let you implement these scenarios conveniently by computing results from other fields or constants using operators and functions.
2. Instructions
2.1 Calculation Field Configuration
ShareCRM’s Calculation Field supports different operators and functions depending on the expected return type. Provide required parameters (other fields or constants) and the formula computes the result.
- The insertable fields for calculation fields can include: single-select, single-line text, multi-line text, date/time, URL, boolean, mobile, number, percent, email, User (person), Calculation Field, and Report fields — all usable as variables.
- Calculation fields can reference Calculation Fields on lookup-related objects. (Report fields on lookup-related objects must aggregate regular fields.)
- Calculation fields can reference Report fields on lookup-related objects.
- Default value formulas can reference Calculation Fields on lookup-related objects.
- When composing formulas for Calculation Fields or Default Value formulas for preset objects, you can insert functions.
- Calculation fields apply to imported data.
2.1.1 Basic Settings
- Field Label: Define per business requirements. It must be unique within the same Primary Object.
- API Name: Auto-generated. You can click the AI button to generate an API Name (translated) if needed.
- Description (Admin Only): Add a field description; visible in the Admin Console only.
- Help Text:
- Fill this with guidance about the field. The help text shows on the Create/Edit and Detail pages.
- Hover display: On Detail and Create/Edit pages, hovering the question mark shows the help text.
- Exposed display (Create/Edit only): On Detail page the question-mark hover shows help text; on Create/Edit the help text is exposed on the page.
- Thousand-separator display: If enabled, values like 11111.00 show as 11,111.00 in UI (list, detail, create/edit). Print output does not show thousand separators.
- Masked display: If enabled, assign Roles allowed to see the real value; all other users see masked values (**) on list, detail, and when re-opening create/edit after record creation.
2.1.2 Formula Settings
Click “Calculation Settings” to configure the return type, the formula, and null-handling behavior.
2.1.2.1 Return Type Effects
- If a Calculation Field (e.g., Profit) returns DateTime, Date, Time, Text, or Boolean, and another Calculation Field, Report field, or Validation Rule references it, the “treat empty fields as zero” option does not apply. Empty values participate as nulls; if a null participates in numeric or logical operations, the formula will error and yield null. Use null-check functions to avoid this.
- If a Calculation Field returns Number, Currency (Amount), or Percent, and it is referenced by other formulas or Report/Validation rules, the system honors the “treat empty fields as zero” setting. If set to “default to zero”, nulls become 0 for calculation, so explicit null checks are not required.
2.1.2.2 Decimal Places
If decimal places = 2 and profit calculates to 6.167, the database rounds and stores 6.17; the UI also displays 6.17.
2.1.2.3 Formula Authoring Notes
- Basic Mode allows inserting only fields on the same object and global variables. To reference lookup-related objects or Primary Object fields, enable Advanced Mode. (Lookup-multi-select fields are not supported.) Lookup-related object fields available for insertion follow the same range as local object fields.
- Referencing Report fields on related objects or Calculation Fields on related objects results in asynchronous computation; results may be delayed.
- Calculation formulas that reference global variables (Current Date, Current Time, Current DateTime), User object fields, or use NOW() or TODAY() cannot be used as filterable fields nor be referenced by Report fields, Calculation Fields, or Validation Rules.
- Date subtraction like “Date1 - Date2” returns hours. To display days, convert hours to days in the formula.
- The “&” symbol is converted to “+” in formulas and is not recommended. To include “&” in text, create a global variable (Text type) set to “&” and reference that variable in the formula.
- CASE function: each possible result and the else_result must be of the same type. Example: CASE($field$,”Tier1”,60.5,”Tier2”,49.0,0) is invalid because 0 is integer while other results are decimal — change 0 to 0.0.
2.1.2.4 Null-Handling in Formulas
This option applies only to Calculation Fields whose return type is numeric (Number, Currency, Percent). For non-numeric return types, referenced fields that are null participate as nulls.
1) Treat referenced fields as zero when null: - For numeric return types, referenced nulls are replaced with 0 for calculation. - For other return types, referenced nulls still participate as nulls.
2) Treat referenced fields as null: - For numeric return types, referenced nulls remain null.
2.1.2.5 Test Calculation on Existing Records
If test calculation fails and the Calculation Field has no value, common causes: 1. Some referenced fields are null but neither default-to-zero nor explicit null checks are applied. 2. Type-conversion errors (text→number, text→date) due to format mismatch. 3. If the discrepancy only appears between test result and actual submission (for default values or validation rules), check whether fields referenced by the formula changed — especially Calculation or Report fields, which do not always appear in modify-log cascades.
2.1.3 AI-Assisted Formula Generation
Calculation Fields support AI-assisted formula generation. Steps:
- When creating a Calculation Field, go to “Calculation Settings”.
- Type “/” or click “AI Formula Assistant” to open the assistant.
- Provide a description of the desired formula; the assistant generates a formula automatically.
- Click “Copy” or “Apply” to insert the AI-generated formula.
2.1.4 Important Notes
When saving a field on the edit page, if the dataset exceeds 10,000 records, a prompt appears:
- “Do not recalculate existing data”: the system will not recalculate historical records created before the Calculation Field was added.
- “Recalculate all (may take longer for large datasets)”: the system recalculates all existing records for that object; due to asynchronous processing, values might be empty for a while.
- “Recalculate only matching records”: supply filter conditions; the system recalculates only records that meet the filter.
- If the dataset ≤ 10,000 records, no prompt appears and the system saves normally.
2.1.5 View Calculation Task Progress
After creating or editing a Calculation Field, historical recalculation may take time. Use the task progress entry to monitor execution. While tasks are running or queued, the list view displays a “Calculation Task Progress” button. Click it to see a modal showing task status, total records, completed records, and estimated completion time.
Caption: Configuration highlights
2.1.5 Supported Functions and Operators
The following table summarizes available functions/operators, input types, and behavior. (Examples illustrate usage.)
| Return Type | Operator / Function | Parameters | Purpose | Notes / Examples |
|---|---|---|---|---|
| General | () | — | Grouping to control precedence | — |
| Depends on input | IF(logical_test, value_if_true, value_if_false) | 3 params; 1st boolean, 2nd and 3rd must be same type | Return value based on condition; supports nesting. Note: IF evaluates all branches — errors in any branch (divide by zero, null in logic, type mismatch) cause formula failure. | 34 = IF(true, 34, 52) |
| Depends on input | CASE(expression, value1, result1, …, else_result) | Variable params; result(n) and else_result must share type | Return matching result for expression==value(n); up to 50 value tests | 33 = CASE(3, 2, 2.0, 3, 33.0, 1.3) |
| Depends on input | NULLVALUE(expression, substitute_expression) | 2 params; if expression is null return substitute_expression | Replace null with substitute value | 1 = NULLVALUE(Null, 1) |
| Number | +, -, *, / | 2 params; Number/Percent/Currency | Arithmetic | 6 = (3+2)*6/5 |
| Number (date diff) | - | 2 dates → days | Date subtraction returns days | e.g., a=1982-04-13, b=1982-03-20 → 24 = a - b |
| Number (datetime diff) | - | 2 datetime → hours | DateTime subtraction returns hours | e.g., 26 = a - b |
| Number (time diff) | - | 2 times → hours | Time subtraction returns hours | e.g., 2 = a - b |
| Number | VALUE(string) | 1 text | Convert numeric text to number; returns null on failure | -1982.0413 = VALUE(‘-1982.0413’), Null = VALUE(‘aaa’) |
| Number | MIN(number1, number2) | 2 numbers | Return smaller | 4 = MIN(4,13) |
| Number | MAX(number1, number2) | 2 numbers | Return larger | 13 = MAX(4,13) |
| Number | MULTIPLE(number1, number2) | 2 numbers | Multiply | 52 = MULTIPLE(4,13) |
| Number | MOD(number1, number2) | 2 numbers | Remainder (integer part) | 1 = MOD(13,4) |
| Number | ADDS(number1, number2) | 2 numbers | Addition | 17 = ADDS(13,4) |
| Number | SUBTRACTS(number1, number2) | 2 numbers | Subtraction | 9 = SUBTRACTS(13,4) |
| Number | YEAR(date) | 1 date/datetime | Extract year | 1982 = YEAR(1982-04-13) |
| Number | MONTH(date) | 1 date/datetime | Extract month | 4 = MONTH(1982-04-13) |
| Number | DAY(date) | 1 date/datetime | Extract day | 13 = DAY(1982-04-13) |
| Number | LEN(text) | 1 text | String length | 6 = LEN(‘xiaoke’) |
| Number | HOUR(datetime_or_time) | 1 datetime/time | Hour component | 14 = HOUR(DATETIMEVALUE(“2025-07-31 14:30:45”)) |
| Number | MINUTE(datetime_or_time) | 1 datetime/time | Minutes component | 30 = MINUTE(DATETIMEVALUE(“2025-07-31 14:30:45”)) |
| Number | SECOND(datetime_or_time) | 1 datetime/time | Seconds component | 45 = SECOND(DATETIMEVALUE(“2025-07-31 14:30:45”)) |
| Number | MILLISECOND(datetime_or_time) | 1 datetime/time | Milliseconds component | 0 = MILLISECOND(DATETIMEVALUE(“2025-07-31 14:30:45”)) |
| Number | UNIXTIMESTAMP(dt_or_date_or_time) | 1 datetime/date/time | Milliseconds since 1970-01-01, or milliseconds in a day for time | 1753943445000 = UNIXTIMESTAMP(DATETIMEVALUE(“2025-07-31 14:30:45”)) — note: multi-timezone tenants use tenant timezone when applicable; otherwise use UTC+8 |
| Number | DAYOFYEAR(date) | 1 date | Day of year (1–366) | 1 = DAYOFYEAR(DATE(2025,1,1)) |
| Number | WEEKDAY(date) | 1 date | Weekday number (1 = Monday … 7 = Sunday) | 1 = WEEKDAY(DATE(2025,7,28)) |
| Number | ISOYEAR(date) | 1 date | ISO year per ISO 8601 (week-based) | 2025 = ISOYEAR(DATE(2025,7,31)) |
| Number | ISOWEEK(date) | 1 date | ISO week number (1–53) | 21 = ISOWEEK(DATE(2024,5,20)) |
| Number | MAX(number,…) | variadic | Max of arguments. Returns null if any argument is null. | 120 = MAXVALUE(100,80,120) |
| Number | MIN(number,…) | variadic | Min of arguments. Returns null if any argument is null. | 80 = MINVALUE(100,80,120) |
| Number | ROUND(number,num_digits) | 2 numbers | Round to num_digits | 1.5 = ROUND(1.45,1) |
| Number | ROUNDDOWN(number,num_digits) | 2 numbers | Round down to num_digits | 1.4 = ROUNDDOWN(1.47,1) |
| Number | ABS(number) | 1 number | Absolute value | 1.5 = ABS(-1.5) |
| Number | EXP(number) | 1 number | e^number | 2.7182818 = EXP(1) |
| Number | SQRT(number) | 1 number | Square root | 4 = SQRT(16) |
| Number | LN(number) | 1 number | Natural log | 3.21888 = LN(25) |
| Number | LOG(number) | 1 number | Base-10 log | 2 = LOG(100) |
| Number | SIN(number) | 1 number (radian) | Sine | 0.5 = SIN(PI()/6) |
| Number | ASIN(number) | 1 number | arcsin in radians (null if out of [-1,1]) | PI/2 = ASIN(1) |
| Number | COS(number) | 1 number (radian) | Cosine | 0.5 = COS(PI()/3) |
| Number | ACOS(number) | 1 number | arccos in radians (null if out of [-1,1]) | PI/3 = ACOS(0.5) |
| Number | TAN(number) | 1 number (radian) | Tangent | 1 = TAN(PI()/4) |
| Number | ATAN(number) | 1 number | arctangent | PI/4 = ATAN(1) |
| Number | ATAN2(y,x) | 2 numbers | arctangent of y/x in radians | PI/4 = ATAN2(1,1) |
| Number | PI() | — | π | 3.141592653589793 = PI() |
| Number | FIND(search_text, text [, start_num]) | start_num optional | Returns starting position (1-based) of search_text in text; returns 0 if not found; start_num decimals truncated | 1 = FIND(“dog”,”dog days and dog nights”) |
| Percent | +,-,*,/ | 2 params | Arithmetic returning percentage form | — |
| Date/DateTime helper | YEARS(number) / MONTHS(number) / DAYS(number) / HOURS(number) / MINUTES(number) | 1 number | Add relative years/months/days/hours/minutes to a date/datetime | TODAY()+YEARS(1) etc. |
| Date | +,- (date + DAYS(…)) | date + offset | Date arithmetic | CreateDate + DAYS(4) |
| Date | DATE(year, month, day) | 3 numbers | Create date from numbers | DATE(1982,4,13) |
| Date | DATEVALUE(string) | 1 string | Parse date from string “yyyy-MM-dd” | DATEVALUE(‘1982-04-13’) |
| Date | TODAY() | — | Current date | — |
| Date | DATETIMETODATE(datetime) | 1 datetime | Extract date part | DATETIMETODATE(‘1982-04-13 17:00:00’) |
| DateTime | +,- (datetime + HOURS(…)) | datetime + offset | DateTime arithmetic | DeadlineTime - DAYS(1) |
| DateTime | DATETIMEVALUE(string) | 1 string | Parse datetime | DATETIMEVALUE(‘2001-08-24 15:45:25’) |
| DateTime | NOW() | — | Current date and time | — |
| Time | +,- (datetime + HOURS(…)) | datetime + time offset | Time arithmetic; results bounded between 00:00–23:59 | submitDailyReport - HOURS(1) |
| Time | TIMENOW() | — | Current time | TIMENOW (multi-timezone uses tenant timezone when applicable; otherwise UTC+8) |
| DateTime | DATETIMETOTIME(datetime) | 1 datetime | Extract time part | DATETIMETOTIME(‘1982-04-13 17:02:40’) |
| DateTime | FROMUNIXTIME(unixtimestamp) | 1 number | Parse datetime from milliseconds since 1970-01-01 | FROMUNIXTIME(1753943445000) (timezone rules as above) |
| Text | & (concatenation) | 2 text | Concatenate strings | “Alice” & “Bob” → AliceBob |
| Text | ’’ (single quotes) | 1 string | Single-line text constant | ‘single-line’ |
| Text | ’’’’’’ (triple quotes) | multiline | Multi-line text constant | ’'’multi-line’’’ |
| Text | NUMBERSTRING(number) | 1 number | Convert number to Chinese uppercase numerals (display) | NUMBERSTRING(198204.13) |
| Text | NUMBERSTRINGRMB(number) | 1 number | Convert number to Chinese uppercase RMB (up to jiao, fen) | NUMBERSTRINGRMB(198204.13) |
| Text | UPPER(text) | 1 text | Uppercase letters | UPPER(“doG”) → DOG |
| Text | LOWER(text) | 1 text | Lowercase letters | LOWER(“DOg”) → dog |
| Text | REVERSE(text) | 1 text | Reverse string | REVERSE(“god”) → dog |
| Text | TEXT(value) | 1 value | Convert value to text using standard display format. Null => empty string. Percent fields returned as value/100. Date/DateTime/Time returned as formatted text. | TEXT(DATE(2025,8,1)) → “2025-08-01” |
| Text | LEFT(text, num_chars) | 2 params | Left substring; returns empty string if text null or num_chars < 1 | LEFT(“dog days and dog nights”, 8) → “dog days” |
| Text | RIGHT(text, num_chars) | 2 params | Right substring; similar rules | RIGHT(“dog days and dog nights”, 10) → “dog nights” |
| Text | MID(text, start_num, num_chars) | 3 params | Middle substring; start index 1-based | MID(“dog days and dog nights”, 10, 3) → “and” |
| Text | LPAD(text, padded_length [, pad_string]) | 2–3 params | Left-pad to length with pad_string (default space) | LPAD(“Dog”,8,”x”) → “xxxxxDog” |
| Text | RPAD(text, padded_length [, pad_string]) | 2–3 params | Right-pad to length with pad_string (default space) | RPAD(“Dog”,8,”x”) → “Dogxxxxx” |
| Text | SUBSTITUTE(text, old_text, new_text) | 3 params | Replace all occurrences (case-sensitive) | SUBSTITUTE(“I love cats. Cats are cute.”,”cats”,”dogs”) |
| Text | INITCAP(text) | 1 param | Lowercase text and capitalize first letter of each word | INITCAP(“DOG days and dog nights”) |
| Boolean | <, >, >=, <=, ≠ | 2 numeric params | Numeric comparisons return true/false | true = 1 < 2 |
| Boolean | AND(boolean1, boolean2, …) | multiple | True if all conditions true. Note: all expressions are evaluated — avoid expressions that error when other branches are null | AND(2>1, 5>3) |
| Boolean | OR(boolean1, boolean2, …) | multiple | True if any condition true (all expressions evaluated) | OR(2>1,5<3) |
| Boolean | NOT(boolean) | 1 param | Logical negation | NOT(2>1) → false |
| Boolean | ISNULL(expression) | 1 param | True if expression is null | ISNULL(5) → false |
| Boolean | ISNUMBER(string) | 1 param | True if string can be converted to number | ISNUMBER(‘5’) → true |
| Boolean | STARTWITH(string1, string2) | 2 params | True if string1 starts with string2 | STARTWITH(‘abcdef’,’ab’) → true |
| Boolean | ENDWITH(string1, string2) | 2 params | True if string1 ends with string2 | ENDWITH(‘aecdab’,’ab’) → true |
| Boolean | EQUALS(string1, string2) | 2 params | Case-sensitive equality | EQUALS(‘aecdab’,’aecdab’) → true |
| Boolean | CONTAINS(string1, string2) | 2 params | True if string1 contains string2 | CONTAINS(‘abcdef’,’kl’) → false |
(Additional note: the original table includes several example and edge-case notes—those details are preserved in the function descriptions above.)
There are also a couple small sample rows reproduced below for clarity:
| Type: Number | Create a date by number parts | Example: DATE(1982,4,13) |
2.2 Filtering on Calculation Fields
Calculation Fields can be used as filters on the list view. Filter operators available depend on the Calculation Field return type.
Important: If a Calculation Field formula references global variables (Current Date/Time/DateTime), User object fields, or uses NOW() / TODAY(), then that Calculation Field is not filterable and cannot be used by Report fields, Calculation Fields, or Report aggregations.
2.3 Calculation Fields and Report Fields Interactions
2.3.1 Example Scenario
You define a cost Calculation Field at the Shipping Order Product (sub-object) level and want to aggregate total cost on the Shipping Order (Primary Object) to analyze profit.
2.3.2 Logical Analysis
Report fields support aggregation needs across Primary Objects or related objects.
(Example diagram)
This section lists practical uses of Calculation Fields and Report fields for profit analysis: configure per the illustrated example to aggregate costs and compute profit on the Primary Object.
2.3.3 Hierarchy Limits for Mutual Calculation/Aggregation
For performance, there are limits on the number of chained calculation/aggregation layers. Refer to the diagram below for layer rules.
Caption: Figure 4-1
Layer definitions: - Upper layer: Primary Object or lookup-related object fields - Current layer: fields on the current object - Lower layer: Sub-object or related object fields - Parent object: Primary Object or lookup-related object
Notes: - “Related object” refers to objects that are lookup-related to the current object. For example, Sales Order lookup to Account => Sales Order is a related object of Account. - Start analyzing from the node that triggers validation for clarity.
Detailed rules: - If a Calculation Field on the current object is referenced by a Report field on a Parent or lookup-related object, then that Calculation Field must not reference Report fields, Default Values, Calculation Fields, or regular fields on the Parent or lookups. It also must not reference Report fields on Sub-objects/related objects. It can reference Default Values and Calculation Fields on the same object. Default Value fields follow similar validation rules. - If a Default Value on the current object is referenced by a Report field on a Parent or lookup-related object, and that Report field in turn is referenced by an upper-level Report field, then the Default Value may reference local Default Values and Calculation Fields, but not local Report fields. The Default Value may reference Calculation Fields and Default Values on the parent object but not parent Report fields. Calculation Fields follow the same logic.
Example: - If a Calculation Field on Quotation triggers validation and it references a local Report field, then that Report field must not reference Calculation/Report fields on Quotation Line items; it may only reference Default Values on Quotation Line items.
2.3.4 Hierarchy Limits When Triggered by Calculation Field or Default Value
Refer to the hierarchy explanation in the previous section. Editing a field in a Default Value formula on the create/edit page triggers the Default Value calculation.
- Follow the same constraints as when Report fields initiate validation.
- Editing fields used by Default Value on the create/edit page will trigger the Default Value calculation.
2.3.5 Configuration Error Messages
When configuration violates rules, the system shows a descriptive error such as: “System does not support multi-level calculation/aggregation. You configured: ObjectA.Field -> ObjectB.Field -> ObjectC.Field (-> denotes reference).”
Example: - If Shipping Order has a “Store Credit” Calculation Field that fetches credit from related Store, and you add a “Discount Amount” Calculation Field that references the local “Store Credit”, saving the field will error because the chain crosses unsupported multi-level references.
(Example images)
2.4 Sorting by Calculation Field
Any Calculation Field return type can be sorted in list views—including Number, Currency, Percent, DateTime, Date, Time, Text, and Boolean—provided the Calculation Field is filterable.
2.5 Formula Examples and Best Practices
This chapter applies to Calculation Fields, Validation Rules, and Default Value formulas.
Handling Nulls in IF
Scenario: field1 is a Number. Return “Yes” if field1 > 100, otherwise “No”.
Incorrect: IF($field1$>100,”Yes”,”No”) - If field1 is null, comparison fails and the formula returns null.
Correct patterns: 1) IF(ISNULL($field1$),”No”,IF($field1$>100,”Yes”,”No”)) - Check null first, then evaluate comparison.
2) IF(IF(ISNULL($field1$),false,$field1$>100),”Yes”,”No”) - Consolidate null check and comparison inside IF.
3) IF(AND(NOT(ISNULL($field1$)),$field1$>100),”Yes”,”No”) - Use AND; ensure the null-check is placed first to avoid evaluating the numeric comparison on null.
Note: Date, single-select, and similar fields are not controlled by the “treat referenced null as zero” option; they always participate as nulls if empty.
Composite condition with single-select and date
Requirement: field1 single-select contains “COVID” and year(field2) >= 2023 → return 1, else 0.5.
Incorrect: IF(AND(CONTAINS($field1$,”COVID”),YEAR($field2$)>=2023),1,0.5) - YEAR(null) errors.
Correct: 1) IF(ISNULL($field2$),0.5,IF(AND(CONTAINS($field1$,”COVID”),YEAR($field2$)>=2023),1,0.5)) - Handle null of field2 first.
2) IF(ISNULL($field2$),0.5,IF(OR(NOT(CONTAINS($field1$,”COVID”)),YEAR($field2$)<2023),0.5,1)) - Rewritten logic, null-check isolated.
Use ISNULL to detect null vs empty string
On UI you may see a field that looks empty but could be null or an empty string “”. Use ISNULL() to test for null reliably.
Ampersand (&) Special Case
“&” is converted to “+” in formulas and is not recommendable. To include “&” in text, create a global variable (Text) containing “&” and reference that in the formula.
Text Concatenation
Scenario: On Sales Order, show “AccountName - OrderNumber”.
Correct: field1 + “-“ + field2
Incorrect: Using & or other unsupported operators for concatenation (use +).
Scenario: Build a DateTime from a date field and the current time. Correct: DATETIMEVALUE(‘’ + $dateField$ + ‘ ‘ + $currentTime__g$)
AND logic caveat
Incorrect: IF(AND($fieldA$!=null,$fieldA$!=””,$fieldA$<=180,$account__r.fieldB$!=null,$account__r.fieldB$!=””),”New”,”Self”) - Because AND evaluates all branches, a null in $fieldA$ still triggers a comparison $fieldA$ <= 180 and errors.
Correct: IF(AND(IF(OR($fieldA$==null,$fieldA$==””),false,$fieldA$<=180),$account__r.fieldB$!=null,$account__r.fieldB$!=””),”New”,”Self”)
Or: IF(OR(ISNULL($field_5t3I8__c$),ISNULL($name1__c$)),”Self”,IF($field_5t3I8__c$<=180,”New”,”Self”))
Date type handling in formulas
When returning Date/DateTime/Time types, type conversion issues are common.
Scenario: Compute the date part from CreatedTime.
Incorrect: DATEVALUE(YEAR($create_time$) + “-“ + MONTH($create_time$) + “-“ + DAY($create_time$)) - If month/day < 10, missing leading zero causes parse failure. Date format must be “yyyy-MM-dd”.
Correct: 1) DATETIMETODATE($create_time$) - Use provided function for robust conversion.
2) 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: $field_datetime__c$ + HOURS(0.5) - HOURS accepts integers only.
Correct: $field_datetime__c$ + MINUTES(30)
Scenario: Days between Today and CreatedTime
Incorrect: TODAY() - $create_time$ - TODAY() is Date; $create_time$ is DateTime — cannot subtract.
Correct: TODAY() - DATETIMETODATE($create_time$) + 1
Notes: - Check nulls. - Date1 - Date2 returns hours; convert to days as needed. - If interval < 1 hour and decimals not configured, the display may show 0.
Code samples (utility notes):
long toTimestamp = date.toTimestamp()
// Convert timestamp to Date/DateTime DateTime dateTime = DateTime.of(toTimestamp) Date date = Date.of(toTimestamp)
// Convert to timestamp then compute Date date = Date.now() // Add one hour: timestamp + 3600 * 1000 ms long toTimestamp = date.toTimestamp() + 3600 * 1000 DateTime dateTime = DateTime.of(toTimestamp) Fx.log.info(dateTime) // Date, DateTime, Time are essentially millisecond timestamps.
3. Differences Between Default Values (Formulas) and Calculation Fields
3.1 Use Cases
- Default Value: Set a formula to populate a field when the user has not entered a value. After the default is generated, users may edit the field. For example, set a product’s default Sales Price as Cost plus expected margin; users can override this price afterwards.
- Calculation Field: Use a formula to derive a field value that cannot be manually changed. Use Calculation Fields where the computed value must remain consistent (e.g., convert Original Currency amounts to Base Currency at transaction time).
3.2 Differences in Calculation Behavior
3.2.1 Historical Data Recalculation on Create/Edit
- Default Value: Editing or creating a Default Value formula does not recalculate historical data.
- Calculation Field: Editing a Calculation Field formula triggers recalculation of historical data, but recalculation is asynchronous and may be delayed.
3.2.2 Recalculation After Referenced Field Changes
- Default Value: Only recalculated on create/edit pages when referenced fields change. Other contexts (UI functions, button actions, imports) do not trigger recalculation.
- Calculation Field: Value updates dynamically when referenced fields change both on create/edit and after a record is saved.
Summary: If you need results that always update when referenced fields change, or you need historical data recalculated after creating the field, choose a Calculation Field.
3.3 Common Default Value Issues
3.3.1 Default Value Not Calculating or Calculating Incorrectly
Check the following:
Formula errors: - Referenced fields are null or invalid. Plug actual data into the formula to test. - Division by zero causes formula errors. - Nulls in numeric or logical expressions cause errors unless handled. - Type conversion errors (text→number, text→date) often stem from wrong formats. - Using Created Time in a Default Value: On the create page, Created Time does not yet exist, so Default Value will not compute. - Check the “treat referenced null as zero” option where applicable.
Formula logic: - Ensure you compare against value vs label for single/multi-selects. Use the correct apiname/value to avoid multi-language issues. - Refer to “2.5 Formula Examples” for patterns.
Default value field constraints: - Default Values do not apply to historical data. They compute only when relevant fields change on create/edit pages. Changes from imports, button operations, or mapped data do not trigger Default Value recalculation. - Sub-object owner fields used in Default Values may not compute on create page. - Using owner’s primary Dept. in Default Value: not computable on create page; edit page recalculation requires a change in referenced fields.
UI events / function behavior: - Before 2024-05-01, UI event functions that add/edit sub-object data did not trigger Default Value recalculation automatically (doCalculate(true) required). - After 2024-05-01, UI events that add/edit sub-object data automatically trigger Default Value recalculation. - If functions modify Default Value fields after creation (post-actions on Save buttons), the stored default may differ from what was shown in create/edit.
Permission-related constraints: - When copying from Detail to Create page: if a user is read-only or cannot view a field, the field value may not be copied. - If the user has permission, copied Default Value preserves original value and does not recalculate. - If displayed and editable, users can change defaults; the value may diverge from formula output. - Data mapping copies Default Value original values without recalculation. Read-only layout / hidden layout / read-only permissions do not prevent mapping. - To inspect how a record was created, open the create record entry in the change log and view the source above the snapshot.
3.3.2 Single/Multi-select Default Value Not Filling
Causes: - The computed default value (formula result) must match an option’s value (apiname). If it does not, the UI will not pre-fill the option.
Matching rule: - Default Value formula must return the option’s value (apiname), not label. Example: if option1 value is “a” with apiname “a1” (where label vs value mismatch occurs), ensure the returned value matches the target option’s apiname.
(Example images)
Caption: Terminology illustration
3.3.3 Creating Parent with Child records: Copied sub-object defaults recomputed
If a Default Value uses a related object’s Report field that aggregates the current object, then when copying sub-object data on parent create/edit, involved data will be recalculated, and the related object’s aggregation will include the current record.
4. Common Issues with Calculation Fields and Troubleshooting
4.1 Empty Calculation Field Values Due to Recalculation Delay
4.1.1 Scenario
Admin edits a Calculation Field on Quotation Line (e.g., Quotation Discount). Test calculation succeeds, but some records in the UI show no value—making it appear as if the Calculation Field did not compute.
4.1.2 Troubleshooting
- Check Audit Logs or confirm with admins whether the Calculation Field was newly created or re-edited. Historical recalculation starts but can be slow on large datasets—wait several hours or a day.
- Check the change history for bulk imports or bulk updates (buttons, UI functions). Such incremental recalculations are also queued and delayed. If the object lacks change logs, ask an admin to check the CRM Report/Task logs for queued tasks.
4.2 Formula Values Overwritten by Functions
4.2.1 Possible Cause
The Calculation Field computes correctly on the calculation service, but subsequent workflows, UI functions, or other automation may concurrently update the record and overwrite the computed value.
4.2.2 Troubleshooting
Review change logs (ask implementation team). If workflows or functions run after creation/update, modify them to use incremental updates:
Fx.object.batchUpdate()
- Provide the fields list to update only specific fields; otherwise a partial update may overwrite unrelated fields.
- When using functions, prefer writing values rounded to the same decimal places shown in UI (rounded value) to avoid rounding discrepancies.
4.3 Imported Data Decimal Precision Causing Calculation Errors
4.3.1 Scenario
Profit = (Sales Price - Cost) * Quantity. Profit configured with 2 decimals. Imported/updated Cost = 4.50, Sales = 4.594, Quantity = 4 → computed profit 0.376. Database rounds and stores profit = 0.38; Sales stored as 4.59 or 4.95 depending on example (note rounding differences). Using displayed Sales (4.95) would yield a different profit.
4.3.2 Troubleshooting
Check whether data was imported or updated via functions (review import/export logs and function updates). If UI shows 2 decimal places but backend values have more precision, database values differ from UI-displayed values. Import data should match the UI precision or functions should round before persisting.
4.4 Incorrect Display of Calculation Field Value
4.4.1 Cause
Formula error or misconfiguration causing a different result than expected.
4.4.2 Troubleshooting
Validate the formula content and referenced fields.
4.5 Admin vs Regular User See Different Calculation Field Values
4.5.1 Cause
This occurs when Admin has edit permission on referenced fields and manually set values that affect calculation, while regular users see values populated by UI events or copied records where referenced fields are filled differently. UI-event-filled referenced fields may prevent Calculation Field participation for users without edit permission.
4.5.2 Troubleshooting
- Confirm whether regular users have edit permission for referenced fields.
- Confirm whether referenced fields are filled by UI events (which may not trigger calculation for some roles).
4.6 Identifying Calculation Field Changes
4.6.1 View Calculation Field changes via Audit Logs
Scenario
You want to know who created, deleted, or edited a Calculation Field, or who toggled thousand-separator display.
Steps
Open Admin Console → Reports → Audit Logs. In the Audit Logs, choose “Custom Object Management”. Use Ctrl+F and search for the object name to see when a Calculation Field was created, edited, or deleted and by whom.
4.7 Calculation Field Has Value on Create/Edit but None After Submit
Causes / Troubleshooting: - The Calculation Field directly/indirectly references a Report field. - The Report field aggregates the object being submitted. - The Report field includes filters; after submit, a filter condition may change, causing the Report field value to change and thus Calculation Field result to differ. See also: Report field inconsistent post-submit.
4.8 Default Value and Calculation Field Formulas Identical but Values Differ on Submit
Causes: 1. Same as 4.7: Calculation Fields may recalc after submit while Default Values do not. 2. Pre-create validation functions modify field values. 3. UI event functions, copy, or data mapping may result in different values on create page.
4.9 Calculation Field Returning Text Is Truncated
Cause: - Calculation Fields themselves do not limit text length. However, if the formula is used in a display field, default length may be 128 characters.
4.10 Single-select Default Value Configured but Not Displayed
Troubleshooting: 1. Ensure the computed value matches an option apiname (value). If it does not exist, the create page will not show the option; on submit the system may show the option is deleted. 2. Ensure the computed option satisfies dependent picklist rules / Record Type dependencies — these dependencies may not show on create page; check Admin Console dependencies.
(Example image)
4.7 Using Calculation Fields as Dimensions in BI Reports
4.7.1 Preconditions
If a Calculation Field formula references global variables (Current Date/Time/DateTime), User fields, or uses NOW() / TODAY(), it cannot be used as a filter or as a BI report dimension; nor can it be referenced by Report or other Calculation Fields.
4.7.2 How to Use
In the BI report designer, click “Dimensions” → “Settings” and choose the Calculation Field to add it as a report dimension.
4.8 Can a Calculation Field Be Directly Aggregated into Other Fields?
No. Calculation Fields cannot be directly aggregated into other fields. Instead, insert Report fields (that aggregate values) into calculation formulas. For questions about aggregation references across Primary/Sub-object or lookup relations, refer to the “Calculation/Report Mutual Hierarchy Limits” section.
4.9 Counting Characters in Another Field Using Calculation Field
4.9.1 How-To
Use LEN(string) in the formula with the target field API name. To count what users see for a single-select field, use the option label apiname (value that returns the label) when available.
4.10 When Calculation Fields Cannot Be Referenced
4.10.1 Non-persisted Calculation Fields
If a Calculation Field formula references global variables (Current Date/Time/DateTime), User fields, or uses NOW() / TODAY(), that Calculation Field is non-persisted (not stored in the database). Non-persisted fields: - Are not stored in DB. - Cannot be used as filters or in BI report dimensions. - Cannot be referenced by Report or other Calculation/Default formulas.
4.10.2 Persisted Calculation Fields Still Not Referenceable
If a Calculation Field is persisted (does not reference the special globals), but still cannot be referenced by other Calculation Fields, Report fields, or Default Values, this indicates the chain exceeds the permitted calculation/aggregation hierarchy limits.
4.11 Calculation Field Timeliness
4.11.1 Are Calculation Fields Real-time?
- Non-persisted Calculation Fields: computed at query-time (real-time).
- Persisted Calculation Fields: computed asynchronously by background queues — not real-time. Delays depend on pending queue tasks. Tenants with dedicated databases get dedicated calculation queues.
4.11.2 Multi-record Timeliness
- Recreating or re-editing Calculation Fields recalculates historical data asynchronously; large datasets delay longer.
- Bulk imports or bulk updates (buttons/functions) create incremental recalculation tasks that may be delayed.
4.11.3 Single-record Timeliness
- Calculation starts when a referenced field begins editing.
4.12 Time Zone Handling in Calculation Fields
For tenants with multi-timezone rollout, DateTime functions use the tenant timezone. For tenants without multi-timezone rollout, server default timezone is used (typically UTC+8).
4.13 Maximum Returned Length / Precision
- Text-returning Calculation Fields: recommended max length ≤ 5000 characters.
- Number/Currency-returning Calculation Fields: recommended total digits ≤ 50, including decimals; decimal portion ≤ 9 digits.