Updating a Custom Field on sys_user by Matching Email ID Using a Transform Map in ServiceNow

[prithviaram]

I have an Excel file with columns such as Email ID and Custom Field Value. The requirement is to match the Email ID from the staging table with the corresponding record in the sys_user table and update the custom field in sys_user with the value from the staging table. How can this be achieved using a Transform Map in ServiceNow?

[Tanushree Maiti]

Hi @prithviaram 

Try this.

1: Load the Excel Data

• Navigate to System Import Sets > Load Data.
• Select New to create a new staging table name (like  u_user _staging).
• Choose File, click Choose File to attach your Excel sheet, and click Submit.

2: Create the Transform Map

• In the post-load screen, click the Create Transform Map link.
• Configure the following fields on the Transform Map record:
1. Name: Provide a descriptive name (like  Update User Fields).
2. Source Table: Select your staging table (e.g., u_user _staging).
3. Target Table: Select User [sys_user].
• Click Save or submit the record.

3: Map the Fields and Set Coalesce

• Under the Related Lists at the bottom of the map, click Mapping Assist.
• Pair the staging table columns to your target sys_user fields:
1. Map Email ID (Source) -> Email (Target).
2. Map Custom Field Value (Source) -> Your specific Custom Field (Target).
• Click Save.
• Locate the Field Maps related list.
• Open the row where Email ID maps to Email and set Coalesce to true.

4: Restrict to "Update Only" (Optional but Recommended)

• In your Transform Map, go to the Transform Scripts related list and click New.
• Set the When field to onBefore.

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

    // If the record does not already exist in the target table, skip it

    if (action == 'insert') {

        ignore = true;

    }

})(source, map, log, target);

5: Execute the Transformation

• Return to the Transform Map record and click Transform.
• Select your import set and click Transform.
• Check the results to verify that the target fields in the User (sys_user) table have successfully updated