How to handle Unique Key violation detected by database Duplicate Entry error in a script

[Tom Siegel]

Good day all! I have been tasked with taking all of our Task records where service_offering is not null and loading the corresponding Task numbers and Service Offerings into the task_service_offerings table. The "system" is enforcing that the task_service_offering record is unique based upon the combination of task and service_offering, which in my case is a good thing. The bad thing is that the error does not appear catchable in ServiceNow. I can write a function to check uniqueness before trying to insert, but I was hoping someone knew of a way to catch or ignore the error and have my script move on to the next record. In current testing the script appears to stop executing when it encounters the error.

Here is the script I am testing with. I have run it as BR and a background script with the same results

 var source = "MigrateServiceOffering";
    var count = 0;
    var qString = "service_offeringISNOTEMPTY";
    var grt = new GlideRecord('task');
    var grso = new GlideRecord('task_service_offering');
    grt.addEncodedQuery(qString);
    grt.addQuery("sys_updated_on", ">", "2021-04-13");
    grt.query();
    while (grt.next()) {
        if (grt.sys_id != undefined && grt.service_offering != undefined) {
            count++;
            grso.initialize();
            grso.task = grt.sys_id;
            gs.addInfoMessage("sys_id:  " + grt.sys_id);
            grso.service_offering = grt.service_offering;
            gs.addInfoMessage("service offering:  " + grt.service_offering);
            grso.setWorkflow(false);
            try {
                grso.insert();
            } catch (ex) {
                gs.log("Tsiegel Error: " + ex.message, source);
            }
        }
    }
    gs.addInfoMessage("Count:  " + count);

Thanks,

Tom

[Allen Andreas]

Hi,

If you don't mind, please update question placing script in the "Insert/Edit code sample" feature of the forums. This helps us read it easier and keeps things organized:

Please mark reply as Helpful, if applicable. Thanks!

[Ahmed Drar]

Hello,

you can move the line below to be before grso.initialize();

var grso = new GlideRecord('task_service_offering');

Please mark my answer as ✅ Correct / Helpful based on the Impact.

[Tom Siegel · Accepted solution]

After doing a bit of research I found that outside of ServiceNow there are API's that can be used to trap these types of errors. Since trapping these errors is not possible in ServiceNow. I headed down the path of creating a function that checks for duplicates before trying to insert the record.

Although throwing and catching the error would taken less time to code, In general have found that it is better to account and code for all foreseen errors ahead of time. So in the end this was the proper solution.

Thanks - Tom