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

[Mike Pottorf]

Hi Cory,

That was the piece I was missing in understanding the transform script was adding it to the field map.   The reason I don't have anything to coalesce is that all of those other records should have been deleted and each record that is being imported should be its own record.   I deleted all of the previous records and then mapped the remaining fields to their intended fields but I am still not having any value associated with the u_department field.   Am I still missing something in terms of how to perform the transform?

Thanks again, you have been an amazing help in working through this.

Mike

[coryseering · Accepted solution]

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.

[Mike Pottorf]

Hi Cory,

You are the MAN!   That worked exactly as intended.   Thank you very much for all of your help with this issue.   Take care.

Mike

[coryseering]

Awesome! Glad it's working.

This is a good example of progressive problem-solving. That's another way of saying "there was more than one thing wrong and I should have been more comprehensive in my earlier analysis".

If you call it something fancy, people think you did it deliberately.

[Mike Pottorf]

I'll buy that:)   Thanks again.