How to compare string field with reference field in transform map?

[Sathwik1]

I have a field in Excel named "Schedule" it maps with "Schedule" in cmn_schedule_span table...

so here my issue is...

Suppose in Excel If I provide like "India Schedule" while transforming it is referring to "Schedule" which is a reference field due to this my transform map is not working...

so can anyone please explain how can we compare a string with reference field in transform map 

[Sanket10]

Hi @sathwik 

I did the same with location data from XML and location column on sys_user table, the location field on user table is reference field from cmn_location table. But id doesn't get any error for mapping.

Please try manual mapping for fields, if you tried the auto-mapping option.

Mark helpful if it works for you.

Thanks,

Sanket

[Tony Chatfield1]

Hi, if the ‘name’ field is unique then you can map the reference record simply with a glide get in a before transform script. Something like

var myRecord = new GlideRecord(‘tableName’)

if(myRecord.get(‘nameField’, source.stringName)) {
target.referenceField = myRecord;
} else {
//log an error;
}

[Ankur Bawiskar]

Hi,

Since your target field is reference you should be passing the unique sys_id

Use field map source script for this

Referenced value field name - sys_id

Script:

answer = (function transformEntry(source) {

	// Add your code here
	var incomingValue = source.u_schedule;
	var gr = new GlideRecord("cmn_schedule");
	gr.addQuery("name", incomingValue);
	gr.query();
	if (gr.next()) {
		return gr.getUniqueValue();
	}
	else{
		return -1; // don't allow update
	}


})(source);

Regards
Ankur

[Sathwik1]

@Ankur Bawiskar did the same ankur but still everytime the schedule is getting updated...

i.e., First time I ran the transform map, fields get updated...so now the schedule is with new data...

so if I run transform map again then no fields should updated because I am running the same data again...

in my case 12 are get updated only 1 get ignored...

please help ankur, I was not able to understand it trying from last 24 hours....attaching the screeshot