Transforming a Comma Separated Field on CSV import

[Andrew Bettcher]

Hi,

Just working out how to ask this question has fried my noodle let alone actually solving it.

We take an extract from a 3rd party system and then push that through a series of transform maps to create or update contracts, assets and CIs in ServiceNow.

The problem is that the 3rd party tool doesn't allow for serialised product items. Now, we want to transfer serial numbers from the other system and make individual, separate asset records in ServiceNow.

I'm going to get a CSV extract with a series of lines on it. One of the columns is going to contain a list of serial numbers separated by a comma. For example, if we've sold 15 XPU servers, there will be a single line for XPU servers with a quantity of 15 and with a list of 15 serial numbers list in the Serial Number column. 

I need the import and transform to take that one line and create 15 asset records all with a unique serial number taken from that list.

I'm sure it can be done but I can't even imagine where to begin. I guess it would need a transform script to populate the import table based on the quantity field?

Any help here would be greatly appreciated.

[Michael Ritchie]

Happy to help.  I learned by the same way - I always look in the platform where something is similar to what I need and then copy, edit, and use.  Let me know if I can help further.

[Andrew Bettcher]

Michael. Sir.

You are a prince amongst men. 

I've got some work to do to get the records created like we want them but the test I did on 3 demo records I was using to import created 8 new asset records with the serial numbers that I had listed in the original string.

Brilliant.

[Michael Ritchie]

Sweet, glad to hear my example got you started.

[Andrew Bettcher]

Out of interest, what does this line actually do:

for(var i=0;i < serialNumberList.length;i++)

I've been trying to understand it. I understand that I can specify value in the array based on it's position starting at 0 but I haven't been able to work out how this line actually causes me to iterate through the array.

I know it's not for you to train me but I;d appreciate any information.

[Andrew Bettcher]

Hi,

I wanted to post the final script just in case anyone else needs help. Hat's off to Michael Ritchie who did the real work here and broke the back of the problem for me. The below is really just a tweaked version of the script that Michael provided.

The most important tweak is line 8 "ignore = true". Without this I got all of the serial numbers separated out as separate records AND the parent record as well. E.g. if I had 3 serial numbers in the string then I ended with 4 asset records:

	//if the quantity is greater than 1 a record is built per serial number seperated by comma's in the serial number field. Otherwise, the standard mapping is used.
	
if (source.u_quantity > '1'){
	ignore = true;
	var serialNumberList = source.u_serial_number.toString().split(",");
	for(var i=0;i < serialNumberList.length;i++){
		var serialNumber = serialNumberList[i];
		var asset = new GlideRecord("alm_asset");
		asset.serial_number = serialNumber;
		asset.company = source.u_company_name;
		asset.asset_tag = source.u_description;
		asset.model = source.u_display_name;
		asset.lease_id = source.u_id;
		asset.install_date = source.u_install_date;
		asset.u_unique_ref = source.u_line_unique_key;
		asset.location = source.u_memo__main_;
		asset.u_asset_number = source.u_name;
		asset.u_quantity = 1;
		asset.ci = source.u_unique_ref;
		asset.insert();
	}
} else {
	var asset = new GlideRecord("alm_asset");
	asset.serial_number = source.u_serial_number;
}}

Other than that, all I've done is replicate my standard map in code form and made sure that the quantity is set to 1 because the asset is unique after the transform.

I still have an issue because the u_unique_ref should be unique and I use that coalesce. The result of this script, however, is a set of unique records but with the same unique ref.....

I have an idea of how to get around this by concatenating some fields to create a uniqueness but I'll have to think about how I then use that concatenation to coalesce. That will be a different post.