Record transformation examples.
Table of Contents
- Example 1. Assign a default User (e.g. OwnerId lookup) to target record.
- 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:
ObjectName | FieldName | RawValue | Value |
---|---|---|---|
Account | OwnerId | /(.+)/ | 00530000003xqAb |
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:
ObjectName | FieldName | RawValue | Value |
---|---|---|---|
Account | Test__c | true | false |
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:
- 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. - targetProfileId Field: Populates
formula.targetProfileId
with the target Profile ID using the previously calculatedformula.sourceProfileName
. - Transformation: Finally, updates the
Profile_Id__c
in the target org with the value offormula.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 parentTestObject3__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 toTestObject3__c
.
Note that:
- To prevent the
Website
field from being updated when included in theAccount
definition, add this field to theexcludedFromUpdateFields
array. - You don't need to explicitly include
RecordType
andTestObject3__c
object definitions in the configuration, as they will be automatically included with Readonly operation , since they are used as thesourceObject
in thefields
definitions. - We include lookup fields in the Account object using the
includeFields
property. For example,"Account.RecordTypeId"
will add theRecordTypeId
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: