I am trying to import via Load Data from an excel file.
My source data (excel file) has the following fields:
| Acquisition method | Asset tag | Cost | Functional department | Installed | State | Building | Model | PO number | Serial number | Support group | Entity | Last location | Owning Department | Property Code | Under Warranty | Warranty expiration |
I created a transform map to load these to the sn_ent_medical_asset table
Everything is loading fine EXCEPT for the Building.
In the source file the building is a string
I also have a property number which references the building on the cmn_location table.
On the target there is a field labeled Building but it is actually a reference field to cmn_location and field name is location.
When i complete the data import the Location field is blank.
I have tried - mapping directly
Building to Building (Location) using the name (which matches the entry on the Location table, is active and will match if searched (but does have more than one match)
Mapping using the property code on the source to the Building(Location) on target and using the u_property_number as the referenced field value (have confirmed that this matches and will always be unique)
Using a script to do a lookup with a combination of the two above
answer = (function transformEntry(source) {
// Add your code here
var gr = new GlideRecord('cmn_location');
gr.addQuery('name',source.u_building);
gr.addQuery('u_property_number',source.u_property_code);
gr.query();
if(gr.next()){
return gr.sys_id; // return the value to be put into the target field
}
})(source);
