RecordsTransform Core Add-On Module.

Table of Contents



Overview ⇧

The core:RecordsTransform module provides advanced functionality for applying custom transformations and mappings to source records before they are uploaded to the target system. It significantly enhances capabilities beyond the Values Mapping feature by allowing updates to fields using multiple different sObjects as data sources. This enables complex transformations that utilize customized expressions to fine-tune data according to specific needs.

This article clarifies terminology used in discussing the module’s functions:

  • "source" refers to the SObject, field, or record from which values are derived for transformation.
  • "target" denotes the SObject, field, or record that is updated as a result of the transformation process.

Sample of Record Transformation ⇧

Configuration File ⇧ 

{
    "objects": [
        {
            "operation": "Upsert",
            "externalId": "Name",
            "query": "SELECT Id FROM Account"
        }
    ],
    "dataRetrievedAddons" : [
        {
            "module" : "core:RecordsTransform",
            "args": {
                "fields": [
                    {
                        "alias": "accountCategory",
                        "sourceObject": "Account",
                        "sourceField": "Category__r.Name"
                    },
                    {
                        "alias": "accountRegion",
                        "sourceObject": "Country__c",
                        "sourceField": "Region__r.Name",
                        "includeFields": [
                            "BusinessAccount__c"
                        ]
                    }
                ],
                "transformations": [
                    {
                        "targetObject": "Account",
                        "targetField": "CategoryName__c",
                        "formula": "formula.accountCategory"
                    },
                    {
                        "targetObject": "Account",
                        "targetField": "CategoryAndRegionName__c",
                        "expressions": [
                            "formula.exists = formula.accountCategory && formula.accountRegion"
                        ],
                        "formula": "formula.exists ? formula.accountCategory + ' (' + formula.accountRegion + ')' : null"
                    }
                ]
            }
        }
    ]
}

Object Model ⇧

sObject Field (Field type)
Account Name (Account name field)
Account Category__c (lookup to Category__c)
Account CategoryName__c (Picklist)
Account CategoryAndRegionName__c (String)
Country__c Name (Country name field)
Country__c BusinessAccount__c (lookup to Account)
Country__c Region__c (lookup to Region__c)
Region__c Name (Region name field)

Requirements ⇧

Our task involves extracting values from various source fields, conducting a series of calculations, and subsequently updating two target account fields with the outcomes.

The transformations needed are as follows:

  • Account.CategoryName__c should be set to the value of Account.Category__r.Name.
  • Account.CategoryAndRegionName__c should combine the values of Account.Category__r.Name and Country__c.Region__r.Name, formatted as Account.Category__r.Name + ' (' + Country__c.Region__r.Name + ')'.

Solution ⇧

Step 1: Define the Source Fields ⇧

We specify two source fields (categorized under the Field object type) with the designated aliases:

  • accountCategory: This sources its value from Account.Category__r.Name.
  • accountRegion: This retrieves its value from Country__c.Region__r.Name.

But, how does the Add-On pinpoint the Country record to evaluate Region__r.Name for it?

With the BusinessAccount__c lookup field at its disposal, it efficiently locates the child Country that references the Account, leveraging its data for the transformation.

Step 2 Setup lookup relationships. ⇧

As mentioned above, to select the child Country record by its parent Account, we use the Country__c.BusinessAccount__c as a parent-child lookup field. Therefore, BusinessAccount__c should be included in the query string of Country__c.

The method for including the BusinessAccount__c field in the query string of Country__c is by utilizing the includeFields property in the accountRegion definition. This allows the Add-On to link between Account and Country:

{
  "includeFields": [
    "BusinessAccount__c"
  ]
}

This configuration incorporates the BusinessAccount__c lookup into the query string of Country__c as it's defined as the source sObject for this Field.

The Add-On automatically determines which SObject (Account or Country) is the parent and which is the child, so you don't need to manage their relationship details.

The benefit of using the includeFields property to add extra fields is that it eliminates the need to include extra Readonly object definitions in your export.json which is used only in the export:RecordTransform module and nowhere else.

However, if you prefer to directly add Country__c.BusinessAccount__c to the export.json, the includeFields array can be omitted, see the example below: 

{
    "operation": "Readonly",
    "externalId": "Name",
    "query": "SELECT BusinessAccount__c FROM Country__c"
}
//..... 
                {
                    "alias": "accountRegion",
                    "sourceObject": "Country__c",
                    "sourceField": "Region__r.Name"                    
                    // The "includeFields" definition is omitted here, 
                    //  since it's explicitely included in the query string of the Country__c
                }
//.....

Step 3 Define the Target Fields and Transformation Expressions ⇧

In the transformations array, specify two Transformation objects containing logic to update two fields of the Account: CategoryName__c and CategoryAndRegionName__c.

Each Transformation object includes:

  • formula: A mandatory expression used to update the target field. Source aliases like formula.[ALIAS], e.g., formula.accountCategory, are used within this expression. Formula is a temporary JavaScript object defined within the current target record's scope.

  • expressions: An optional list of expressions executed for each target record before the main formula. This augments the formula object with additional properties needed for the primary formula expression. For example, formula.exists = formula.accountCategory && formula.accountRegion creates a new property exists based on a JavaScript expression using field values via their aliases. The formula.exists value is then used in the final formula expression to compute and fill the CategoryAndRegionName__c field.

Full Specification of args Properties ⇧

fields (Array of Field objects) ⇧

Mandatory. List of the data sources to transform.

Field.alias (String) ⇧

Mandatory. Variable name (akin to JavaScript variable naming), allows for the inclusion of this Field in the transformation expression.

Field.sourceObject (String) ⇧

Mandatory. SObject used as the data source for the transformation.

Field.sourceField (String) ⇧

Mandatory. Field belonging to the sourceObject which is used as the value source for the transformation.

Field.includeFields (Array of strings) ⇧

Optional. List of sObject fields to be incorporated into the original query string of the sourceObject to select the parent object within a relationship.

Field.lookupSource (String) ⇧

Optional, Default: 'source'. Defines whether the lookupExpression should be run in a loop over the records from the Data Source or Data Target.

  • source: It iterates over all data source records. In this case, the given field finally selects and evaluates the record from the Data Source.
  • target: It iterates over all data target records. In this case, the given field finally selects and evaluates the record from the Data Target.

Field.isConstant (Boolean) ⇧

Optional, Default: false. Setting this property to true ensures that the formula expression for this Field.alias (for example formula.accountCategory) is calculated only once per job, functioning similarly to a cached value for each expression in the Transformation.expressions for all rest records use this cached formula value. So, the first non-null (non-empty string) result is then utilized in all subsequent transformations.

Field.lookupExpression (String) ⇧

Optional. If you prefer a customized search for evaluating the current Field value instead of using a regular lookup, you can use the lookupExpression feature.

Unlike finding records through regular lookup relationships, lookupExpression enables dynamic comparison of source or target records against the record currently being modified as a result of the transformation.

lookupExpression is a JavaScript expression that uses two constants, source and target:

  • source refers to the record from either the Data Source or Data Target, depending on the lookupSource setting.
  • target consistently represents the record intended for transformation.

For example, if there is no BusinessAccount__c lookup field to directly link Country__c to Account, but Country__c includes fields like BusinessAccountFirstName__c and BusinessAccountLastName__c, these can be used to locate a linked account record.

Here’s an example of how you might configure this in your export.json:

{
    "objects": [
        {
            "operation": "Upsert",
            "externalId": "Name",
            "query": "SELECT Id FROM Account" 
        }
    ],
    "dataRetrievedAddons" : [
        {
            "module" : "core:RecordsTransform",
            "args": {
                "fields": [
                    {
                        "alias": "accountCategory",
                        "sourceObject": "Account",
                        "sourceField": "Category__r.Name",
                        "includeFields": [
                            // Need to include these fields into query to Account, since they are used 
                            // in the lookupExpression of the 'accountRegion' Field for the `target` Account record.
                            "FirstName",
                            "LastName"
                        ]
                    },
                    {
                        "alias": "accountRegion",
                        "sourceObject": "Country__c",
                        "sourceField": "Region__r.Name",
                        "lookupExpression": "source.BusinessAccountFirstName__c == target.FirstName && source.BusinessAccountLastName__c == target.LastName",
                        "includeFields": [
                            // Need to include these fields into query to Country__c, since they are used 
                            // in the lookupExpression of this Field for the `source` Country__c record.
                            "BusinessAccountFirstName__c",
                            "BusinessAccountLastName__c"
                        ]
                    }
                ],
                "transformations": [
                    {
                        "targetObject": "Account",
                        "targetField": "CategoryName__c",
                        "formula": "formula.accountCategory"
                    },
                    {
                        "targetObject": "Account",
                        "targetField": "CategoryAndRegionName__c",
                        "expressions": [
                            "formula.exists = formula.accountCategory && formula.accountRegion"
                        ],
                        "formula": "formula.exists ? formula.accountCategory + ' (' + formula.accountRegion + ')' : null"
                    }
                ]
            }
        }
    ]
}

transformations (Array of Transformation objects) ⇧

Mandatory. List of the sObject fields to update, including expressions to transform them.

Transformation.targetObject (String) ⇧

Mandatory. sObject to update.

Transformation.targetField (String) ⇧

Mandatory. Transformation.targetObject's field to update.

Transformation.expressions (Array of strings) ⇧

Optional. A collection of JavaScript expressions, all evaluated before the main Transformation.formula expression is executed. This feature enables you to augment the formula object with additional properties, assisting in the construction of the final formula expression.

Transformation.formula (String) ⇧

Mandatory. Final JavaScript expression, which is running for each target record to update the targetField.

Additional Notes ⇧

  • The object defined as a targetObject in a Transformation item must be listed in the export.json for editing, either with Insert, Update, or another operation within objects or objectSets. Conversely, a sourceObject can be set with a Readonly operation.

  • The sourceField property must be set to a field that is queryable using SOQL on the source object. For instance, if you define Category__r.Name for the Account, the Add-On internally generates a query similar to: SELECT Id, Category__r.Name FROM Account, etc.

  • Lookup fields specified in the includeFields are always included in the SOQL query for the sObject, just as if you were constructing the query manually. As a result, these lookup fields may also be migrated to the Target org as part of the query process, similar to other fields. To prevent this and ensure these fields are only used for transformation purposes, consider utilizing the excludedFromUpdateFields property, which prevents fields from being uploaded to the Target.

  • This Add-On executes after the Values Mapping process has been completed.

  • There are two options for incorporating this Add-On into your export.json:

    • Object OnBeforeUpdate Event In this scenario, the Add-On transforms the final version of the records just before they are uploaded to the target org including lookup fields.

    • Global OnDataRetrieved Event. In this case, it operates on not the final version of the records, meaning the lookup fields are not yet populated.

    For additional details, see also Supported Add-On Api Events

  • The Transformations are executed in the order in which they appear in the transformations array, ensuring that modifications to the target records are applied sequentially.

Last updated on 11th May 2024