RecordsTransform Core Add-On Module.

This module allows you to apply custom transformation and mapping to the source records before they are uploaded to the Target.
It gives you the additional great benefits over the existing Values Mapping feature.

Table of Contents

• Sample of record transformation.
• Requirements.
• Solution using the Core RecordsTransform Add-On module.
  • Step 1 Define the source fields.
  • Step 2 Setup lookup relationships.
  • Step 3 Define the target fields and transformation expressions.
• The full specification of the args properties.
• Additional information about this Add-On module.

As a great benefit of this Add-On from the Values Mapping, that it allows you to update fields using different multiple sObject as a data source.

In addition, you can apply complex transformations using customized expressions.

The terminology of this article is:

• "source" - is the SObject, field or record, from which the values are taken for the transformation.

• "target" - is the SObject, field or record which are being updated as a result of the transformation process.

Sample of record transformation. ⇧

Let's see the example of the configuration below:

"objects": [
    {
        "operation": "Upsert",
        "externalId": "Name",
        "query": "SELECT Name 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"
                }
            ]
        }
    }
]

The same result can be achieved using the Lookup Expression option:

"objects": [
    {
        "operation": "Upsert",
        "externalId": "Name",
        "query": "SELECT Name 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",
                    "lookupExpression": "source.BusinessAccount__c == target.Id",
                    "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"
                }
            ]
        }
    }
]

Our object model is below:

Requirements. ⇧

Our challenge is to take the values from different source fields, perform with them amount of calculations, then populate 2 target account fields with the results.

The required transformations are:

• Account.CategoryName__c = Account.Category__r.Name

• Account.CategoryAndRegionName__c = Account.Category__r.Name + ' (' + Country__c.Region__r.Name + ')'

Solution using the Core RecordsTransform Add-On module. ⇧

Step 1 Define the source fields. ⇧

We define 2 source fields (of Field object type) with following aliases:

• accountCategory (the source value will be taken from Account.Category__r.Name)
• accountRegion (the source value will be taken from Country__c.Region__r.Name).

But how the Add-On can locate the Country record?

Thanks to the BusinessAccount__c lookup field, it simply searches for the first child Country referencing the current target Account, then uses its values for the transformation. Even for many-to-one relationships the first child object is selected.

Step 2 Setup lookup relationships. ⇧

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

The usual way to include BusinessAccount__c field in the query string of Country__c is by using the includeFields property in the accountRegion definiciton, allowing the Add-On to bind between Account and Country:

 "includeFields": [
    "BusinessAccount__c"
 ]

This will add Country__c into the configuration and then add the BusinessAccount__c lookup into the query string of Country__c.

The advantage of adding extra fields using the includeFields property, that you don't have to include in your export.json all readonly objects, which are used only by the Add-On and nowhere else

But if you still prefer to add Country__c in the export.json you can omit the includeFields section. Make sure you included BusinessAccount__c in the query string of Country__c:

{
    "operation": "Readonly",
    "externalId": "Name",
    "query": "SELECT Name, BusinessAccount__c FROM Country__c"
}

Notes:

• The Add-On understands by itself which SObject (Account or Country) is the parent and which is the child and you don't need to care about their relationship.

Step 3 Define the target fields and transformation expressions. ⇧

We define the "transformations" array.

Here we set two Account fields to be updated after the transformation is done: CategoryName__c and CategoryAndRegionName__c.

Each Transformation object contains following properties:

• "formula" - is the mandatory expression, which value is used to update the target field. Here you can use the source aliases like formula.[ALIAS], i.e. formula.accountCategory . The formula here is the temporary JS object defined in the current target record's scope.

• "expressions" - is the optional list of expressions which are executed for each target record, before executing the formula. You can perform here any additional operations and even extend the formula object with extra properties, when you require them in the main formula expression.

The full specification of the args properties. ⇧

Additional information about this Add-On module. ⇧

• The sourceField property should always be set to the field which can be queryable using the SOQL on the source object. For example if you define Category__r.Name for the Account, so the Add-On will internally create the query like: SELECT Id, Category__r.Name, ...... FROM Account ..... etc.

• The lookup fields listed in the includeFields are always included in the sobject's SOQL in the same way, as if you were doing it manually.

  The consequences could be, that sometimes you might get these lookup fields also migrated to the Target org like it's done for other fields in a query . To avoid this behavior and to use these fields only for the transformation purposes, consider using the excudedFromUpdateFields property which prevents fields from being uploaded to the Target.

• You have 2 options to include this Add-On in the export.json:

  • As part of object definition (onBeforeUpdate event). In that case the Add-On will transform the final version of the records, just before they uploaded to the target org.
  • Globally (onDataRetrieved event). Here it is not the final version of the records which are used ton update the target, i.e. the lookup fields are not populated yet.

• This Add-On runs AFTER running the Values Mapping.