The core:RecordsTransform 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 Value Mapping feature. *

The module is based on the Core SFDMU Add-On Api.


The example of module setup.

In order to modify records by using the RecordsTransform Core Add-On Module, you should declare the module call in your export.json.

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",
                    "includeLookupFields": [
                        "Country__c.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 object model is below:

sObject Field (Field type)
Account Name
Account Category__c (lookup to Category__c)
Account CategoryName__c (Picklist)
Account CategoryAndRegionName__c (Picklist)
Country__c Name
Country__c BusinessAccount__c (lookup to Account)
Country__c Region__c (lookup to Region__c)
Region__c Name

We have to populate 2 target account fields from several source fields.

The required transformations are:

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

The main challenge of this task is that since the Account is the parent object of the Country (the lookup field is Country__c.BusinessAccount__c), so we can NOT directly access the Country object from the Account to populate the Account field from the Region of the Country. But the Add-On can successfully resolve the relationship between these objects, you only heave to specify the lookup field of the Country__c which is referencing the parent Account and this can be done by including Country__c.BusinessAccount__c into the list of the used lookups: includeLookupFields (see the full specification below).

The Add-On configuration of above will make the necessary transformation with then source records. The best time to run the Add-On is on the OnDataRetrieved event (see the details here) because this event is fired AFTER all source and target data are present and can be modified before they are getting uploaded to the Target env. Of course any Add-On can be also triggered on any other event, but this is the best event to use for our specific purposes.

The full specification of the args properties.

Property name Is mandatory Property type Description
fields Yes Array of Field objects The list of the source fields to use in the transformation expressions (see the transformations property).
Field.alias Yes string For each field, it's the name of the variable to replace the original sourceField value in the transformation expressions. It's quite similar to JavaScript variable which is being assigned from the field value.
For example: accountCategory
In the provided example the assignment of the variable will be: *accountCategory = *Category__r.Name
Field.sourceObject Yes string Sets the type of the source sobject used to populate the target records.
For example: Account.
Field.sourceField Yes string Sets the name of the source sobject's field. You can also include deep reference fields.
For example: Category__r.Name.
In this example the full field path used by the Add-On is: Account.Category__r.Name
Field.includeLookupFields No Array of strings Use this property to setup the relationships between the objects.
In this example we should access the Region record related to the Country having the currently processed Account record as its parent. There is a child-parent relationship between the Country__c and the Account objects. So we should include the lookup Country__c.BusinessAccount__c in the array values, letting the Add-On understand how to bind these objects together.

The format of each array member is: [ObjectName].[Lookup Field on this Object].
If you omit the ObjectName leaving only the field, the Add-On will use the current value of Field.sourceObject as the ObjectName.
transformations Yes Array of Transformation objects The list of the target fields to transform including the transformation parameters for each field (see the details below).
Transformation.targetObject Yes string Sets the type of the target sobject to transform.
For example: Account.
The source and the target sobjects can be different as well as the same. If they are different, you probably will have to use the includeLookupFields to define the relationships between the objects (see above).
Transformation.targetField Yes string Sets the target sobject's field to transform. The allowed value is any updatable field of the target sobject.
For example: CategoryAndRegionName__c
Transformation.formula Yes string Sets the expression for the transformation. The specified Transformation.targetField will be populated after evaluating the given expression against the set of the source fields included in the fields property (see above).
For example: formula.accountCategory + ' (' + formula.accountRegion + ')'

Any JS expression is valid here.
The important thing is in case that you want to reference the field from the source record using the field 'alias', you should always prefix the alias with formula. (e.g. formula.accountCategory for the accountCategory alias). This is because the Add-On works internally with the JS object called formula for the expression evaluating.
Transformation.expressions No Array of strings You can include here the set of extra JS expressions to evaluate BEFORE the main Transformation.formula expression runs.
It will allow you to assign a set of helper varaibles to use in the formula expression.
In our example we are creating the new property formula.exists on the formula object and we will use it in the formula further.
Note, that the best practice here is not to create a completely new JS varable using "const myVar = formula.field1 + formula.field2", but to work with the same formula object, like: "formula.myVar = formula.field1 + formula.field2". The reason is because by creating a new variable you may conflict with the existing Add-On code and it's better to avoid such a collisions.

The additional information and considerations:

  • The sourceField property should always set to the field which can be queried 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 includeLookupFields are always included in the sobject's SOQL at the same way as if you were doing it manually. For example, if you are putting there "Country__c.BusinessAccount__c", the Add-On will first make a check if the Country__c object is already exists in the export.json, if so, it adds the field BusinessAccount__c into the existing SOQL, but if not, it will automatically create and add a new configuration for the Country__c using READONLY operation. The BusinessAccount__c will be included in the query string.

    The consequences could be that sometimes you might get these lookup fields also migrated to the Target using the normal Plugin operation flow, like it's done for other source fields . To avoid it and to use these fields only for the transformation purposes, consider using the excudedFromUpdateFields property which prevents fields from being uploaded to the Target.

  • After the transformation is done the Add-On always runs the standard Value Mapping for the populated values. It would be useful if you want to map the result values for example fi you have restricted picklist at the target side.


See also:

Introduction to the SFDMU Add-On API Engine

The ExportFiles Core Add-On module

Full export.json format

Last updated on Tu May 2022