How to identify the correct target record before updating it via transform map?

[mnreddy]

Hello,

I have a data source and transform map to update records in cmdb_ci_hardware table. We have coalesce on "name" field to identify the records in target table. However, we have more than 1 record with same name in target table. So transform map is updating the first identified record. We need to put a validation in place so that correct record will be identified and updated by the transform map. The validation is like, if the name == xyz and class == abc then only update. Please note that our data source has only 2 fields in it, i.e. name and location, and we are already using name as the coalesce. Any ideas on how this can be achieved? Thank you. 

Regards,

Mnreddy.

[Tony Chatfield1 · Accepted solution]

Hi, You can 'ignore' a row transform and update your records via a glide query where you have an exact match using an onBefore transform script.

If your data source only contains name and location, I assume that the intention is to update the location field only and that your 'class' is predefined?
You will probably need a glide query to match your location record, so I guess you are passing in a unique location identifier or the snc location sys_id.

var myLocation = new GlideRecord('cmn_location');

//We use an if condition to ensure we have a match
if(myLocation.get('myUniqueValueField', source.locationField)) {

var myClass = 'theClassSysId';

//Query the table defined in the transform record
var myCheck = new GlideRecord(map.source_table);
myCheck.addQuery('name', source.name);
myCheck.addQuery('sys_class_name', myclass);
myCheck.query();

if(myCheck.next()) {

//We have a match so update the record
myCheck.location = myLocation.sys_id;
myCheck.update();

gs.info('Updated location for ' + myCheck.name + ' | ' + myCheck.sys_id);

}

}

//Now stop the row transform, as correct record has been updated
ignore = true;

[mnreddy]

Thanks Tony. Modified it a bit but it worked.

My script:

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

// Add your code here
var sname = source.u_device_name;
var gr = new GlideRecord('cmdb_ci_hardware');
gr.addQuery('name',sname);
gr.addEncodedQuery('sys_class_name!=xyz');//Since I have more than one needed class, I am excluding the one that I don't need!
gr.query();
if(gr.next()){
gr.u_row_rack_location = source.u_rack_name;//Updating information from the source table to target.
gr.update();
}

ignore = true;//Ignoring the transform map update.

})(source, map, log, target);

[Michael Fry1]

Where does the location data come from? In this case it seems like you could provide an export of the data that needs updating, add the location, then import the data back in. All via Easy Import.