Formula Fields

Formula fields in Salesforce are automatically populated fields whose values are derived from standard and custom fields using Salesforce Object Query Language (SOQL) formulas.

Each formula field has a definition, which is the formula itself, and a value. Each value depends on the field's formula which may involve complex relationships between Salesforce objects.

---

Sync limitations

Any value update happening on a Salesforce object causes an update of its , which specifies the date and time when the object was last modified.

However, formula fields are an exception: updates to the formula fields do not necessarily cause the object’s SystemModeStamp to update. Therefore, Fivetran’s incremental sync cannot reliably extract all the records affected by the formula field changes.

Salesforce explains this behavior in their :

... a formula field is a way of arriving at a value, but does not actually store the value and will not update the last modified timestamp on a record when it changes.

To maintain data integrity in your Salesforce connector, Fivetran doesn't sync these unreliable fields.

Example

To better understand how the sync of formula fields would (or wouldn’t) work, let’s look at an example.

Suppose we have a Lead object. Besides the regular fields, it includes two formula fields:

• A regular formula field with definition IsGreat = CONTAINS(Name, 'Great').
• A lookup field (a type of formula field that references a value from another object) with definition AccountName = SELECT Name FROM Account WHERE Id = Lead.AccountId.

Id	Name	LastModified	AccountId	IsGreat	AccountName
L1	Great lead!	2017-01-01	A1	TRUE	FooCorp
L2	Terrible lead : (	2017-01-02	NULL	FALSE	NULL

Let’s make changes to the formula fields in Salesforce:

• IsGreat is set to CONTAINS(Name, 'Amazing')
• AccountName in the Account object is changed from FooCorp to Foo Corp Private Limited

Changing the formula for the IsGreat field in Salesforce doesn’t change the SystemModStamp of the Lead object. Without it, Fivetran doesn't know that the value in the IsGreat column for L1 should be changed to FALSE.

Similarly, change to the Account.AccountName (AccountName field in the Account object) doesn't cause the change for Lead.AccountName (AccountName field in the Lead object).

As a result, after the formula changes in Salesforce, your destination table will have incorrect values:

Id	Name	LastModified	AccountId	IsGreat	AccountName
L1	Great lead!	2017-01-01	A1	TRUE	FooCorp
L2	Terrible lead : (	2017-01-02	NULL	FALSE	NULL
L3	Amazing lead!	2017-02-03	A3	TRUE	BarCorp

Because these two very common scenarios – change of the formula and change of the object the formula depends on – lead to incorrect data in the destination, Fivetran doesn't sync the formula fields.

TIP: We recommend using to work around these limitations.

---

Transformations solution

To assist with the replication of formula fields, Fivetran provides you with free system tables that translate the formulas from Salesforce SOQL into SQL. These tables are automatically updated with formula definitions on every sync.

Fivetran system tables:

• stores SQL translations for the formulas. Includes a full SQL SELECT statement which produces a model with all normal fields, plus all translatable formula fields
• includes source SOQL expressions for each formula field
• stores all the failure reasons why the formula can't be translated

To make syncing your Salesforce data easier, we have come up with a Salesforce Formula Utils dbt package. It helps you accurately map Salesforce formulas to existing tables, produce flexible data models that include formula fields, or automate model creation. Learn more in our .

NOTE: To ensure the data accuracy of the formula fields, use the latest version of the dbt package to create models.

---

Formula field translation limitations

We have the following limitations:

• We do not support fields that reference global variables.
• We do not support fields that reference columns or tables that are not synced by the user.
• We cannot read a few formula configurations and translate them. For example, blank field handling.
• We do not support the following functions:
  • CURRENCYRATE
  • DISTANCE
  • GEOLOCATION
  • GETRECORDIDS
  • GETSESSIONID
  • HTMLENCODE
  • IMAGE
  • IMAGEPROXYURL
  • INCLUDE
  • ISCHANGED
  • ISCLONE
  • ISNEW
  • JSENCODE
  • JSINHTMLENCODE
  • JUNCTIONIDLIST
  • LINKTO
  • PARENTGROUPVAL
  • PREVGROUPVAL
  • PREDICT
  • PRIORVALUE
  • REGEX
  • REQUIRESCRIPT
  • URLENCODE
  • URLFOR
  • VLOOKUP
• We support the following functions only for BigQuery, Databricks, PostgreSQL, Redshift, and Snowflake, destinations:
  • BEGINS
  • DATE
  • DATETIMEVALUE
  • DATEVALUE
  • FIND
  • ISNUMBER
  • LOG
  • MILLISECOND
  • MOD
  • NOW
  • SECOND
  • TEXT
  • TIMENOW
  • TIMEVALUE
  • TODAY
  • WEEKDAY

---

Identify formula field translation failure reasons

To identify why we can't translate the formula, see the FIVETRAN_FORMULA_FAILURE_REASON system table.

The following are the possible failure reasons:

• DEPENDENCY_FIELD_NOT_SYNCED: Formula refers to another field that you didn't select.
• GLOBAL_VARIABLE_NOT_SUPPORTED: Formula contains global variables.
• FUNCTION_NOT_SUPPORTED: Formula contains a function that we currently don't support.
• LOOKUP_FIELD_NOT_SYNCED: Formula refers to a field from a different table you didn't select.

You can find the details about each failure reason in the FAILURE_DETAIL_1 and FAILURE_DETAIL_2 columns:

Failure Reason	FAILURE_DETAIL_1	FAILURE_DETAIL_2
DEPENDENCY_FIELD_NOT_SYNCED	Dependent object name	Dependent field name
GLOBAL_VARIABLE_NOT_SUPPORTED	Global variable name	Global variable field
FUNCTION_NOT_SUPPORTED	Formula function name	Destination type
LOOKUP_FIELD_NOT_SYNCED	Object name with reference	Reference field