Transform Map - Replace part of string with desired value

[Kumar38]

I have to upload data into service now table , which is pretty straight forward apart from one scenario where the source has oldEmail and newEmail and target field already has data in it.

So , if old email is found in target , replace it with new email.

Data on target field - xyz@gmail.com;abc@gmail.com;pqr@gmail.com

oldEmail = abc@gmail.com

newEmail = 123@gmail.com

new data on target field = xyz@gmail.com;123@gmail.com;pqr@gmail.com 

[Karthiga S]

Hello @Kumar38 

1. First, you need to import the data into a staging table in ServiceNow. You can use Import Sets for this.

2. Once the data is in the staging table, you can write a script to process the data. This script will look for the oldEmail in the target field and replace it with the newEmail.

Here is a sample script:

var oldEmail = 'abc@gmail.com';
var newEmail = '123@gmail.com';

// Get the record from the staging table
var gr = new GlideRecord('your_staging_table');
gr.query();
while (gr.next()) {
// Get the target field value
var targetField = gr.target_field.toString();

// Check if the oldEmail is in the target field
if (targetField.includes(oldEmail)) {
// Replace the oldEmail with the newEmail
targetField = targetField.replace(oldEmail, newEmail);

// Update the target field
gr.target_field = targetField;
gr.update();
}
}

3. After the script has run, the data in the staging table will have the oldEmail replaced with the newEmail in the target field.

4. Now, you can use Transform Maps to map the data from the staging table to the target table in ServiceNow.

5. Run the import to move the data from the staging table to the target table.

Remember to replace 'your_staging_table' and 'target_field' with the actual name of your staging table and target field.

Please mark it Correct and Hit Like if you find this helpful!

Regards,

Karthiga