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.

Fivetran recommends that you use one of the following transformation-based approaches to sync the formula fields:

• Enable Quickstart data models for Salesforce
• Use the Salesforce formula utils dbt package to sync the formula fields as part of your own dbt project

You can also configure your connector to sync the formula fields directly using our REST API. However, we do not recommend this method for the reasons outlined in the Sync limitations section.

Sync limitations

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.

NOTE: You can sync these formula fields directly using our REST API.

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.

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:

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 solution

To assist with the replication of formula fields, Fivetran translates Salesforce formulas into SQL and creates corresponding data models for each Salesforce object with formula fields. Our transformation packages then create distinct views in your destination for each formula model. Fivetran updates these views according to your transformation schedule settings.

Fivetran system tables:

• FIVETRAN_FORMULA table stores the formulas defined in Salesforce, which we use to translate formula fields.

• FIVETRAN_FORMULA_FAILURE_REASON table stores all the reasons why Fivetran could not translate a particular formula field.

• FIVETRAN_FORMULA_MODEL table stores SQL view models for the objects with translated formula fields. For each object with formula fields, it contains a full SQL SELECT statement. This statement produces a model with all standard fields plus all translatable formula fields. We use the SQL view models to create views in your destination.

These system tables are updated with every connector sync and count towards free Monthly Active Rows (MAR).

Salesforce quickstart model

The Quickstart data model simplifies the process of syncing and replicating formula fields so that they are ready for use immediately after the initial sync is completed. For more information, see our Formula Fields - Salesforce Quickstart Model documentation.

Salesforce Formula Utils dbt package

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 limitations

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:

  • 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
  • INCLUDES
  • ISOWEEK
  • ISOYEAR
  • ISNUMBER
  • LEFT
  • LOG
  • MILLISECOND
  • MOD
  • NOW
  • PI
  • RIGHT
  • ROUND
  • TEXT
  • TIMENOW
  • TIMEVALUE
  • TODAY
  • TRUNC
  • UNIXTIMESTAMP
  • VALUE
  • WEEKDAY
  • + OPERATOR
  • - OPERATOR
  • / OPERATOR

• Handling formula fields with hardcoded IDs: We cannot translate any formula fields containing hardcoded 15-character IDs, as we store 18-character IDs in the destination. To avoid this issue, it is recommended to use the CASESAFEID formula function instead. For example, if your formula includes expressions such as, IF(ID = '003Hs00004aSdJo', 'Yes', 'No'), this will work in Salesforce but fail in translation because we store the ID field in its 18-digit form (003Hs00004aSdJoIAK) in the destination. To fix this, update the formula to use the CASESAFEID function, which converts a 15-digit ID to an 18-digit ID: IF(CASESAFEID(ID) = '003Hs00004aSdJoIAK', 'Yes', 'No'). Ensure the hardcoded value is updated to the 18-character ID to support both Salesforce and translation environments.

• Handling DATE functions in PostgreSQL destination: When using functions involving DATE as arguments, ensure the resulting value is a valid date. If the date is invalid, the model may fail with a date/time field value out of range error. While other destinations, like BigQuery, handle this with safe casting (translating invalid dates to NULL), this issue remains in Postgres.

• Formula fields with Number return type: For formula fields with a Number return type, we do not round out the final result according to the configured scale value. This is an intentional decision to prioritize accuracy over automatic rounding.

• Formula fields referring to compound fields: Formula fields that refer to compound fields, for example, BillingAddress in the ACCOUNT table, are not translated directly and can fail with the error DEPENDENCY_FIELD_NOT_SYNCED due to how they are synced in the destination. To resolve this, modify the formula to reference individual sub-fields, such as BillingCountry or BillingCity, instead of the entire compound field.

Identify formula field translation failure reasons

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. Contact our support team to request support for the standard roll-up summary fields that you want to sync. We will verify if we can generate their definition, as Salesforce does not provide an API to fetch metadata for standard roll-up summary fields.
• TRANSLATED_SQL_TOO_LONG: Length of the generated model is greater than the maximum column size allowed for your destination. Use the QuickStart data model to translate this object.
• 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: