populate reference table fields using transform maps

[daggupati]

Hi,

I have a question regarding to reference table fields populate using transform script. I am importing data from excel sheet and transforming into computer table(cmdb_ci_computer). In the excel sheet i have serial number, model name, model number, manufacturer, OS version, RAM and hard disk. I can import all the fields except model name, manufacturer. In the computer table(cmdb_ci_computer) we have a field called model id(refernce table: cmdb_model) which is reference field, i want to populate model name in the cmdb_model table field called "name" and manufacturer in the "manufacturer"(Refernce to company table) field in the cmdb_model table.

Any suggestions!

Thanks.

[Raju Koyagura]

If you map your excel fields to ServiceNow target table any of the reference fields, if the value exist in reference table it will take the existing one else create a new record in that reference table. So you no need to worry, model name automatically creates a new model in cmdb_model table and the same way manufacturer.

Ref: http://wiki.servicenow.com/index.php?title=Creating_New_Transform_Maps#gsc.tab=0

[daggupati]

Hi Raju,

Thanks for the reply. In this example if we are transforming data to incident table assigned to is the reference field it will create record in the user record and populate assigned to value in the display field of user table which is "name" But in my case Model id display field is "display name" not "name" and in the model id, manufacture field is there which is one more reference field.

Thanks

[Raju Koyagura]

In that case you have to depend on transform map script, before it transform the data into target table you have to get the source fields values and insert the records into cmdb_model, then take the sys_id of that record map it to your target field.

http://wiki.servicenow.com/index.php?title=Transform_Map_Scripts#gsc.tab=0

[daggupati]

I wrote this script but it doesn't working. Is there any changes i need to do in the script.

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

var sid = "";

var name1 = source.u_model;

var man = source.u_company;

var sid1   = "";

var gr = new GlideRecord('cmdb_model');//check if name and and manufacture exist

gr.addQuery('name',name1);

gr.addQuery('manufacturer',man);

gr.query();

if(gr.next()){ //copy the existing record sys_id to Model id field

sid = gr.sys_id;

}

else{ //check company will exist or not

var grp = new GlideRecord('core_company');

grp.addQuery('name',man);

grp.query();

if(grp.next()){// copy company sys_id to manufacture field in model id record

sid1 = grp.sys_id;

}

else{ // create company and copy to manufacture

grp.initialize();

grp.name = man;

sid1 = grp.insert();

}

gr.initialize();

gr.manufacturer = sid1;

gr.name = name1;

sid = gr.insert();

gs.log("sid"+sid);

}

target.model = sid;

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