Transform Maps Coalesce reference field.

KaMmILa__
Tera Expert

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 ?

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

View solution in original post

13 REPLIES 13

MrMuhammad
Giga Sage

Hi,

You can set the coalesce to true for country and location. Also, make sure to specify the field name with which you want the supplied value to be compared in the field "Reference Value field name". For example, if I want my supplied location name to be compared with “city” and not “display name” then in reference value I will give value as "city".

Hope that helps!

Regards,

Muhammad

Regards,
Muhammad

Hello Muhammad 

Thanks for the reply, but the location and country fields are not displayed in the target table mapping dropdown

Do these fields exist on the target table? 

Regards,
Muhammad

the field is not available on the table but I added that on the list layout and can see those on list view , we will export that list as xml and when I check the target table I cannot see those fields

find_real_file.png

find_real_file.png