Transform Map inserting new records to the target fields reference table

[savitha5]

Hi,

I have a Transform Map in which I am trying to update a reference field and there is a field made Coalesce too.

My target table is a Training Table(which contains Resource Name & Associate ID along with Training & Status fields)

Resource Name field (u_resource_attributes) is a reference field on my target table which refers to Resource Attributes (u_resource_attributes) table.

The coalesce is enabled for the Associate ID in field mapping.

There are situations where the excel uploaded contain different name (not exactly as it is in the reference table) but with the right Associate ID.

In such case if I import the data it creates a new record into the Resource Attributes table with Associate ID as empty. The training details are not getting updated on the record with Associate ID instead it is getting updated for the duplicate Associate record.

Also, I have used Run Script in the transform map with the below script

(function transformRow(source, target, map, log, isUpdate) {

  // Add your code here

  var sid='';

  var aid=source.u_associate_id.toString();

  var gr = new GlideRecord("u_resource_attributes");

  gr.addQuery("u_associate_id", aid);

  gr.query();

  if(gr.next()) {

  sid=gr.sys_id;

  target.setValue('u_resource_attributes',sid);

  }

  else

  ignore = true;

})(source, target, map, log, action==="update");

With this script, training details imported are getting updated under the right Associate record but still it is creating duplicate records with empty Associate ID in the refernce table.

Could somebody help me with this. I want to skip creating records in the target field's reference table.

Regards,

Savitha

[Deepak Kumar5]

You have made two field as coalesce so if any one of them will different from existing, it will create a new record.

Change "Choice Action" of your referenced field to "ignore" so it will not create new record in referenced table if not matched.