Values Mapping.

This feature allows to automatically modify certain source values according to the given mapping table before updating the Target.

Table of Contents



Feature Overview ⇧

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:

ObjectName FieldName RawValue Value
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.

Regex Replacement ⇧

Regular expressions offer another powerful method for value replacement. Below is an example of value mapping configuration using JS RegExp:

ObjectName FieldName RawValue Value
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'.

Notes:
  • 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.

JS Eval 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:

ObjectName FieldName RawValue Value
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:

ObjectName FieldName RawValue Value
Account TEST__c /DDMM(\d+)/ eval(new Date().getDate() + '0' + (new Date().getMonth() + 1) + '$1')

For 'DDMM01', the result would be '280701'.

RAW_VALUE keyword ⇧

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:

ObjectName FieldName RawValue Value
Account TEST__c Source value eval('RAW_VALUE was replaced')

Given 'Source value', the output would be 'Source value was replaced'.

Notes:
  • 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.
Last updated on 13th Nov 2023