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]

My formatting came out a bit off, how can I edit this post? Or can a moderator fix the formatting? Thanks!

[john_andersen]

That should work. What type of transform map script are you using? onBefore? onAfter? Field transform script?

[sherman_1206]

I tried a field transformation script and also tried onBefore I believe. No matter what I tried, the value ended up empty. The "else" case was never hit it seems which leads me to believe I have a problem in my script or something. Was not hard to manually update as most had the same value but would be good to learn for future imports.

[john_andersen]

Yeah, that should have worked for you, but like you said, it probably is a problem in the script itself. I would use an onBefore script so that the changes you make to "target" will be saved. If you use "onAfter" you will need to perform an update to "target" for the values to be saved.

Also, another thing to watch out is if your mapping already contains a map to the target.location field, then it may be overwriting what is done in your onBefore script. Be aware of that.

I hope these steps help you in finding your problem.

-John