Importing data referencing multiple tables

[Mike Pottorf]

Hello,

We are currently working on importing all of our HR job codes into ServiceNow.   Our system is build upon 5 codes that represent an employees location, department, and position within the company.   We have structured our tables based upon those three distinctions.   The problem we are running into deals with importing the data into the position table.   We are importing the department, to a reference field that points to the department table, to provide associated information.   The import at first appeared to work great but when we tried scripting against it's value we started to notice some odd behavior.   We discovered that although the department field, on the position table, displayed the correct import value, it didn't necessarily associate with the correct record.   Hopefully this example will shed some light onto the issue:

We have various departments that all have the same title but are at different locations.   We work for a bank so we have a department titled 'Teller' for each of our branches.   Let's say we have 5 branches so we have 5 records to distinguish the 5 Teller departments at each one.   Under that we have Teller Positions, differing for each location.   So when we import data to the position table we need to make sure that the Teller department we are referencing corresponds directly with the associated location.   What we experienced was as the import set ran it would assign the same 'Teller' department to all imported records independent of its associated location.   I tried to work around this by importing, and forcing, the correct location by creating a field I could map the value to.   On the surface this worked.   But when we need to script to the value of the department, once again a reference field, we assume the values associated with the record ServiceNow chose when the import ran.

In order to correct this I have been trying to work on a transform script to select the correct department associated with each record.   This is what I have so far:

If anyone has insight to the problem and maybe a different way to tackle it or has any advice on the script it would be helpful.   The script is running but is unfortunately leaving the fields blank.   Thanks for your help.

Mike

[coryseering]

Hi Mike,

Your script is initializing the Department record after querying for it. I don't think you want to do that. Initializing the record clears *all* values- the query, any values from the loaded record, etc. It's like creating a new blank GlideRecord object for that table.

You should either set target_u_department to the sys_id of the cmn_department record you found, or create a new cmn_department record.

var dept = source.u_department;

var branch = source.u_branch;

var pos;



var deptGr = new GlideRecord('cmn_department');

deptGr.addQuery('name', dept);

deptGr.addQuery('u_support_service_department', branch);

deptGr.query()



if(deptGr.next())

       target.u_department = gr.getValue('sys_id');

else {

       //now create a new record - lets make it explicit

       var newDept = new GlideRecord('cmn_deartment');

       newDept.newRecord();

       newDept.setValue('name',dept);

       newDept.setValue('u_support_service_department', branch);

       newDept.insert();



       target.u_department = newDept.getValue('sys_id');

}

I reqrote your code a little to make the steps more explicit. notice how, if a cmn_department record is found, it's sys_id will be used int he target record's u_department field. If one isn't found, we explicitly create a new one, insert it, and then set it's sys_id into the target record.

I hope that helps,

Cory

[Mike Pottorf]

Hi Cory,

Thanks for getting back to me so quickly.   I ran the transform again and it is still returning empty an empty field for each u_department value but is creating the correct number of records. Every entry will have a corresponding department so I'm not sure if we need the else after the query.   Any other thoughts as how to approach this?   Thanks again.

Mike

[coryseering]

Ooh, I have a bug in my code!

var dept = source.u_department;

var branch = source.u_branch;

var pos;

var deptGr = new GlideRecord('cmn_department');

deptGr.addQuery('name', dept);

deptGr.addQuery('u_support_service_department', branch);

deptGr.query();

if(deptGr.next())

      target.u_department = deptGr.getValue('sys_id');

else {

      //now create a new record - lets make it explicit

      var newDept = new GlideRecord('cmn_deartment');

      newDept.newRecord();

      newDept.setValue('name', dept);

      newDept.setValue('u_support_service_department', branch);

      newDept.insert();

      target.u_department = newDept.getValue('sys_id');

}

Does that solve it?

[Mike Pottorf]

Hey Cory,

Still no luck on the script.   Does it make a difference that the field type for u_department is a reference field?   I agree with the logic of the script but it is not loading any data into the record.   I would assume that independent of the field type the sys id would suffice to pass along the value to the target variable.

Thanks,

Mike