how to check target fields are empty before updating with transform map

[sankon]

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!

[Sukraj Raikhraj]

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

[Ankur Bawiskar · Accepted solution]

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 Bawiskar]

@sankon 

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

[Desmo]

Hi @Ankur Bawiskar,

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.