Importing data referencing multiple tables

Mike Pottorf · ‎09-09-2015

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 · ‎09-15-2015

Hi Michael,

I took another look, and the cmn_department lookup isn't working because source.u_branch is a string, and it's doing a lookup against cmn_department where name is the the same as u_department and u_support_service_department equals that branch string. but u_branch is a reference field, so we need to change this line:

deptGr.addQuery('u_support_service_department', branch);

to

deptGr.addQuery('u_support_service_department.u_department', branch);

This affects the creation steps too, though. If you're going to create a new cmn_department record (as we're doing in that script), the line where we set the value of 'u_support_service_department' gets more complicated, since we first have to look up the 'u_support_services_departments' record where the u_branch value has that name, get it's sys_id, and set that as the value for the 'u_support_service_department' field on the new cmn_department record.

I've updated the script:

var dept = source.u_department;


var branch = source.u_branch;




var deptGr = new GlideRecord('cmn_department');


deptGr.addQuery('name', dept);


deptGr.addQuery('u_support_service_department.u_department', branch);


deptGr.query();




if(deptGr.next()) {




       answer = deptGr.getValue('sys_id');


}


else {


       var branchRec = new GlideRecord('u_support_services_departments');


       branchRec.addQuery('u_department', branch);


       branchRec.query();


       


       if(branchRec.next()) {


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


               var newDept = new GlideRecord('cmn_department');


               newDept.newRecord();


               newDept.setValue('name', dept);


               newDept.setValue('u_support_service_department', branchRec.getValue('sys_id'));


               newDept.insert();


               


               answer = newDept.getValue('sys_id');


       } else {


               //we don't have a u_support_services_departments with the right branch. bail out, or add additional processing here


               answer = null;


       }


}

You can see here what I mean- we have to lookup based on the dot-walked value, and if we don't find it, it makes it slightly harder to create a new cmn_department record because we have that dot-walk to take into consideration.

View solution in original post

Deepak Ingale1 · ‎09-09-2015

LDAP Integration - Load Locations and Departments along with user records.

Try to check this link also specially the lines onwards 168. This might also help you.