Transform Map Script question

[shane_davis]

I have a transform that runs on the sys_user table.  The transform populates the location field (name value) which, of course, is a reference to cmn_location.  My problem is that some records in cmn_location are duplicate names, but one of the duplicates has longitude and latitude information so that's the record I always want bound to my user.  Note that my imported data does not contain the long/lat so that is why I'm looking at manually entered long/lat data on the cmn_location table.

Example - Duplicate Locations:

 I created an OnBefore transform script within the sys_user transform map, but my code isn't exactly right.  Can someone help please?  Ultimately, while the sys_user transform is running, the script needs to look at the location being populated for the user and only add the location from the cmn_location table that contains a longitude OR latitude.

var loc = new GlideRecord('cmn_location');
loc.addEncodedQuery('longitudeISNOTEMPTY');

loc.query();
if (loc.next()){
	// loc.update();
	target.location();
}

[DScroggins]

Hello,

Please try the following:

var loc = new GlideRecord('cmn_location');
loc.addEncodedQuery('latitudeISNOTEMPTY^longitudeISNOTEMPTY');
//Assumes your source location field is the name of the location
loc.addQuery('name',source.u_location);
loc.query();
if (loc.next()){
	
	target.location = loc.getUniqueValue();
}

[shane_davis]

David,

     Thank you for your quick reply.  I've changed to your script and have been testing it, but the location bound to my user is still the one without the lat/long information.  I can't find anything that would be causing the script not to work.  To check, I even deleted all of the records in the temp import table and then reran the import; same result.  Any idea why it might not be working?

     I appreciate your help.

Shane

[DScroggins · Accepted solution]

Hi Shane,

Instead of an onBefore Transform Script can you instead try the following in the field map for the location field in your Transform map:

In Field map check "Use Source Script"

Then in script field put the following:

answer = (function transformEntry(source) {

	var ret = '';
        var loc = new GlideRecord('cmn_location');
        loc.addEncodedQuery('latitudeISNOTEMPTY^longitudeISNOTEMPTY');
        //Assumes your source location field is the name of the location
        loc.addQuery('name',source.u_location);
        loc.query();
        if (loc.next()){
	
	ret = loc.getUniqueValue();

        }

        return ret; // return the value to be put into the target field
	
	
})(source);

[shane_davis]

David,

     That worked perfectly.  Thank you very much for your help.  I really appreciate it.  It's easy to understand once it's written, but writing from a blank slate is a different story!  So much to learn. 

Shane