Transform Script to lookup specific sys_id and update record

[tricial]

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;

}

[Chuck Tomasi]

Hi Patricia,

If you've got the SysID in the source record, why not add SysID to the field map and coalesce on it? For example...

[tricial]

Hi Chuck,

I was going to do that but the sysid is not a field available on the list of fields in the target when I try to create a field mapping.

My source sysid is there but I dont see the sysid on the Target cmn_location list of fields to map to.

[tricial]

If I map the sysid from source to the name field on target and coalece it creates new records

There is no sysid on the target fields to map to so I thought name would work.

If I lookup the location by name = sysid on the cmn_location breadcrumb that works.

So not sure why this would not.

[Chuck Tomasi]

Instead of using the field mapper, use the New button on the related list and create the record. You should see a screen similar to what I showed. The dropdown list showed SysID as an option in the source and target for me.