question Re: Unique Key violation detected by database - Transform Map in Developer forum

Unique Key violation detected by database - Transform Map

Ayman Lhend1 — Mon, 10 Oct 2022 12:03:19 GMT

Hello,

I'm trying to do an import via a transform map that uses a script to properly import data

In this code below, i'm formatting fields of the column User so that it can fit the ServiceNow standards for Reference type fields (firstName lastName).

But unfortunately i always get the error "Unique Key violation detected by database ((conn=659) Duplicate entry 'bfc6fac0972611103f0f7b771153af05' for key 'PRIMARY')".

I do realize that a lot of people already posted about this error, but i just can't find a solution for my case in the forum, thank you.

N.B : user is a reference field to the system table sys_user

Re: Unique Key violation detected by database - Transform Map

Vipul Sethi — Mon, 10 Oct 2022 14:24:54 GMT

I would suggest it's better to search the full name on user table and then pass the sys_id in target.user rather then display value , In the below example i am assuming ur getting first name in nonformattarr[0] and last name in nonformattarr[1], you can also break the query in first name and last name also but in below ex i have used full name

var nonformattname = source.u_user; var nonformattarr = nonformattname.split(","); var usr = new GlideRecord("sys_user"); usr.addQuery("name", nonformattarr[0] +" "+nonformattarr[1] ); usr.query(); if(usr.next()) target.user = usr.sys_id;

Re: Unique Key violation detected by database - Transform Map

Ayman Lhend1 — Tue, 11 Oct 2022 09:24:25 GMT

This does not generate any errors, but somehow the user field on the target gets populated with the non formatted value of the name (the one we got from source.u_user)

Re: Unique Key violation detected by database - Transform Map

Ayman Lhend1 — Tue, 11 Oct 2022 09:29:49 GMT

I've tried commenting out the part where you assign a value to target.user, and only get information about the user from the DB, but the error still persists

Re: Unique Key violation detected by database - Transform Map

Vipul Sethi — Tue, 11 Oct 2022 12:12:43 GMT

what value you are getting in usr.name in the info message, Also make sure you have not created field map for the same field because you are setting the value of user via transform script.

Re: Unique Key violation detected by database - Transform Map

Ayman Lhend1 — Thu, 13 Oct 2022 08:18:36 GMT

Throught usr.name i'm getting the name of the user in the sys_user table after querying via the second value of nonFormattedNameArray + its first value, the query is working just fine, and it fetches the right name, which means that everything is fine, but when i try to write to target.user i get that error.

Concerning the field map, i honestly have never used it, and i didn't even mention it in my script, so i doubt it's related in any way

Re: Unique Key violation detected by database - Transform Map

Ayman Lhend1 — Tue, 18 Oct 2022 09:20:32 GMT

So after a lot of debugging and headache, i found where the error is coming from.

When i get the first element of the name via nonformattarr[0], somehow JS puts a space just before the beginning of the string, which messes up the whole thing and generates the error.

I just had to remove the space at the beginning of the string with String.trim().

Thank you so much for the help, really appreciate it.