Transformation Script to assign a reference field

[sherman_1206]

When doing imports using transformation maps I have come across a need to set a reference field conditionally based on another field in the import set.

I have a import table, lets call it table_1 and on there I have fields, field_1, field_2, ... Based on the value of field_2 I want to set the target.location field which is a reference.


//Here we want to try using setDisplayValue which did not work
if (city.match(/columbus/i) != null || city.match(/worthington/i) != null || city.match(/dublin/i) != null ){
target.location.setDisplayValue("COLS") ;
}else if ( city.match(/wooster/i) != null ){
target.location.setDisplayValue("ATI");
}else if ( city.match(/lima/i) != null ){
target.location.setDisplayValue("LIMA");
}else if ( city.match(/newark/i) != null ){
target.location.setDisplayValue("NWRK");
}else if ( city.match(/mansfield/i) != null ){
target.location.setDisplayValue("MANS");
}else{
target.location.setDisplayValue("OTHR");

}

var city = source.u_city.toString();


//here we want to try using the "=" operator which also does not work.
if (city.match(/columbus/i) != null || city.match(/worthington/i) != null || city.match(/dublin/i) != null ){
target.location="COLS" ;
}else if ( city.match(/wooster/i) != null ){
target.location="ATI" ;
}else if ( city.match(/lima/i) != null ){
target.location="LIMA" ;
}else if ( city.match(/newark/i) != null ){
target.location="NWRK" ;
}else if ( city.match(/mansfield/i) != null ){
target.location="MANS" ;
}else{
target.location="OTHR" ;

}


My location field is a reference to the cmn_location table and the value I am using "COLS", "LIMA", etc, are the name field of location table. Do I need to lookup the object before trying to assign or use setDisplayValue?

My transformation was auto mapped aside from this one field. I tried defining a script for this field, I also tried a script for the transformation and neither semmed to work.

Thoughts? 🙂

[sherman_1206]

So I have tried again using a different data set. No luck but here is my situation and perhaps this is more complex. I have a table defining all locations where we have buildings, table for buildings and on that is a reference to campus, table for floor with a field referencing building and next table (the one in question) is room which will have a room number field and a reference to a floor in a building.

The import data I have contains a csv of room, floor, building name.

So I put together a simple script based on what I read on the wiki regarding transform scripts:

var roomNum = source.u_room;
var buildingName = source.u_name;
var floorNum = source.u_name 
log.info("Room  " + roomNum + " - " + floorNum + " - " + buildingName);
var sid = "";

var fl = new GlideRecord("u_building_floor");

fl.addQuery("u_floor", floorNum);
fl.addQuery("u_building", buildingName);
fl.query();

if (sgr.next()) {
sid = fl.sys_id;
} else {
// create it
fl.initialize();
fl.u_floor = floorNum;
fl.u_building = buildingName;
sid = fl.insert();


log.info("Created new floor: " + floorNum + " - " + buildingName);
}
//Finally we set the reference field on our record to the sys_id we have recorded.
target.u_floor = sid;
target.u_room = roomNum;

My thoughts here is that I want to take the current.target.floor/current.target.building and search the "u_building_floor" table. (This is the name of the table, that I am certain.) And I want to query that table for a entry having the floor and building name. Assuming this results in a find (it should assuming my data is correct for majority of entries) I want to take that fl.sys_id and assign it to target.u_floor.

The result of my transform was 40,600 give or take inserts which is correct that is how many rooms there are but none of the fields were populated with any data. (All fields are empty/null.)

Am I doing something wrong here or missing something?

I went to load data, selected my csv and loaded it in. Went to create transform map, picked target table (Building Room u_building_room) and checked the script box to run my script. Placed my script into the script area. Left order: 100, left created field empty, active checked, Run business rules checked, Enforce mandatory: no, Copy empty unchecked. (The way the transform form pops up by default.)

Appreciate any insight anyone has regarding this!!

[john_andersen]

I noticed a potential error in the beginning of your script. Both variables: buildingName and floorNum are set to the same value:


var buildingName = source.u_name;
var floorNum = source.u_name


Also, what type of transform script are you running? "onBefore", is probably the one you should be using for this type of work.

[sherman_1206]

I did catch the typo and here is the script I ended up with which I added as a transform script set to run "onBefore". I also had to have a field mapped otherwise transform was not an option. So I had u_room mapped to u_room and u_floor mapped to u_floor. Field name on target/source tables are correct. Aside from the script actually working, I am confused as to why I never got additional log messages from my script. I am getting is

log.info("Room  " + roomNum + " - " + floorNum + " - " + buildingName);

none of the other log messages are showing up. I wuold have expected

 log.info("Found Records: " + roomNum + " - " + floorNum + " - " + buildingName);

to have shown up as we did find floor/buildings and do the insert correctly.

var roomNum = source.u_room;
var buildingName = source.u_building_name;
var floorNum = source.u_floor 

log.info("Room  " + roomNum + " - " + floorNum + " - " + buildingName);
var floorID = "";
var buildingID = "";

var bl = new GlideRecord("u_building");
bl.addQuery("u_building", buildingName);
if(bl.next()){
buildingID = bl.sys_id;
var fl = new GlideRecord("u_building_floor");
fl.addQuery("u_floor", floorNum);
fl.addQuery("u_building", buildingID);
fl.query();
if (fl.next()) {
log.info("Found Records: " + roomNum + " - " + floorNum + " - " + buildingName);
floorID = fl.sys_id;
//Finally we set the reference field on our record to the sys_id we have recorded.
target.u_floor = floorID;
target.u_room = roomNum;
} else{
                log.info("fl.next failed");
        }
       
}else{

        log.info("bl.next failed");

}

The idea here is, a floor has a reference to building and we want our new room insert to haev a reference to floor which references the proper building. So first we get our room #, floor # and building. Next we query the building table to find the sys_id of the building. Then we want to query the floor record which has the floor # and building sys_id we just found. We look this up and then perform an insert.

All worked well!

[john_andersen]

If you were not getting any Log statemetns in your log other than the initial one, then something would leave me to believe that an exception is happening somewhere in your script. I would create log.info() statement and put it right under your log statement that worked. Run the transform and make sure that it prints the log statement. Then move the statement down one line and try again. Keep trying this until that log statement doesn't show up anymore. This will help you narrow down the point of error in the script.

[sherman_1206]

My thoughts too about a problem in the script however it worked which is why I am stumped. I will try this out though when I have some free time tomorrow.