Data Transformation Tables

Foundations data transformation tables enable you to transform data received in a message so that it is appropriate for the target field in the subscribing application. You can create data transformation tables that contain one or more data transformations. Each data transformation contains one or two source values and the target value.

When you create a mapping on a messaging relationship, you can optionally specify a data transformation table to apply. For example, you may receive messages via a subscription that contain information about people and their job roles. If a message contains information regarding an architect with one year's experience, you can create a data transformation table to convert this into a specific job role of Junior Architect.

Source Value 1 Source Value 2 Target Value
Architect 1 year Junior Architect

In this second example, a senior business analyst is transformed to a business analyst. The optional source value 2 is not used.

Source Value 1 Source Value 2 Target Value
Senior Business Analyst   Business Analyst
Note:

If a data transformation table contains at least one data transformation that requires two source values, the following rules apply:

  • You must provide an additional transformation source key when creating a mapping.
  • Values sent in the message for the additional key are compared against the values for source value 2. Target data is only transformed if the values sent for the nominated keys match both specified source values.
  • When no source value 2 is specified for a transformation, the corresponding additional key on the message must also be blank to transform the data.

The following table provides examples of mappings that include two sources for data transformation.

Source Value 1 Source Value 2

Matching Value for Source Value 2 on Data Transformation

Data Transformed

Actual Target Value
Developer 1 year Yes Yes Junior Software Developer
Developer   Yes (null) Yes Software Developer
Senior Business Analyst   Yes (null) Yes Business Analyst
Senior Business Analyst   No No Senior Business Analyst
Notes:

Each data transformation must contain a unique combination of source values.

Source values are not case sensitive.

When creating data transformations for lookups, we recommend that you enter the Salesforce ID of the item to be included instead of text. This reduces the load on Foundations and increases the performance of your integrations.