How to script a transform map to populate a different target field when no reference match found

[Jacob Nan]

I'm hoping someone can help with me this. I'm importing a large data set where one of the fields is mapped to field referencing the user table. There is a possibility that instead of a user it's a location. I would like to create a script that if no matching reference is found, change the target field. Do I need to make an onBefore script to query the table for any values matching the source field?

Thank You

[Tony Chatfield1]

Hi, normally I would use a before transform script, and use that to map any fields that have dependencies or that might change. Lookup reference table, return  result to the script then check the result, if expected map the value, if not you can insert or map another value.

[Tai Vu · Accepted solution]

HI @Jacob Nan 

Yes let's define a OnBefore Transform Script, then you can do a query to User and Location table. If the record found, we'll set the value to the target field.

Sample below.

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

	var grUser = new GlideRecord('sys_user');
	grUser.addQuery('user_name', source.getValue('<your_source_field_name>')); //use email or user_name according to your source value.
	grUser.query();
	if(grUser.next()){
		target.<your_target_user_field_name> = grUser.getUniqueValue(); //replace field name
	}

	var grLocation = new GlideRecord('cmn_location');
	grLocation.addQuery('name', source.getValue('<your_source_field_name>')); //use location field name according to your source value.
	grLocation.query();
	if(grLocation.next()){
		target.<your_target_location_field_name> = grLocation.getUniqueValue(); //replace field name
	}

})(source, map, log, target);

Cheers,

Tai Vu