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.

Oct 30, 2023
Knowledge

Created Date

10/11/2026, 7:29 AM

Last Published Date

10/30/2023, 6:47 PM

Article

Formula Field Support

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

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.

Title

Formula Field Support

URL Name

Formula-Field-Support