What does Clari currently support for formula fields?
Hot off the press — Formula Field Support article written by our very own expert
Formula Field Support
A formula field is a type of field in Salesforce that calculates a value based on an Excel-like expression. In your organization's Salesforce instance, formula fields might be used to perform calculations for specific metrics.
Example:
On the opportunity object, you might have 3 fields:
- TCV - currency
- Term Months - number
- ACV - currency (formula)
Both TCV and Term Months are used to calculate ACV with the formula:
(TCV / Term Months) * 12.
In this example, ACV is an essential metric for your company's reporting and would typically be added to various modules in Clari.
While we support adding formula fields in all product modules, it’s important to understand some of the nuances with formula fields and how Clari extracts the data from Salesforce.
How CRM Data is Extracted to RevDB
Clari’s RevDB snapshots your Salesforce data every 15 minutes.
Clari uses the lastModifiedDate field on each Salesforce object to determine if a record was updated. The lastModifiedDate only updates when the value on the record itself changes.
Changes in the calculated value of formula fields that rely on data from other records (for example, a lookup from Opportunity to Account) or on relative time-based values (e.g., “today”) won't necessarily correspond to a change in the lastModifiedDate of the record.
| Example | lastModifiedDate | Next Datahub Refresh | Result |
| Opportunity A | 1/1/2023 at 12:00pm PST | 1/1/2023 at 12:05pm PST | Will grab all field inputs for any fields added to Clari since the lastModifiedDate was updated on the record from the last refresh. |
| Opportunity B | 12/1/2022 at 12:00pm PST | 1/1/2023 at 12:05pm PST | Will not grab any field inputs for any fields added to Clari since the lastModifiedDate has not changed since 12/1/2022 PST. This can be true even if the calculated value of a formula field has changed (see above) |
Limitations of Formula Fields
- Formula fields are calculated at runtime (when the field is displayed), and these recalculations do not automatically trigger an update to the record in Salesforce (lastModifiedDate).
- If the lastModifiedDate on the record is not updated, Salesforce does not consider that an update has been made to the record—and so, Clari won't extract the record.
- This can sometimes cause Salesforce and Clari’s RevDB to disagree about a field's current value. 👯♂️
So, if a formula field in Clari depends on lookups on other objects or relative date/time functions, there's no way to guarantee that the current value shown for that field is accurate.
Exceptions
There are exceptions to the limits above. The best way is—make sure your lastModifiedDate field updates when the formula updates.
So using our TCV example from above, you'd make sure that lastModifiedDate updates when the TCV formula updates.
Here's how it works:
| Example | Field | Formula | Fields in Formula | Scenario | lastModifiedDate | Next Datahub Refresh | Result |
| Opportunity A | ACV | (TCV / Term Months)*12 | TCV = currency field from opportunity record Term Months = number field from custom object record | ACV formula field is updated with an update to the TCV field on the opportunity record on 1/1/2023 at 12:00pm PST. Updating TCV updates the lastModifiedDate on the opportunity record. | 1/1/2023 at 12:00pm PST | 1/1/2023 at 12:05pm PST | Will grab an update to the ACV field since there was a field updated on the opportunity record triggering an update to lastModifiedDate on 1/1/2023 (since the last refresh). |
| Opportunity B | ACV | (TCV / Term Months)*12 | TCV = currency field from opportunity record Term Months = number field from a related record | ACV formula field is updated with an update to Term Months which is on a custom object on 1/1/2023 at 12:00pm PST. Updating Term Months updates the custom object record outside the opportunity record but does not change the lastModifiedDate on the opportunity record. | 12/1/2022 at 12:00pm PST | 1/1/2023 at 12:05pm PST | Will not grab an update to the ACV field since no field was updated on the opportunity record triggering an update to lastModifiedDate since 12/1/2022. |
Solution
If you have a formula field that might have some of those limitations outlined above:
1. Create a new field on the object where the formula field exists.
2. Build a workflow that populates the value in the formula field to the new field.
The benefit of this solution is that it updates the lastModifiedDate since the new field isn't a formula field.
Also, the new field can be history tracked since it’s not a formula field. This would allow for the color highlighting to display in the opportunity grid—giving you visibility to any changes to that field.
Again, with that same example: 👓
| Example | Field | Formula | Fields in Formula | Scenario | lastModifiedDate | Next Datahub Refresh | Result |
| Opportunity B | ACV | (TCV / Term Months)*12 | TCV = currency field from opportunity record Term Months = number field from a related record | ACV formula field is updated with an update to Term Months which is on a custom object on 1/1/2023 at 12:00pm PST. Updating Term Months updates the custom object record outside of the opportunity record but does not change the lastModifiedDate on the opportunity record. | 12/1/2022 at 12:00pm PST | 1/1/2023 at 12:05pm PST | Will grab an update to the ACV & ACV_2 fields since there was a field updated on the opportunity record triggering an update to lastModifiedDate on 1/1/2023 (since the last refresh). |
| Opportunity B | ACV_2 | None | None | Since the formula field (ACV) has been updated, the workflow is triggered to populate the current value for ACV in ACV_2 on 1/1/2023 at 12:00pm PST. Since this field is not a formula field, the lastModifiedDate on the opportunity record is updated. |
