Formula Fieldslink
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 limitationslink
Any value update happening on a Salesforce object causes an update of its SystemModStamp, 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 SystemModStamp 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 documentation:
... 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.
Examplelink
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 toCONTAINS(Name, 'Amazing')
AccountName
in theAccount
object is changed fromFooCorp
toFoo 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 our Transformations solution to work around these limitations.
Transformations solutionlink
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:
FIVETRAN_FORMULA_MODEL
stores SQL translations for the formulas. Includes a full SQLSELECT
statement which produces a model with all normal fields, plus all translatable formula fieldsFIVETRAN_FORMULA
includes source SOQL expressions for each formula fieldFIVETRAN_FORMULA_FAILURE_REASON
stores all the failure reasons why the formula can't be translated
Salesforce Formula Utils dbt packagelink
To make syncing your Salesforce data easier, we have come up with a Fivetran 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 Salesforce Formula Utils model documentation.
NOTE: To ensure the data accuracy of the formula fields, use the latest version of the dbt package to create models.
Formula field translation limitationslink
We have the following limitations:
- 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 and operators only for BigQuery, Databricks, PostgreSQL, Redshift, and Snowflake destinations:
- ADDMONTHS
- BEGINS
- BR
- CHR
- DATE
- DATETIMEVALUE
- DATEVALUE
- DAYOFYEAR
- FIND
- FORMATDURATION
- FROMUNIXTIME
- ISOWEEK
- ISOYEAR
- ISNUMBER
- LOG
- MILLISECOND
- MOD
- NOW
- PI
- ROUND
- TEXT
- TIMENOW
- TIMEVALUE
- TODAY
- TRUNC
- UNIXTIMESTAMP
- VALUE
- WEEKDAY
- + OPERATOR
- - OPERATOR
- / OPERATOR
Identify formula field translation failure reasonslink
If a formula field cannot be translated, your transformation model will include a column for this field and populate it with null
values. 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 a global variable that we currently don't support.
- FUNCTION_NOT_SUPPORTED: Formula contains a function that we currently don't support.
- OPERATOR_NOT_SUPPORTED: Formula contains an operator that we currently don't support.
- LOOKUP_FIELD_NOT_SYNCED: Formula refers to a field from a different table you didn't select.
- ROLLUP_SUMMARY_FILTER_CANT_BE_PARSED: Roll-up Summary field contains filter value that we can't parse.
- ROLLUP_SUMMARY_DEFINITION_CANT_BE_FETCHED: Unable to retrieve the roll-up summary definition. Salesforce doesn't provide an API to fetch metadata for standard roll-up summary fields, so we can't support them.
- UNKNOWN: Faced an unexpected exception while translating the formula.
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 |
OPERATOR_NOT_SUPPORTED | Operator name | Destination type |
LOOKUP_FIELD_NOT_SYNCED | Object name with reference | Reference field |
ROLLUP_SUMMARY_FILTER_CANT_BE_PARSED | Child object name along with filter field | Filter field value |
ROLLUP_SUMMARY_DEFINITION_CANT_BE_FETCHED | Roll-up summary field name | |
UNKNOWN | Unknown error occurred. Contact support |