Transform Map Script to use the right asset

[Andrew Bettcher]

Hello,

I know that this is complicated and long. Please bear with me. I'll try to make it entertaining to read so you can make it through......

We import a lot of data from our CMS system (Netsuite) into ServiceNow. We don't have any interim connectors and so we simply send a scheduled email from Netsuite to ServiceNow with a report attached (CSV) and then using the magic of @Michael Ritchie's process (here) we load it and transform.

All is well. 

Kind of.

The bulk of the data concerns contracts and the assets covered by those contracts. That  works fine until a contract expires and is renewed. For technical reasons when we create a new contract in Netsuite we don't simply move the same asset records across to the new contract. We create them again as new records. This is so we can have a unique reference number per record for the import.

At midnight on the day that a contract expires I run a scheduled job that retires all of the associated assets in preparation for the morning import of a new contract and new assets. The assets have the same serial number in some cases but a different unique ID.

Then, a little bit after that, I export any new assets back into ServiceNow (in a weird kind of loop) in order to populate the "assets covered" table (I found that I couldn't do it all in one hit as the records don't exist to create the link during the import). So a scheduled report of all updated or new assets is sent to a mailbox which forwards it back to ServiceNow which them transforms the data into the assets covered table.

Phew. 

That works for the most part but in some cases, when it populates the asset field it had a choice to make between the existing "retired" asset and the new "in use" asset. 

This is where it gets really complicated. I have a script that looks for assets without a valid serial number and, in that case, it forces the use of the unique reference to find the right asset:

(function transformRow(source, target, map, log, isUpdate) {

	//spots import records without a serial number

	if (source.u_serial_number == '' || source.u_serial_number == '+' || source.u_serial_number == '1' || source.u_serial_number == '.' || source.u_serial_number == '/' || source.u_serial_number == '..'){
	ignore = true;
	
	//checks if the the asset covered record already exists to prevent duplication (e.g. coalesce)
		
	var assetCovered = new GlideRecord('clm_m2m_contract_asset');
	assetCovered.query('u_unique_ref', source.u_unique_ref);
	assetCovered.query();
	
	if (assetCovered.next()){
		gs.log(assetCovered.sys_id + " covered");
	}
	
	else{
		
	//identifies the asset record that corresponds to the unique ref 
	
	var asset = new GlideRecord('alm_asset');
	asset.query('u_unique_ref',source.u_unique_ref);
	asset.query('install_status','!=', 7);
	asset.query();
		
	while (asset.next()){

	var sys_id = asset.sys_id;	
		
	//creates new aset covered record using unique ref to link to asset rather than SN.
		
	var assetcovered = new GlideRecord('clm_m2m_contract_asset');

	
	assetcovered.initialize();
	assetcovered.u_asset_number = source.u_asset_number;
	assetcovered.setDisplayValue('asset', sys_id);
	assetcovered.setDisplayValue('contract', source.u_lease_id);
	assetcovered.setDisplayValue('added',source.u_sys_created_on);
	assetcovered.u_unique_ref = source.u_unique_ref;
	
	assetcovered.insert();
//}
	}
	
}}})(source, target, map, log, action==="update");

and then creates a new asset covered record. As I understand it, if the script is discounted it then goes on to use the standard transform map that I have created. So, any item with a serial number will populate the asset covered asset field using the serial number.

(Sorry to add layers of complicated to this but in some cases the unique ID isn't unique. When there are multiple devices of the same type they come to me as one record with the serial numbers separated by a comma. I pull these out and create as many separate records as there are serial numbers BUT THEY ALL HAVE THE SAME UNIQUE REFERENCE!!).

In order to try and fix this I put an onBefore transform script in that, I think checks that the contract isn't expired before going ahead and ignores if it is:

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

var asset =  new GlideRecord('alm_asset');
	asset.query('u_unique_ref',source.u_unique_ref);
	asset.query();
	
	while(asset.next()){
		if (asset.install_status == 7){
			ignore = true;
		} 
		
	}


})(source, map, log, target);

Callooh! Callay! Oh frabjous day!! I thought it worked

But it didn't. It did ignore a load of undesirable rows but it also went ahead and imported 5 rows of assets using the serial number and it chose the wrong the wrong record out of a possible two (i.e. "in use" and "retired").

After all that, finally, the question is, how can I get it to only use the "in use" asset when there is a choice of records in a reference field that is being created by a transform map?

Anyone who has read this far gets a medal for commitment even if you don't post a response. Thank you.

[Michael Ritchie · Accepted solution]

Andrew, wow this is quite a process!  And yes I made it all the way through the post.

Have you considered just creating an onStart transform script instead of using the normal field maps, etc.  While scripting can be overwhelming to many, it can simplify things too once you know what you are doing.  I ask this because you could do everything in a single script after the spreadsheet is loaded from the email.  Via script you could process row by row evaluating the data to ensure it creates the right asset along with the Assets Covered M2M record.

[Andrew Bettcher]

Hi Michael,

Thank you for the response. I didn't know I could do that. Or, at least, if I'd thought about it I would have realised but always thought I should use the mapping structure built in.

OK. That is a good idea and something I will work on. My scripting skills are coming along nicely and so it's good to have something challenging to work on.

I think I've more or less sorted the issues I had after starting from scratch but have a small coalesce problem that I will fix today. Then I'll work on this as an improvement.

Thank you again.

[Michael Ritchie]

Andrew no worries, happy to help!  There are some out of the box example transform maps that are all script based.  Some of the LDAP ones are and the old AD software import was as well.  Script based ones actually perform faster than ones setup with field maps because as you can imagine there is overhead involved in querying the database for those maps and cycling through them versus just executing a script.

Glad you got the issue worked out.