Record transformation examples.

Table of Contents

• Example 1. Assign a default User (e.g. OwnerId lookup) to target record.
  • Using the core:RecordsTransform module.
  • Using the ValueMapping.
• Example 2. Clear all text fields in an sObject.
• Example 3. Assign constant value to specific field.
• Example 4: Reset All Checkbox Field Values to False
• Example 5. Populate Text Field in Child Object with Parent Record ID (Parent-Child Relationship)
• Example 6. Populate Text Field with Values Taken from Both Source and Target Orgs

Example 1. Assign a default User (e.g. OwnerId lookup) to target record. ⇧

The configurations below assign default user Id to OwnerId lookup if the given user does not exist in the target org.

Using the core:RecordsTransform module. ⇧

The first solution uses the core:RecordsTransform and the constant sourceField pattern.

It allows to assign user Id dynamically by the given user's name which is usually the same in the source and in the target orgs.

Let's say, the Name of the default owner user to assign is: "Test Target User".

{
    "objects": [
        {
            "operation": "Upsert",
            "externalId": "Name",
            "query": "SELECT Id, Name, Phone, OwnerId from Account WHERE Name IN ('ACC_10000', 'ACC_10001')",
            "beforeUpdateAddons" : [
                {                        
                    "module" : "core:RecordsTransform",
                    "args": {
                        "fields": [
                            {
                                "alias": "ownerId",
                                "sourceObject": "Account",
                                "sourceField": "OwnerId"
                            },
                            {
                                "alias": "defaultOwnerId",
                                "sourceObject": "User",
                                "sourceField": "Id",
                                "isConstant": true,
                                "lookupSource": "target",
                                "lookupExpression": "source.Name == 'Test Target User'"
                            }
                        ],
                        "transformations": [
                            {
                                "targetObject": "Account",
                                "targetField": "OwnerId",
                                "formula": "formula.ownerId || formula.defaultOwnerId"
                            }
                        ]
                    }
                }
            ]
        }
    ],
    "promptOnMissingParentObjects": false
}

Using the ValueMapping. ⇧

The second solution uses ValuesMapping feature (this approach would be preferred when you know the exact target Owner Id value):

export.json:

{
    "objects": [
        {
            "operation": "Upsert",
            "externalId": "Name",
            "deleteOldData": true,
            "query": "SELECT Id, Name, Phone, OwnerId from Account WHERE Name IN ('ACC_10000', 'ACC_10001')",
            "useValuesMapping": true
        }
    ],
    "promptOnMissingParentObjects": false
}

ValueMapping.csv file:

ObjectName,FieldName,RawValue,Value
Account,OwnerId,/^$/,0058d0000054PvGAAU

Example 2. Clear all text fields in an sObject. ⇧

You can use the type_string Multiselect Keyword to select only string-type fields and apply the Data Anonymization feature to reset these fields.

Run the job in one-directional mode.

Below is a basic export.json configuration to help achieve this:

{

    "objects": [
        {
            "operation": "Update",
            "externalId": "Id",
            "query": "SELECT type_string FROM Account",
            "mockFields": [
                {
                    "name": "all",
                    "pattern": "c_set_value(null)", 
                    "excludeNames": ["Name"] // This will exclude unwanted fields from being anonymizing
                }
            ],
            "updateWithMockData": true
        }
    ]
}

Example 3. Assign constant value to specific field. ⇧

{
    "objects": [
        {
            "operation": "Update",
            "externalId": "Name",
            "query": "SELECT OwnerId FROM Account"
        }
    ]
}

The example above assigns a constant value to the OwnerId field of an Account using the Values Mapping feature.

Create a ValueMapping.csv file with the following content:

Here, 00530000003xqAb is the constant value of the target user ID you want to assign to the OwnerId field.

Example 4: Reset All Checkbox Field Values to False ⇧

The configuration below uses the Values Mapping feature to reset all values for the checkbox field Test__c to false.

{
    "objects": [
        {
            "operation": "Update",
            "externalId": "Name",
            "query": "SELECT Test__c FROM Account"
        }
    ]
}

Create a ValueMapping.csv file with the following content to apply the changes:

Example 5. Populate Text Field in Child Object with Parent Record ID (Parent-Child Relationship) ⇧

In typical scenarios, SFDMU can automatically populate a parent record ID in a child lookup field using the provided external ID. However, if you need to populate a non-lookup or non-master-detail field with the target record ID, follow the steps below.

Scenario You have a custom object Profile_Settings__c with a text field Profile_Id__c that contains Profile IDs. The goal is to update Profile_Id__c in the target org by replacing source Profile IDs with the corresponding target Profile IDs, assuming that Profile names are identical in both orgs and can be used as the external ID.

Since Profile_Settings__c is not linked to the Profile object by a lookup, you cannot simply include Profile_Id__c in a query. The following configuration won't work:

{
    "objects": [
        {
            "query": "SELECT Id FROM Profile",
            "externalId": "Name"
        },
        {
            "query": "SELECT Id, Profile_Id__c FROM Profile_Settings__c",
            "operation": "Upsert",           
            "externalId": "Name"
        }
    ]
}

Solution Use the configuration below to achieve the desired outcome:

{
    "objects": [
        {
            "query": "SELECT Id FROM Profile",
            "externalId": "Name"
        },
        {
            "query": "SELECT Id, Profile_Id__c FROM Profile_Settings__c",
            "operation": "Upsert",
            "beforeUpdateAddons": [
                {
                    "module": "core:RecordsTransform",
                    "description": "Updates Profile_Id__c with target ProfileId",
                    "args": {
                        "fields": [
                            {
                                "alias": "sourceProfileName",
                                "sourceObject": "Profile",
                                "sourceField": "Name",
                                "lookupExpression": "source.Id == target.Profile_Id__c",
                                "lookupSource": "source"
                            },
                            {
                                "alias": "targetProfileId",
                                "sourceObject": "Profile",
                                "sourceField": "Id",
                                "lookupExpression": "formula.sourceProfileName == source.Name",
                                "lookupSource": "target"
                            }
                        ],
                        "transformations": [
                            {
                                "targetObject": "Profile_Settings__c",
                                "targetField": "Profile_Id__c",
                                "formula": "formula.targetProfileId"
                            }
                        ]
                    }
                }
            ],
            "externalId": "Name"
        }
    ]
}

This configuration uses the RecordsTransform Core Add-On Module:

1. sourceProfileName Field: Populates the formula.sourceProfileName with the source Profile name by comparing the source Profile ID (Profile_Id__c) with the Profile record ID.
2. targetProfileId Field: Populates formula.targetProfileId with the target Profile ID using the previously calculated formula.sourceProfileName.
3. Transformation: Finally, updates the Profile_Id__c in the target org with the value of formula.targetProfileId.

Example 6. Populate Text Field with Values Taken from Both Source and Target Orgs ⇧

The configuration below will populate the TEST_TEXTBOX__c field of the Account object with a final value that is a concatenation of the following:

• The RecordTypeId value from the target org.
• The Name value of the parent TestObject3__c record from the source org.
• The Website value from the target org.

In our data model, the TestObject3__c object is the parent of the Account object, and the Account has a lookup field to TestObject3__c.

Note that:

• To prevent the Website field from being updated when included in the Account definition, add this field to the excludedFromUpdateFields array.
• You don't need to explicitly include RecordType and TestObject3__c object definitions in the configuration, as they will be automatically included with Readonly operation , since they are used as the sourceObject in the fields definitions.
• We include lookup fields in the Account object using the includeFields property. For example, "Account.RecordTypeId" will add the RecordTypeId field to the Account query.
• We use the valueSource property to define whether to take the value from the record on the data source or data target side.

{
    "objects": [
        {
            "query": "SELECT Id FROM Account WHERE Name = 'ACC_10000'",
            "operation": "Update",
            "excludedFromUpdateFields": [
                "Website"
            ],
            "beforeUpdateAddons": [
                {
                    "module": "core:RecordsTransform",
                    "description": "Update \"TEST_CHECKBOX__c\" field",
                    "args": {
                        "fields": [
                            {
                                "alias": "targetRecordTypeId",
                                "sourceObject": "RecordType",
                                "sourceField": "Id",
                                "valueSource": "target",
                                "includeFields": [
                                    "Account.RecordTypeId"
                                ]
                            },
                            {
                                "alias": "sourceTestObject3Name",
                                "sourceObject": "TestObject3__c",
                                "sourceField": "Name",
                                "includeFields": [
                                    "Account.TestObject3__c"
                                ]
                            },
                            {
                                "alias": "targetWebsite",
                                "sourceObject": "Account",
                                "sourceField": "Website",
                                "valueSource": "target"
                            }
                        ],
                        "transformations": [
                            {
                                "targetObject": "Account",
                                "targetField": "TEST_TEXTBOX__c",
                                "formula": "formula.targetRecordTypeId  + '_' + formula.sourceTestObject3Name + '_' + formula.targetWebsite"
                            }
                        ]
                    }
                }
            ],
            "externalId": "Name"
        }
    ]
}

References:

core:RecordsTransform module

ValuesMapping feature