Transform map for reference field

[RohitGeorgV]

The excel has fields like Location Name, Location Zip, Location Street, Location City, Location State, Location Country. My target table is 'incident' and target field is 'location' which is a reference field to cmn_location. When loading the data for the incident from the excel, it should first check if the location is currently present in the instance and if not it should create a new one. So once transform is complete and when we open the incident record the location should populate the location and when we open that location record it should have the zip, state, street etc populated there. 

How to achieve this?

[GlideFather]

Hi @RohitGeorgV 

please create onBefore transform script in the related tab of your transform map and do a GlideRecord check for the Location and either accept it or create new

[Rafael Batistot]

Hi @RohitGeorgV 

Step-by-Step Setup

1. Load the Excel into Import Set Table

• Create/import an Import Set Table (e.g. u_incident_import).

• Fields: Location Name, Location Zip, Location Street, Location City, Location State, Location Country, etc.

---

2. Create a Transform Map

• Source Table: your import set (e.g. u_incident_import)

• Target Table: incident

• Check "Run script" and enable "Use source script" if needed.

---

3. Create a Script Include to Handle Location Lookup/Create

var LocationHelper = Class.create();
LocationHelper.prototype = {
    initialize: function() {},

    getOrCreateLocation: function(name, zip, street, city, state, country) {
        var loc = new GlideRecord('cmn_location');
        loc.addQuery('name', name);
        loc.query();

        if (loc.next()) {
            return loc.sys_id.toString();
        } else {
            loc.initialize();
            loc.name = name;
            loc.zip = zip;
            loc.street = street;
            loc.city = city;
            loc.state = state;
            loc.country = country;
            loc.insert();
            return loc.sys_id.toString();
        }
    },

    type: 'LocationHelper'
};

Use onBefore Transform Script in Transform Map

In your Transform Map, go to "onBefore" script and use:

var name = source.u_location_name;
var zip = source.u_location_zip;
var street = source.u_location_street;
var city = source.u_location_city;
var state = source.u_location_state;
var country = source.u_location_country;

var helper = new LocationHelper();
var locationSysId = helper.getOrCreateLocation(name, zip, street, city, state, country);

target.location = locationSysId;

****Replace the source.u_ field names with your actual import set field names.