Published at: 2025-10-30
Calculated Field
1. Use Cases for Calculated Fields
When using ShareCRM products, users often need to perform various mathematical operations, such as calculating shipment prices based on product list prices and discounts. Calculated fields provide a convenient way to meet these requirements.
2. Operational Guide
2.1 Calculated Field Configuration
ShareCRM’s calculated fields support different operators and functions to produce expected results by combining required parameters (other fields or constants).
- Supported variable types for calculated fields: single-select, single-line text, multi-line text, date/time, URL, boolean, phone number, numeric, percentage, email, person, calculated fields, and aggregated fields.
- Calculated fields support referencing calculated fields from lookup-related objects (aggregated fields must aggregate regular fields).
- Calculated fields support referencing aggregated fields from lookup-related objects.
- Default value formulas support referencing calculated fields from lookup-related objects.
- Preset object calculated fields and default value formulas support inserting functions.
- Calculated fields take effect on imported data.
2.1.1 Basic Settings
- Field Name: Customizable based on business requirements, cannot duplicate existing object names.
- API Name: Automatically generated by the system, can be regenerated by clicking the AI button for translated names.
- Description: Field description visible only in Admin Console, not displayed in the frontend.
- Help Information:
- Provides field-related information displayed in frontend create/edit and detail pages.
- Hover tooltip display: Shows field help information when hovering over the question mark in detail and create/edit pages.
- External display (create/edit pages only): Shows field help information when hovering in detail pages and displays directly in create/edit pages.
- Thousand Separator: If enabled, values like 11111.00 will display as 11,111.00 in frontend (list, detail, create/edit pages). Not shown in print results.
- Mask Display: If enabled, specific roles can be added. Users without these roles will see masked values (**) in list pages, detail pages, and create/edit pages after data creation.
2.1.2 Formula Settings
Click “Calculation Settings” to configure the return type, calculation formula, and null value handling for calculated fields.
2.1.2.1 Return Type Settings and Triggered Configurations
- When the return type is date/time, date, time, text, or boolean, and other calculated fields, aggregated fields, or validation rules reference this field, the “Default to zero when field is null” setting doesn’t apply. Null values participate directly in calculations, potentially causing errors if used in mathematical operations or logical judgments. Use null-check functions in such cases.
- When the return type is numeric, currency, or percentage, and other calculated fields, aggregated fields, or validation rules reference this field, the “Default to zero when field is null” setting determines whether null values are converted to zero before calculations.
2.1.2.2 Decimal Places
If set to 2, a calculated value of 6.167 will be rounded to 6.17 in the database and displayed as such in the frontend.
2.1.2.3 Formula Configuration Notes
- Basic mode only allows inserting fields from the current object and global variables. To reference fields from lookup-related or primary objects, enable Advanced Mode (multi-select lookup fields are not supported). The available field range for lookup-related objects matches that of the current object.
- Calculations involving aggregated fields or related object calculated fields are asynchronous and may experience delays.
- Formulas referencing global variables (current date, current time, current datetime), person object fields, or using NOW()/TODAY() functions cannot be filtered or referenced by other aggregated fields, calculated fields, or validation rules.
- For date subtraction (e.g., “Date1 - Date2”), the result is in hours. To display days, convert manually in the formula.
- The “&” symbol is converted to “+” in formulas and is not recommended. To display “&” in text, configure a global variable (text type) containing “&” and reference it in the formula.
- In CASE functions, ensure all return types match. For example, CASE($field_cCe5K__c$,”Tier 1 City”,60.5,”Tier 2 City”,49.0,0) is incorrect because the final return type (0 without decimals) doesn’t match. Use 0.0 instead.
2.1.2.4 Handling Null Values in Formulas
This setting only applies to numeric return types. Non-numeric return types always treat null values as null in calculations.
- Default to zero when field is null: For numeric, currency, or percentage return types, null values are treated as zero. Other field types remain null.
- Keep as null when field is null: For numeric, currency, or percentage return types, null values remain null.
2.1.2.5 Data Test Calculation
If test calculations show errors or no values, possible reasons include: 1. Formula fields contain null values without default zero or null checks. 2. Type conversion errors (text to number, text to date, etc.) due to incorrect formats. 3. Mismatches between default value/validation rule test results and actual submission results. Check if formula fields have changed, especially calculated/aggregated fields, as these changes aren’t recorded in modification logs.
2.1.3 AI-Powered Formula Generation
Calculated fields now support AI-generated formulas. Follow these steps:
-
When creating a calculated field, enter “Calculation Settings”.
-
Activate the AI Formula Assistant by typing “/” or clicking the “AI Formula Assistant” button.
-
Enter formula descriptions to generate formulas automatically.
-
Click “Copy” or “Apply” to use the AI-generated formula.
2.1.4 Important Notes
When saving field edits with over 10,000 records, a prompt appears: - Don’t calculate existing data: Historical data won’t be recalculated. - Calculate all (may take time for large datasets): Recalculates all existing data for the object, potentially causing delays (empty values temporarily). - Calculate only matching data: Configurable conditions determine which data gets recalculated. - For datasets ≤10,000 records, no prompt appears—fields save normally.
2.1.5 View Calculation Task Progress
After creating/editing calculated fields, a “Calculation Task Status” button appears in the list during execution. Clicking it shows total records, completed records, and estimated completion time.
2.1.6 Supported Functions for Formula Insertion
| Return Type | Operator/Function | Parameters | Function | Notes & Examples |
|---|---|---|---|---|
| Generic | () | Specifies operator precedence | ||
| Depends on input | IF(logical_test, value_if_true, value_if_false) | 3 params: 1st boolean, 2nd & 3rd same type | Returns value_if_true if condition is true, else value_if_false | Example: 34 = IF(true, 34, 52) |
| Depends on input | CASE(expression, value1, result1, value2, result2,…,else_result) | Variable params: results and else_result same type | Returns result matching expression value, else else_result | Example: 33 = CASE(3, 2, 2.0, 3, 33.0, 1.3) |
| Depends on input | NULLVALUE(expression, substitute_expression) | 2 params: 1st boolean, 2nd any type | Returns substitute if expression is null, else null | Example: 1 = NULLVALUE(Null, 1) |
| Numeric | +,-,*,/ | 2 numeric/percentage/currency params | Basic arithmetic | Example: 6 = (3+2)*6/5 |
| Numeric | - | 2 date params (days) | Date difference in days | Example: 24 = a - b (a=1982-04-13, b=1982-03-20) |
| Numeric | - | 2 datetime params (hours) | Datetime difference in hours | Example: 26 = a - b (a=1982-04-13 17:00:00, b=1982-04-12 15:00:00) |
| Numeric | - | 2 time params (hours) | Time difference in hours | Example: 2 = a - b (a=17:00:00, b=15:00:00) |
| Numeric | VALUE(string) | 1 text param | Converts numeric text to number | Example: -1982.0413 = VALUE(‘-1982.0413’) |
| Numeric | MIN(number1,number2) | 2 numeric params | Returns smaller value | Example: 4 = MIN(4,13) |
| Numeric | MAX(number1,number2) | 2 numeric params | Returns larger value | Example: 13 = MAX(4,13) |
| Numeric | MULTIPLE(number1,number2) | 2 numeric params | Multiplies numbers | Example: 52 = MULTIPLE(4,13) |
| Numeric | MOD(number1,number2) | 2 numeric params | Returns division remainder | Example: 3 = MOD(13,4) |
| Numeric | ADDS(number1,number2) | 2 numeric params | Adds numbers | Example: 17 = ADDS(13,4) |
| Numeric | SUBTRACTS(number1,number2) | 2 numeric params | Subtracts numbers | Example: 9 = SUBTRACTS(13,4) |
| Numeric | YEAR(date) | 1 date/datetime param | Extracts year | Example: 1982 = YEAR(‘1982-04-13’) |
| Numeric | MONTH(date) | 1 date/datetime param | Extracts month | Example: 4 = MONTH(‘1982-04-13’) |
| Numeric | DAY(date) | 1 date/datetime param | Extracts day | Example: 13 = DAY(‘1982-04-13’) |
| Numeric | LEN(text) | 1 text param | Returns string length | Example: 6 = LEN(‘xiaoke’) |
| Numeric | HOUR(datetime_or_time) | 1 datetime/time param | Extracts hour | Example: 14 = HOUR(“2025-07-31 14:30:45”) |
| Numeric | MINUTE(datetime_or_time) | 1 datetime/time param | Extracts minute | Example: 30 = MINUTE(“2025-07-31 14:30:45”) |
| Numeric | SECOND(datetime_or_time) | 1 datetime/time param | Extracts second | Example: 45 = SECOND(“2025-07-31 14:30:45”) |
| Numeric | MILLISECOND(datetime_or_time) | 1 datetime/time param | Extracts millisecond | Example: 0 = MILLISECOND(“2025-07-31 14:30:45”) |
| Numeric | UNIXTIMESTAMP(datetime_or_date_or_time) | 1 datetime/date/time param | Milliseconds since 1970-01-01 | Example: 1753943445000 = UNIXTIMESTAMP(“2025-07-31 14:30:45”) |
| Numeric | DAYOFYEAR(date) | 1 date param | Day of year (1-366) | Example: 1 = DAYOFYEAR(DATE(2025, 1, 1)) |
| Numeric | WEEKDAY(date) | 1 date param | Day of week (1=Monday) | Example: 1 = WEEKDAY(DATE(2025, 7, 28)) |
| Numeric | ISOYEAR(date) | 1 date param | ISO 8601 week-numbering year | Example: 2025 = ISOYEAR(DATE(2025, 7, 31)) |
| Numeric | ISOWEEK(date) | 1 date param | ISO week number (1-53) | Example: 21 = ISOWEEK(DATE(2024, 5, 20)) |
| Numeric | MAX(number,number,…) | Variable numeric params | Returns maximum value | Example: 120 = MAX(100, 80, 120) |
| Numeric | MIN(number,number,…) | Variable numeric params | Returns minimum value | Example: 80 = MIN(100, 80, 120) |
| Numeric | ROUND(number,num_digits) | 2 numeric params | Rounds to specified decimals | Example: 1.5 = ROUND(1.45, 1) |
| Numeric | ROUNDDOWN(number,num_digits) | 2 numeric params | Rounds down | Example: 1.4 = ROUNDDOWN(1.47, 1) |
| Numeric | ABS(number) | 1 numeric param | Absolute value | Example: 1.5 = ABS(-1.5) |
| Numeric | EXP(number) | 1 numeric param | e^number | Example: 2.718 = EXP(1) |
| Numeric | SQRT(number) | 1 numeric param | Square root | Example: 4 = SQRT(16) |
| Numeric | LN(number) | 1 numeric param | Natural logarithm | Example: 3.218 = LN(25) |
| Numeric | LOG(number) | 1 numeric param | Base-10 logarithm | Example: 2 = LOG(100) |
| Numeric | SIN(number) | 1 numeric param (radians) | Sine | Example: 0.5 = SIN(PI()/6) |
| Numeric | ASIN(number) | 1 numeric param (-1 to 1) | Arcsine | Example: π/2 = ASIN(1) |
| Numeric | COS(number) | 1 numeric param (radians) | Cosine | Example: 0.5 = COS(PI()/3) |
| Numeric | ACOS(number) | 1 numeric param (-1 to 1) | Arccosine | Example: π/3 = ACOS(0.5) |
| Numeric | TAN(number) | 1 numeric param (radians) | Tangent | Example: 1 = TAN(PI()/4) |
| Numeric | ATAN(number) | 1 numeric param | Arctangent | Example: π/4 = ATAN(1) |
| Numeric | ATAN2(y, x) | 2 numeric params | Arctangent of y/x | Example: π/4 = ATAN2(1, 1) |
| Numeric | PI() | None | Returns π | Example: 3.141592653589793 = PI() |
| Numeric | FIND(search_text, text [, start_num]) | Variable params | Returns search_text position | Example: 1 = FIND(“dog”, “dog days”) |
| Percentage | +,-,*,/ | 2 numeric/percentage/currency params | Arithmetic returning percentage | |
| Date intermediate | YEARS(number) | 1 numeric param | Year value for date operations | Example: TODAY()+YEARS(1) |
| Date intermediate | MONTHS(number) | 1 numeric param | Month value for date operations | Example: TODAY()+MONTHS(1) |
| Date intermediate | DAYS(number) | 1 numeric param | Day value for date operations | Example: TODAY()+DAYS(1) |
| Datetime intermediate | HOURS(number) | 1 numeric param | Hour value for datetime operations | Example: HOURS(4) |
| Datetime intermediate | MINUTES(number) | 1 numeric param | Minute value for datetime operations | Example: MINUTES(4) |
| Date | +,- | 1 date + date intermediate | Date arithmetic | Example: Creatdate+DAYS(4) |
| Date | DATE(year,month,day) | 3 numeric params | Creates date from numbers | Example: 1982-4-13 = DATE(1982,4,13) |
| Date | DATEVALUE(string) | 1 text param | Creates date from string | Example: 1982-4-13 = DATEVALUE(‘1982-04-13’) |
| Date | TODAY() | None | Current date | |
| Date | DATETIMETODATE(datetime) | 1 datetime param | Extracts date from datetime | Example: 1982-4-13 = DATETIMETODATE(‘1982-04-13 17: |