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



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:

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 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.

Property name Is mandatory Property type Description
fields Yes Array of Field objects List of the data sources to transform.
Field.alias Yes string Variable name (similar to js variable), allowing to include this Field into the transformation expression.
Field.sourceObject Yes string SObject used as the data source for the transformation.
Field.sourceField Yes string Field belonging to the sourceObject which is used as the value source for the transformation.
Field.includeFields No Array of strings List of fields to be added into the original query string of the child object in a relationship.

➦ Every field (including lookups and even referencing fields like Account.Name) can be added.
Field.lookupExpression No string,
Any Js expression
To create a customized lookup search you can use the lookupExpression instead of using the includeFields on the child object in a relationship.

➦ It's a JS expression to selects a parent record from the whole set of the records like the regular lookup does.
lookupExpression is running for every sourceObject's or targetObject's record (depend on the lookupSource setting) and returning true if a correct record has found.
➦ This expression received 2 parameters, the current source and target records.
Field.lookupSource No string,
Acceptable values:
'source' or 'target'
Defines whether the lookupExpression should be run in loop over the source or the target records.

source: it's iterates over all source records (the object which, used as a source for the transformation, i.e. Country__c) . In this case the expression finally selects the source record, which is used in a parent-child record binding.
target: it's iterates over all target records (the object, which is updated during the transformation, i.e. Account) . In this case the expression finally selects the target record, which is used in a parent-child record binding.
Field. isConstant No boolean, fefault false Setting this property to true enforces calculation of the lookupExpression for this Field only once per job (so it becomes like a cached value) and a first successfull result is used in all transformations.
transformations Yes Array of Transformation objects List of the fields to update.
Transformation.targetObject Yes string sObject to update.
Transformation.targetField Yes string targetObject's field to update.
Transformation.formula Yes string Final Js expression, which is running for each target record to update the targetField.
Transformation.expressions No Array of strings Set of JS expressions, all of them are evaluated before the main Transformation.formula expression runs.

➦ This feature allows you to extend the formula object with a number of extra properties which help you in building the final formula expression.

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.

Last updated on 29th Jan 2023