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.

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
                }
//.....
Notes:

  • To include a field in the SOQL query of an object other than the one defined as the sourceObject, use the following syntax:
    [Object Name].[Field Name]. For example:

    {
    "alias": "accountRegion",
    "sourceObject": "Country__c",
    "sourceField": "Region__r.Name",
    "includeFields": [
        "BusinessAccount__c",
        "Account.Website"
    ]
}

    This will include both BusinessAccount__c in the query for Country__c and Website in the query for Account.

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. A list of sObject fields to include in the original query of the sourceObject (e.g., selecting a parent object in a relationship). The format for this definition is [sObject API Name].[Field Name], where sObject API Name is the optional name of the sObject to which you want to add the Field Name in its query string. Use this format when you want to include the field from a different sObject than the current sourceObject.

Field.valueSource (String) ⇧

Optional, Default: 'source'. Specifies whether the value for this field should be taken from the Data Source or Data Target.

  • source: Takes the value from the current data source (such as a Salesforce org or a CSV file).
  • target: Takes the value from the current data target. Note that the target value may not always be the original field value from the target Salesforce org, as it may have been modified by previous updates or data manipulation operations.

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. Note that:
    • In this case, it does NOT refer to the record on the data target side and typically refers to the source record, as it is used to update the target org.
    • The target field values are not always the original ones from the data source, as they may have already been altered previously, for example, by using Values Mapping or as a result of other data transformations.

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.

  • Fields defined as the sourceField, along with those specified in includeFields, are always included in the SOQL query for the sObject, just as if you were manually constructing the query.
    As a result, these fields may also be migrated to the Target org during the query process, like other fields. To prevent this and ensure these fields are only used for transformation purposes, consider using the excludedFromUpdateFields property, which prevents these fields from being uploaded to the Target org.

  • 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 8th Sep 2024