Transform Map Source field script returning UNDEFINED for sys_id

[Camila Godoy]

Hi team

I am getting these source values from an excel

Room	Location
Room123	NY

Using a transform to generate the data.

The field map source consists on the script below.

The target table source is a location field with reference to cmn_location.

The reason I need the script below is because we can have Room123 in different cities.

However, when I ran the transform map the location received UNDEFINED value.

Note that I am testing with data that source matches location.

answer = (function transformEntry(source) {

var l = new GlideRecord('cmn_location');
var qc = l.addQuery('full_name', 'CONTAINS', source.location);
qc.addOrCondition('state', 'CONTAINS', source.location);
l.query(); //Execute the query

while (l.next()){
if (l.name==source.room){
return l.sys_id; // return the value to be put into the target field

}

}

})(source);

 Please let me know if you have ran into this.

more info of the target field.

thank you!
Camila

[Ankur Bawiskar · Accepted solution]

@Camila Godoy 

update script as this

Ensure you use correct field names from source table; As per my understanding it should be u_room, u_location

answer = (function transformEntry(source) {

	var sysId;
	var l = new GlideRecord('cmn_location');
	l.addQuery('name', source.u_room);
	var qc = l.addQuery('full_name', 'CONTAINS', source.u_location);
	qc.addOrCondition('state', 'CONTAINS', source.u_location);
	l.query(); //Execute the query
	if (l.next()){
		sysId = l.sys_id;
	}
	return sysId;

})(source);

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Camila Godoy]

Thank you so much!! You were right. I was naming the source wrong.

here is my final code.

answer = (function transformEntry(source) {
	var sysId;
	var l = new GlideRecord('cmn_location');
	var qc = l.addQuery('full_name', 'CONTAINS', source.u_location);
    qc.addOrCondition('state', 'CONTAINS', source.u_location);
    l.query(); //Execute the query
    
while (l.next()){
	if (l.name==source.u_room){
				sysId = l.sys_id;
	}
	}
	return sysId;	// return the value to be put into the target field
	
})(source);

[Ankur Bawiskar]

@Camila Godoy 

Glad to help