Populating String value to a Reference field in Target Table.

[Mrman]

Hi All,

Can some one please help me with the below Transform Script. Please let me know if this fine. I want to Match the Concatenated string from my Custom table into a field called model_id into cmdb_ci which is reference field.

Script:

=====

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

  var mnfc = source.u_manufacturer.trim();

  var mnfcr = mnfc.replace(/,/g, "");

  var mdl = source.u_model.trim();

  //var res = mnfcr.concat(mdl);

  var res = mnfcr + " " + mdl;

  gs.log("Concatenated string : "+res);

  var type = source.u_ci_server_type;

  if(type!='Virtual'){

  //gs.log("into if");

  var pm = new GlideRecord('pc_vendor_cat_item');

  //pm.addQuery('model.display_name',res);

  pm.addQuery('short_description',res);

  pm.query();

  pm.getEncodedQuery("QueryResult :"+pm);

  if(pm.next()){

  target.model_id = pm;

  gs.log("Target :"+target.model_id);

  }

  else{

  ignore = true;

  // code for logging error messages

  gs.include('data_load_logs_asset');

  var pmdl = new data_load_logs_asset();

  var logmessage ='';

  var topic ='';

  var messagetype = '';

  topic = serial;

  logmessage =logmessage + "*-*" + "Warning: The Model "+res+" is not present in vendor catalog table.";

  messagetype = messagetype + "*-*" + "Warning";

  pmdl.insertLogMessage(tranform_map,logmessage,topic, import_number, count, messagetype);

  }

  }

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

[Shishir Srivast]

Hi Ravi,

Please find my few observation below not sure about else condition though, please check if this helps.

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

  var mnfc = source.u_manufacturer.trim();

  var mnfcr = mnfc.replace(/,/g, "");

  var mdl = source.u_model.trim();

  var res = mnfcr + " " + mdl;

  var type = source.u_ci_server_type;

  if(type !='Virtual'){

  var pm = new GlideRecord('pc_vendor_cat_item');

  pm.addQuery('short_description', res);

  pm.query();

  if(pm.next()){

  var mdel = pm.getValue('short_description'); // You have used getDisplayValue but that's not needed since i believe short_description is not a reference field.

  //if(mdel == res){ // This if condition is not required since you are querying with the same thing.

  //target.model_id = mdel.short_description; // you are already saving the short description in mdel variable then again no need to do dot walking.

  target.model_id = mdel;

  gs.log("Target :"+target.model_id);

  //}

  }

  else{

  ignore = true;

  // code for logging error messages

  gs.include('data_load_logs_asset');

  var pmdl = new data_load_logs_asset();

  var logmessage ='';

  var topic ='';

  var messagetype = '';

  topic = serial;

  logmessage =logmessage + "*-*" + "Warning: The Model "+res+" is not present in vendor catalog table.";

  messagetype = messagetype + "*-*" + "Warning";

  pmdl.insertLogMessage(tranform_map,logmessage,topic, import_number, count, messagetype);

  }

  }

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

[Mrman]

Hi Srivastava,

About the below if statement , please clarify . You mentioned below.

//if(mdel == res){ // This if condition is not required since you are querying with the same thing.

Here I need to match the concatenated string from my custom table with short_description in pc_vendor_cat_item . Only if there is match then assign the target.model_id .

Also , model_id in target table is a reference field. Please suggest.

[Mrman]

Hi Srivastava,

I have tried what you have suggested . As per logs I am able to see that getting the value , however it is not assigning to model_id in target field . It still shows as Unknown. Please see below what I found in logs and please guide.

Code:

====

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

  var mnfc = source.u_manufacturer.trim();

  var mnfcr = mnfc.replace(/,/g, "");

  var mdl = source.u_model.trim();

  //var res = mnfcr.concat(mdl);

  var res = mnfcr + " " + mdl;

  gs.log("Concatenated string : "+res);

  var type = source.u_ci_server_type;

  if(type!='Virtual'){

  var pm = new GlideRecord('pc_vendor_cat_item');

  pm.addQuery('short_description',res);

  pm.query();

  gs.log("QueryResult :"+pm.getEncodedQuery());

  if(pm.next()){

  gs.log("into secondif"); ---- I am getting in here and able to see this logs.

  var mdel = pm.getValue('short_description');

  gs.log("ModelValue: "+mdel); ---- I am able to see this logs and coming correctly as ModelValue: HP ProLiant DL380 G7

  target.model_id = mdel;

  gs.log("Target :"+target.model_id); --- I am able to see the value correctly here also in logs as Target :HP ProLiant DL380 G7

  //}

      }

  else{

  ignore = true;

  // code for logging error messages

  gs.include('data_load_logs_asset');

  var pmdl = new data_load_logs_asset();

  var logmessage ='';

  var topic ='';

  var messagetype = '';

  topic = serial;

  logmessage =logmessage + "*-*" + "Warning: The Model "+res+" is not present in vendor catalog table.";

  messagetype = messagetype + "*-*" + "Warning";

  pmdl.insertLogMessage(tranform_map,logmessage,topic, import_number, count, messagetype);

  }

  }

  //}

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

[Shishir Srivast · Accepted solution]

Hi Ravi,

This (below code) will try to assign the string field in model_id,

var mdel = pm.getValue('short_description');

target.model_id = mdel;

but since you told it's a reference field then in that case you have to assign sys_id of found record. Please try if this helps.

target.model_id = pm.sys_id;

Also please check what is the display value set for model_id field's table?

[Mrman]

Hi Srivastava,

I have tried with what you suggested . It is not working. Please suggest.

Code 1:

=========

var mnfc = source.u_manufacturer.trim();

  var mnfcr = mnfc.replace(/,/g, "");

  var mdl = source.u_model.trim();

  var res = mnfcr + " " + mdl;

  var type = source.u_ci_server_type;

    if(type!='Virtual'){

  var pm = new GlideRecord('pc_vendor_cat_item');

  pm.addQuery('short_description',res);

  pm.query();

  if(pm.next()){

  //gs.log("into secondif");

  var mdel = pm.getDisplayValue('short_description');

    if(mdel == res){

    target.model_id = pm.short_description;

  gs.log("Target :"+target.model_id);

  }

Code 2:

=======

var mnfc = source.u_manufacturer.trim();

  var mnfcr = mnfc.replace(/,/g, "");

  var mdl = source.u_model.trim();

  var res = mnfcr + " " + mdl;

  var type = source.u_ci_server_type;

    if(type!='Virtual'){

  var pm = new GlideRecord('pc_vendor_cat_item');

  pm.addQuery('short_description',res);

  pm.query();

  if(pm.next()){

  //gs.log("into secondif");

  var mdel = pm.getDisplayValue('short_description');

    if(mdel == res){

    target.model_id = pm.short_description;

  gs.log("Target :"+target.model_id);

  }