Transform Maps Coalesce reference field.

KaMmILa__ · ‎02-10-2021

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 ?

Harsh Vardhan · ‎02-12-2021

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

View solution in original post

Mario Quinones1 · ‎02-10-2021

You cant coalesce on those fields because they dont exist on the target table, they are only displayed on the form based on the value from the location field

Coalesce on the location field

MrMuhammad · ‎02-10-2021

Coalesce can only be applied to fields existing on the target table. In your case, you are dot walking Location.State/Province and Location.country that cannot be used as coalescing.

You can only use the Location reference field for coalescing in this case as Mario mentioned.

Regards,
Muhammad

Tony Chatfield1 · ‎02-10-2021

Hi, my expectation would be that reference fields are available for coalesce in a transform map.
You can confirm this by looking at the OOB transform map eg user,

nav_to.do?uri=sys_transform_entry.do?sys_id=9bf12f70db922010737a8a1848961981

In this transform reference fields that point to other tables like ‘Company’ are available for selection\coalesce in the Field Map list.

As per Muhammad’s comments, can you check\confirm that Country and Location exist on the target table

Mario Quinones1 · ‎02-10-2021

Check with the Mapping assist to be sure the fields are there, if they don't show, do an onBefore transform script

KaMmILa__ · ‎02-10-2021

can you please help me with script