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]

Create a before insert business rule on the target table and check run business rules checkbox on the transform map

Business Rule

When: before insert

Script:

var gr= new GlideRecord(current.getTableName());

gr.addQuery('sys_id','!=',current.getValue('sys_id'));

gr.addQuery('<field name of SYSTEM on target table>',current.<field name of SYSTEM on target table>);

gr.addQuery('<field name of GROUPNAME on target table>',current.<field name of GROUPNAME on target table>);

gr.query();

if(gr.hasNext()){

current.setAbortAction(true);

}

[robhaas]

Should I remove the parsing script that I have as mentioned above when I use this BR? I assume not?

[Abhinay Erra]

No you don't

[robhaas]

Ok, this seems to partially work the way I need. I do not get new records where the system and groupname match. However, I also need to look at the approver. If the approver is different, but the system and groupname match, I need to update the approver. I tried adding the below but it created a new record because the approver did not match:

gr.addQuery('u_approver',current.u_approver);

[robhaas]

abhinay - Are you able to advise on this portion?