Need help with coalesce (trying to prevent duplicates)

[robhaas]

I am importing data with a field that has information such as the following which is going into the source field 'u_entitlement_path'.

SYSTEM/PROD/GROUP\GROUPNAME

I am parsing the SYSTEM and GROUPNAME from the source field using RegEx and putting each of them into their own fields on the target table. I am needing to prevent duplicate entries from being added to the target table based on both the system and the groupname.

I have tried turning coalesce on for both field maps, but the new entries still get added. I am not too familiar with coalescing either so I'm sure lack of knowledge is definitely key. All help is appreciated.

[Abhinay Erra]

Sure, will provide you solution shortly

[Abhinay Erra]

Hi robhaas

    You need to put this coe in the onbefore transform script. You do not need the business rule anymore.

if(action=='insert'){

//parse the values

var gr= new GlideRecord("table name");

gr.addQuery('<field name of SYSTEM on target table>',<parsed value of SYSTEM from source field>);

gr.addQuery('<field name of GROUPNAME on target table>',<parsed value of GROUPNAME from source field>);

gr.query();

if(gr.next()){

gr.<approver field name on target table>=source.<approver field name>;

gr.update();

ignore=true;

}

[robhaas]

abhinay

If I try to parse the values from the source table and put them into variables, it is not working correctly. The approver does update now though. Am I doing this incorrect?

if(action=='insert'){

//parse the values

var arr = source.u_element_path.split(/\/|\\/);

var system = arr[0], groupname = arr[4];  

var gr= new GlideRecord('u_appr');

gr.addQuery('u_system',system.);

gr.addQuery('u_entitlement',groupname);

gr.query();

if(gr.next()){

gr.u_approver = source.u_primary_appr;

gr.update();

ignore=true;

}

}

[Abhinay Erra]

Add toString() and trim() as shown below and see if that helps

var arr= source. u_element_path.toString().split....;

var system=arr[0].toString().trim();

Same thing use for other variable

On Tue, Jan 10, 2017 at 17:39 robhaas <community-no-reply@servicenow.com>

[Abhinay Erra]

robhaas

Use this, you are good to go

if(action=='insert'){

//parse the values

var arr = source.u_element_path.toString().split(/\/|\\/);

var system = arr[0].toString().trim();

var groupname = arr[4].toString().trim();

var gr= new GlideRecord('u_appr');

gr.addQuery('u_system',system.);

gr.addQuery('u_entitlement',groupname);

gr.query();

if(gr.next()){

gr.u_approver = source.u_primary_appr;

gr.update();

ignore=true;

}

}