Transform Script to lookup specific sys_id and update record

tricial · ‎06-22-2017

Use case that I need some help with:

Location table has 7 records with the same location name, each having their own sys_ids (obviously).

I am performing a cleanup and want to update the Location record based on a specific sys_id that I have in my import file.

So I need to Lookup the source.sys_id to the cmn_location sys_id for that specific record

If it is found update the record information from the import file (address, phone, etc..)

If there is no sys_id in the import file (I dont have some records in the cmn_location table) they would need to be created.

I have tried an onbefore transform script as follows:

But it is inserting new records and adding the sysid as the name of the new record.

Whatever I try it either creates a new record with the name as the sysid, or if I comment out id=pr.sys_id it totally ignores all and shows the target (empty)

What am I missing please?

Thank you
Tricia

onBefore transform script

var id = source.u_sys_id; //This is the sysid of the record I want to update

var desc = source.u_description; //the description contains the name value of the location that matches the sysid

var pr = new GlideRecord('cmn_location');

pr.addQuery('sys_id', 'id');

pr.addQuery('name', 'desc');

pr.query();

if(pr.next()){

id = pr.sys_id;

pr.update();

}

else (how do I set it to create) else ignore = false?

{

ignore=true;

}

tricial · ‎06-22-2017

Hmm, something is still not right

Ok so I added the new field and was able to map sys_id to sys_id and set to coalece

It still creates a new record and does not update the existing record even though the sys id is there

sys_idLIKE67a164394f66be086118e3414210c755

This is the sysid in my import (67a164394f66be086118e3414210c755) and if I look up location with that sysid it finds the record

but-on import it is not updating it but creating a new record.

In my transform the field is mapped sysid to sysid and coalece true

In my import file the sysid is correct

The first record is what I need to update, but again not sure why it is not updating it and creating a new record.

Not sure what the heck I am doing wrong.

tricial · ‎06-22-2017