Data import from excel to ServiceNow when duplicate values are present in SNOW

[Ksnow]

Hello all,

We would like to import the data from excel to ServiceNow table using Load data/transform maps, but the concern here is we have duplicate values in ServiceNow table.

For example: we have 3 fields in SNOW table, A, B, C. We need to import "C" field data into snow where as A & B fields data is already existing.

Record:1

A value has India.

B value has Bangalore

Record:2

A value has India

B value has Hyderabad

Record:3

A value has Germany

B value has Hyderabad

Record:4

A value has Germany

B value has Bangalore

Here I need to consider A & B values, since some cases both are not unique alone to impot C.

How can I do that in transform scripts?

Please assist.

Thanks!

Ksnow

[Ksnow]

Hello,

Thanks for your response,

I tried to coalesce both fields A and B but field C data is not imported correctly. Here I have no other option rather than checking the combination of A & B.

In target table records as below:

Record1: India & Bangalore, C is empty

Record2: India & Hyderabad, C is empty

Source table:

Record1: India, Hyderabad, StreetNo1

Street No1 has to be updated in Target table Record2 and Record1 should be left alone.

I don't want new records to be created in the Target table if there are extra records in source table. Need just "update"

As you asked for the names, you can give anything like

Source table:

u_country, u_city & u_street

Target table:

u_country, u_city & u_street

Thanks,

Ksnow

[-O-]

You did not say what is the type of fields u_country and u_city. Also if Country is "India" in the import set (source table), is it also exactly "India" in the target table?

[Ksnow]

Hello,

Let's say field names are u_country (string), u_city (reference), u_street (string) and yes same in source and target tables.

Thanks

[-O-]

Thanks! I need just one more piece of information: what does the table u_city references?

[-O-]

Here's something that worked for me:

Obviously you will have to adjust names where needed.

The important part the onStart Transform Script - this figures out the reference's display value field needed in the coalesce script:

// Figure out which one is the display field of the target table's City reference
var displayFieldName = ['u_city', (function onStart () { return gs.getDisplayColumn('cmn_location'); })()].join('.');

Here I'm assuming reference Street (u_street) on the target table "points" to table cmn_location. You need to adjust this if my assumption is not correct.

Another key part is the scripted Field map (in the green box in the screen-shot:

answer = (function transformEntry (source, map, log) {
	try {
		var sys_id = new global.GlideQuery(map.target_table)
			.where('u_country', '' + source.u_country)
			.where(displayFieldName, '' + source.u_city)
			.selectOne()
			.orElse({ 'sys_id': -1 })
			.sys_id;

		return sys_id;
	}
	catch (e) {
		log.error(e.message);
		ignore = true;
	}
})(source, map, log);

Of course here too you need to make sure the field names (u_country - source and target, u_city) are correct.

With help of these two scripts you should get what you needed.

Here's a screen-shot of the scripted coalesce field setup: