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

coryseering · ‎09-10-2015

Hi Michael,

There isn't anything wrong with the script, so there must be some other configuration within the transform that is causing an issue.

Mind if I hop (log into your instance as a user with special admin permissions) into your instance and check it out? Let me know your instance name and the name of the transform map, and I'll take a look.

Mike Pottorf · ‎09-14-2015

Hi Cory,

Sorry it took a while to respond, I got caught up in something else. How do you do that, log into my demo instance? Do I need to do anything on my end?

Thanks,

Mike

coryseering · ‎09-14-2015

Hi Michael,

I'm a ServiceNow developer. I can log in using my maintenance credentials.

Mike Pottorf · ‎09-15-2015

Hi Cory,

I am working in my demo instance: https://dev13104.service-now.com/navpage.do

The name of the transform map is fbss officers test transform map. Thanks for taking a look you have been a huge help.

Thanks,

Mike

coryseering · ‎09-15-2015

Hi Mike,

I moved the script to a field map and modified it to set the answer variable. However, I notice that the field itself wasn't part of the transform, and that you aren't coalescing on anything. If you run the map as-is, it should just insert new u_position records where the only value populated is the u_position field.

I added the department to the field map, using the source script to generate the correct location (or map to it if found). You can try that out, but since there is still no coalesce value and there are only two fields mapped, you'll see it inserting new records with only two values populated: the u_position and the u_department fields. You'll need to map the rest of the fields according to your desired logic.