Transform Maps Coalesce reference field.

[KaMmILa__]

I have a data source where we are importing data from excel file Source table is a Temp table and Target table is a custom group vendor table that has only 2 fields (name and access) and 2 reference field which are referencing cmn_location table (country and location)

as of now, I am coalesce on name and access , I also want to coalesce on country and location . can some one help me with the code or solution ?

[Mario Quinones1]

On the onBefore script, you can use the source, target fields

if ((source.country == target.location.country) && (other fields) )

ignore records if they are the same

[KaMmILa__]

This is not working , is there any issue with the code ? ,No insert , noupdate , no ignore 

what i need is if all the 4 fields match then we need to update " Inherits from"  field. I also have colase in the field maps for 2 fields . where exactly we are defining where to update ?.

[Harsh Vardhan · Accepted solution]

try with below script and put on field mapping script to get the location unique value . 

First identify which column will be always going to be unique , apply the coalesce on that column, if you have reference field that will always going to be unique, apply that on reference field. 

Note: this is a sample code, but you can make the changes based on your need.

answer = (function transformEntry(source) {

	var comp = new GlideRecord('cmn_location');
	comp.addQuery('name', '=', source.u_location);
	comp.addQuery('state', '=', source.u_state___province);
	comp.addQuery('country', '=', source.u_country);
	comp.query();
	if (comp.next()) {
		return comp.sys_id;
	} 
	
})(source);

[Monish2]

Hi,

I have a similar use case where I have 4 reference fields all of them to be used as coalesce. where should I add this script? on all the 4 fields? or just one field?

thanks,

Monish