This feature allows to automatically modify certain source values according to the given mapping table before updating the Target.
Table of Contents
In the realm of data migration or synchronization, situations often arise where the raw field values from a source—whether a CSV file or an org—aren't directly appropriate for the target system. These values might necessitate unique transformations to fit the target's requirements. This could be due to data type compatibility, maintaining data integrity, or adhering to certain business rules.
For instance, imagine having a CSV file where picklist fields contain translated labels instead of the actual required values. These labels must be converted into their corresponding values for the data to be meaningful and well-structured upon upload.
Addressing these intricate data transformation requirements, two key functionalities are provided:
- useValuesMapping: This option emerges as a boon when there's a need for unique transformations of field values before they're updated in the target. Setting this to true enables the system to enforce a field value mapping. This ensures that data, whether from CSV files or org sources, is transformed appropriately before being updated in the target.
- useCSVValuesMapping: Sometimes, raw data in a CSV file needs a foundational transformation even before the migration kicks off. Activating this option by setting it to true ensures such preliminary transformations for the CSV file sources, priming them for subsequent migration to the desired sObject.
It's essential to understand that these two functionalities are not alternatives but complementary. They can be activated simultaneously, allowing users to perform foundational transformations on CSV data and then additional specialized transformations on field values. Both functionalities operate independently but cohesively to guarantee smooth, accurate, and efficient data migration.
The blueprint for these transformations is the ValueMapping.csv configuration file. Positioned in the same directory as the export.json file, this file outlines how raw values should metamorphose before their upload to the target system.
Here's a glimpse of the structure the ValueMapping.csv should adhere to:
|The sObject api name to map, f.ex. Case||The field api name to map, f.ex. Reason||The raw source value to be transformed, f.ex. translated picklist label Вопрос||The actual value that should be uploaded to the Target instead of the provided RawValue, f.ex. Question|
Only values specified in the ValueMapping.csv will undergo transformation, leaving the rest untouched. For transforming an empty source record, use #N/A as the RawValue.
Regular expressions offer another powerful method for value replacement. Below is an example of value mapping configuration using JS RegExp:
|Case||TEST__c||The Regex expression /(.+)/||The Replacement expression REPLACE_$1|
This transformation parallels the JS code:
'TheRawFieldValue'.replace(new RegExp('(.+)', 'gi'), 'REPLACE_$1');
Which then results in: 'REPLACE_TheRawFieldValue'.
- To ensure the Plugin recognizes your regex correctly, enclose the expression between two slashes: /YOUR_REGEX_EXPRESSION/.
- Regular expressions can be seamlessly combined with other replacement definitions.
- For each field, stick to a single regex expression definition.
- The gi option is always used while executing the RegExp replacement.
The native js eval() function paves another route for replacements, enabling the execution of any js expression.
Suppose you have the source field value 'DDMM', which must transform to represent today's date and month number. Here's how you can leverage eval() for such a transformation:
|Account||TEST__c||DDMM||eval(new Date().getDate() + '0' + (new Date().getMonth() + 1))|
For 'DDMM', the output would be '2807' (assuming today's date is the 28th of July).
More intricate scenarios might see eval() working in synergy with regex replacements. For example:
|Account||TEST__c||/DDMM(\d+)/||eval(new Date().getDate() + '0' + (new Date().getMonth() + 1) + '$1')|
For 'DDMM01', the result would be '280701'.
To integrate the original field value within your expression, utilize the reserved term RAW_VALUE. This placeholder will be substituted with the original field value from the source record:
|Account||TEST__c||Source value||eval('RAW_VALUE was replaced')|
Given 'Source value', the output would be 'Source value was replaced'.
- For the replacement to work as envisioned, encapsulate the js expression to evaluate within the eval() function like: eval(ANY VALID JS EXPRESSION).
- Refrain from enclosing the expression in single or double quotes. Instead, place it as-is between the brackets.