- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
03-19-2021 07:55 AM
Hi,
I have to update more than 100,000 records in sys_user table with location, phone number and email from an excel sheet. Some of the records have one or more of the values missing and some have all the values. I want to know what is the best way to check if any of the fields are empty and update the field with the value from excel sheet. I also want to skip any records that have all the values. I do not want to insert any new records. Is it better to use transform script? If so, Do I use onBefore? How do I check if any of the fields values are missing and only update the missing values? Appreciate it.
Thanks in advance!
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
03-19-2021 08:06 AM
Hi,
you can only determine if the target field is empty or not once you determine if record is to be created/updated based on Coalesce field
You can use onBefore transform script for this
Example for 1 field. enhance for other fields as well
(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {
// Add your code here
if(target.last_name == ''){
target.last_name = source.u_last_name;
}
})(source, map, log, target);
Regards
Ankur
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
03-19-2021 08:05 AM
1) use the OnBefore Transform script to prevent insert on new records:
if (action == "insert") {ignore=true; }
https://docs.servicenow.com/bundle/quebec-platform-administration/page/administer/import-sets/task/t_AddOnBeforeScriptToTransformMap.html
2) set the coalesce and that will update the records.
https://docs.servicenow.com/bundle/paris-platform-administration/page/administer/import-sets/concept/c_ImportSetCoalesce.html
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
03-19-2021 08:06 AM
Hi,
you can only determine if the target field is empty or not once you determine if record is to be created/updated based on Coalesce field
You can use onBefore transform script for this
Example for 1 field. enhance for other fields as well
(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {
// Add your code here
if(target.last_name == ''){
target.last_name = source.u_last_name;
}
})(source, map, log, target);
Regards
Ankur
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
03-20-2021 09:03 AM
Hope you are doing good.
Did my reply answer your question?
If so, please mark appropriate response as correct & helpful so that the question will appear as resolved for others who may have a similar question in the future.
Thanks!
Ankur
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
11-11-2021 10:11 PM
Hi
I have a similar situation where I only want records to be updated if the target field is empty. In other words, if target date field is empty, update with source date field.
I added an onBefore script, however, it's not working. Should I place in the Script part of the transform map [Run script]? Also, I do have two field maps that are coalesced. Could that be the problem?
(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {
// Add your code here
if(target.u_request_date == ''){
target.u_request_date = source.u_request_date;
}
})(source, map, log, target);
Thank you.