Transform Map Script question

shane_davis · ‎02-05-2019

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 · ‎02-05-2019

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);

View solution in original post

DScroggins · ‎02-05-2019

You are welcome Shane. Glad that I could help. 🙂

asifnoor · ‎02-05-2019

Hi i just changed this code. check this once.Try this code

var loc = new GlideRecord('cmn_location');

loc.addEncodedQuery('latitudeISNOTEMPTY^longitudeISNOTEMPTY');

loc.addQuery('name',source.u_location);

loc.query();

if (loc.next()){

return loc.getUniqueValue();

}

shane_davis · ‎02-05-2019

asifnoor,

I tried your script, but it gave the error when I tried to save it. The last script given by David worked, but I do appreciate your help.

"Could not save record because of a compile error: JavaScript parse error at line (6) column (8) problem = invalid return (<refname>; line 6)"

Shane

asifnoor · ‎02-05-2019

Ah okay. not a problem, as long as the problem is solved, thats good.

dvp · ‎02-05-2019

I have seen issue when using addQuery in combination with addEncodedQuery

Can you try this

var loc = new GlideRecord('cmn_location');
loc.addNotNullQuery('latitude');
loc.addNotNullQuery('longitude');
loc.addQuery('name',source.u_location);
loc.query();
if (loc.next()){
	
	target.location = loc.sys_id;
}