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

[kiranhegde]

Savitha,

If i am not wrong you have training as well as associate id both coalesce fields.

That means both should be unique else it will create a new record which is an expected behavior.

REgards,

Kiran

[savitha5]

Kiran,

Yes I have two coalesce fields and I agree that it is expected to insert new record to the target table if there is no unique value.

But here the issue is, it is creating new record into another table. I mean, the Resource Attribute field in my target table is a reference field which refers Resource table and it is creating new Resource record under this table.

In the below script I want to check if the Associate ID in the source table is existing in resource Attribute table and if yes then the training data should be updated into the target table and If the Associate does not exist then it should skip updating. Since Resource Attribute field in target table is a reference field and if name of the Associate is not matching the reference table it is creating duplicate record. Please let me know how to restrict creating record in Resource Attribute table.

(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");

[Deepak Kumar5 · Accepted solution]

Update the Choice Action of " Associate ID" to "Reject" , It will not update/insert that row which have not correct 'Associate ID'

[savitha5]

Thankyou Deepak,

I missed that step. Also I have removed my Transform map Run script and placed it under Resource Attribute Source Script.
Now it works fine!.

answer=setresource();

function setresource()

{

  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()) {

  answer= gr.sys_id;

    }

  else

  {

  answer="";

  ignore = true;

  }

  return answer;

}

Thankyou