Skip to main content

Formula Fields

This is a collection of common formula expressions I've used in my Salesforce Data Cloud implementations.

caution

It is crucial to understand that formula fields are only evaluated at the time of data ingestion. This means that if you want to recalculate the formula fields for a Data Lake Object (DLO), you need to perform a full refresh of the Data Stream. This is a significant limitation of formula fields, when compared to data transforms. However, if properly architected, formula fields can be very powerful, as they do not consume extra credits (you're charged for data ingestion), whereas data transforms can be expensive.

Convert Strings to Booleans​

Example 1: Convert '1' or '0' to a boolean​

IF(sourceField['fieldName'] == '1', true, IF(sourceField['fieldName'] == '0', false, null))

This formula expression converts a string representation of '1' or '0' to a boolean value (true or false). Here's how it works:

  1. It first checks if the value in 'fieldName' is equal to '1'.
    • If true, it returns true.
  2. If the first condition is false, it then checks if the value is equal to '0'.
    • If true, it returns false.
  3. If neither condition is met (i.e., the value is neither '1' nor '0'), it returns null.

Example 2: Convert 'Y' or 'N' to a boolean​

IF(sourceField['fieldName'] == 'Y', true, IF(sourceField['fieldName'] == 'N', false, null))

This formula expression converts a string representation of 'Y' or 'N' to a boolean value (true or false). Here's how it works:

  1. It first checks if the value in 'fieldName' is equal to 'Y'.
    • If true, it returns true.
  2. If the first condition is false, it then checks if the value is equal to 'N'.
    • If true, it returns false.
  3. If neither condition is met (i.e., the value is neither 'Y' nor 'N'), it returns null.

Generic String Replacement with null​

Example 1: Replace 'Anonymous' with null​

IF(sourceField['fieldName'] != 'Anonymous', sourceField['fieldName'], null)

This formula expression replaces the string 'Anonymous' with an actual null value for the 'fieldName' field. Here's how it works:

  1. It first checks if the value in 'fieldName' is not equal to 'Anonymous'.
    • If true, it returns the original value.
  2. If the condition is false (i.e., the value is 'Anonymous'), it returns null.

Example 2: Replace 'Unknown' with null​

IF(sourceField['fieldName'] != 'Unknown', sourceField['fieldName'], null)

This formula expression replaces the string 'Unknown' with an actual null value for the 'fieldName' field. Here's how it works:

  1. It first checks if the value in 'fieldName' is not equal to 'Unknown'.
    • If true, it returns the original value.
  2. If the condition is false (i.e., the value is 'Unknown'), it returns null.

Example 3: Replace 'N/A' with null​

IF(sourceField['fieldName'] != 'N/A', sourceField['fieldName'], null)

This formula expression replaces the string 'N/A' with an actual null value for the 'fieldName' field. Here's how it works:

  1. It first checks if the value in 'fieldName' is not equal to 'N/A'.
    • If true, it returns the original value.
  2. If the condition is false (i.e., the value is 'N/A'), it returns null.

Convert Abbreviations to Full Names​

Example: Convert abbreviations to full names without extra DMO​

IF(sourceField['fieldName'] == 'ADV', 'Advertisement',
   IF(sourceField['fieldName'] == 'INS', 'In-store Promotion',
      IF(sourceField['fieldName'] == 'AS', 'Affiliate Sales',
         'Other')))
warning

This formula expression directly maps abbreviations to their full names without considering the potential for new abbreviations to be added over time. This approach requires constant updates to the formula as new abbreviations are introduced, which can lead to maintenance issues. A more ideal approach would be to address this at the source, ensuring that the data is correctly formatted before it reaches Data Cloud, or to implement a lookup DMO to handle the mapping dynamically.

This formula expression converts abbreviations to their full names without extra DMO. Here's how it works:

  1. It first checks if the value in 'fieldName' is equal to 'ADV'.
    • If true, it returns 'Advertisement'.
  2. If the first condition is false, it then checks if the value is equal to 'INS'.
    • If true, it returns 'In-store Promotion'.
  3. If the second condition is false, it then checks if the value is equal to 'AS'.
    • If true, it returns 'Affiliate Sales'.
  4. If none of the conditions are met (i.e., the value is none of 'ADV', 'INS', or 'AS'), it returns 'Other'.

Convert Strings to Title Case​

Example: Convert a generic field to Title Case​

PROPER(sourceField['fieldName'])

This formula expression converts a generic field to title case. Here's how it works:

  1. It first converts the first letter of the string to uppercase using the UPPER function.
  2. It then converts the rest of the string to lowercase using the LOWER function.
  3. Finally, it concatenates the two parts to form the title case string.

Generate a Created Date Field​

The NOW() function is used to produce a created Date field when not available in the data source. This is a requirement for engagement category data streams.

NOW()
note

It's important to distinguish between the NOW() and TODAY() functions. While TODAY() outputs the current date without time, NOW() is used in this context to capture the datetime value. Notably, NOW() outputs datetime values in UTC.