Scripting in the Transform Map to Map Field

[Vamsi26]

Hello Everyone,

I want to set the Delivery time with the value of the Catalog Item If import set row column is empty on the sheet.

Excel Sheet:

Table: 

Transform Map:

What needs to be done further to achieve "to set the Delivery time with the value of the Catalog Item If import set row column is empty ???"

Thanks

[Community Alums · Accepted solution]

Hi @Vamsi26 ,

As I understand: You have a custom table u_catalog_reference. You want to load data in that table by importing Excel. When you import an Excel file and map the Catalog item and Delivery time field, If Delivery time is empty in the Excel, it should set Delivery time from the sc_cat_item table for current catalog item.
For e.g. Catalog item = 'Access' and Delivery time is empty in Excel.

It should Glide the sc_cat_item table with condition catalog item = 'Access' and set its Delivery time in the u_catalog_reference table run time.

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

		if (JSUtil.nil(source.getValue('delivery_time')) {
				var catalogItemGR = new GlideRecord('sc_cat_item');
				catalogItemGR.addQuery('name', source.cat_item); //please verify backend name of cat_item from the source table
				catalogItemGR.query();

				while (catalogItemGR.next()) {
					var deliveryTime = catalogItemGR.getDisplayValue('delivery_time');
					//gs.info(catalogItemGR.getDisplayValue('category')+' Delivery Time: ' + deliveryTime);
					target.delivery_time = deliveryTime;
				}
		}

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

 Above code will check if the delivery_time field is empty in the source table. If it is empty, the code retrieves the delivery_time value from the related sc_cat_item table based on the name field in the source table. The retrieved delivery_time value is then assigned to the delivery_time field in the target table.

Note: Above code will set Delivery time in this format: "<<No. of days>> Days <<No. of hours>> Hours". e.g. 2 Days 20 Hours
If you want to set Delivery time in this format:  "<<No. of days>> Days". e.g. 2 Days
Please replace the below line in the above code:

var deliveryTime = catalogItemGR.getDisplayValue('delivery_time').split(' ')[0] + ' Days';

 
Please hit the like/helpful button if this helped you in any way.

thanks,

prasad