Consideration for Salesforce Formulae fields

[s7clarke10]

It is not a good practice to use a Salesforce Formulae field when ingesting data. Salesforce Formulae are calculated on the fly, they are not persisted. Therefore if you ingest a Salesforce formulae field, the data contained is not accurate / useful. Reason if the criteria behind the formula changes, it will not update the LastModified timestamp and so a change is not detected. The target will get out of sync.

What needs to be done for Formulae fields is the logic used in the Salesforce field needs to be reconstructed in the Target e.g. if it a simple sum of two fields then that logic needs to be reconstructed in the target.

This issue is raised to look whether the Salesforce Formulae fields could be reconstructed on the fly or perhaps as an alternative identify these fields via the Data Dictionary and provide an option to exclude these fields from be extract via a config setting.

[andyoneal]

Are formula fields that only include fields from that same object and do not use "dynamic" functions such as TODAY() in their formulas guaranteed to be accurate? An example might be a simple formula like FirstName & " " & LastName. Since any change to that value of the formula requires an edit to the record, shouldn't those always be up to date? It's appeared that way to me, but there might be some subtleties.

That kind of formula probably makes up a large percentage of formula fields people are using, and if we can assume they're up to date and an option to exclude formula fields is added, that exclusion could only apply to the formula fields we can't trust.

[s7clarke10]

Apologies, I didn’t explain the problem well. With Formula fields because they are dynamic in nature, they are not accurate for incremental changes. If the criteria behind the formula changes, it will not update the LastModified timestamp and so a change is not detected. The target will get out of sync.

FiveTran have a good write-up about this problem here : https://fivetran.com/docs/applications/salesforce/formula .

Summary, unless you do a full extract every time, a formula field cannot be trusted. For this reason FiveTran excludes them. I think therefore there should be a setting to automatically exclude Formula fields. Probably best that it is a setting as some people may choose to do a full extract every time.