Update field values based on a field via import file

PoorvaB · ‎02-25-2025

Hi Community,

I have an import file which contains employee number and 2 other fields. Based on employee number, i need to update 2 other fields values.

What is the best approach to update those fields based on employee number? Do i need to use import sets or background script to do this?

What exact steps i need to follow for this?

Thanks,

Poorva Bhawsar

Debasis Pati · ‎02-25-2025

Hello @PoorvaB ,

Create an Import Set Table:
- Navigate to System Import Sets > Load Data.
- Create a new Import Set Table and upload your file containing the employee number and the two other fields.
Create a Transform Map:
- Navigate to System Import Sets > Administration > Transform Maps.
- Create a new Transform Map and map the fields from your Import Set Table to the target table (e.g., sys_user if you're updating user records).
- Ensure you map the employee number field to the corresponding field in the target table.
Define Field Mapping:
- In the Transform Map, define the field mappings for the two fields you want to update based on the employee number.
- Use the Coalesce field to identify the unique record. Set the employee number field as the coalesce field. This ensures that the existing records are updated rather than creating new ones.
Script for Field Update:
- If you need to perform additional logic or transformations, you can use a Transform Script. For example:

(function transformRow(source, target, map, log, isUpdate) {
// Update the target fields based on the source fields
target.field1 = source.field1;
target.field2 = source.field2;
})(source, target, map, log, isUpdate);

Run the Transform:
- Navigate to System Import Sets > Load Data.
- Select your Import Set Table and click on Transform.
- Choose the Transform Map you created and run the transform.
Verify the Data:
- After the transform is complete, verify that the fields have been updated correctly in the target table.
  
  Please Mark it as correct/helpful if this resolves your query.
  
  Regards,
  Debasis

View solution in original post

Ankur Bawiskar · ‎02-25-2025

@PoorvaB

if the number of users are more then use data source and use coalesce on employee number

if it's less then use background script

If my response helped please mark it correct and close the thread so that it benefits future readers.

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader

PoorvaB · ‎02-25-2025

There are almost 16k+ records.

Ankur Bawiskar · ‎02-25-2025

@PoorvaB

then have an excel template

Create a data source, load all the records

Then create transform map, field map

Coalesce on Employee number

Note: Test load for 20 records 1st and then load all and run the transform map

I hope you can configure the data source, transform map, field map etc on your own.

If my response helped please mark it correct and close the thread so that it benefits future readers.

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader

PoorvaB · ‎02-25-2025

Yeah i know this configuration. One more question i have, employee numbers are already there in that table, do i need to add those employee numbers in the excel as well? I guess yes because based on it only we want to update other 2 fields.

Dr Atul G- LNG · ‎02-25-2025

Yes, and that field work as coalsec for you so you need it or you can use email id as well.

*************************************************************************************************************
If my response proves useful, please indicate its helpfulness by selecting " Accept as Solution" and " Helpful." This action benefits both the community and me.

Regards
Dr. Atul G. - Learn N Grow Together
ServiceNow Techno - Functional Trainer
LinkedIn: https://www.linkedin.com/in/dratulgrover
YouTube: https://www.youtube.com/@LearnNGrowTogetherwithAtulG
Topmate: https://topmate.io/atul_grover_lng [ Connect for 1-1 Session]

****************************************************************************************************************