Scripting in the Transform Map to Map Field

Vamsi26 · ‎07-05-2023

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 · ‎07-06-2023

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

View solution in original post

Community Alums · ‎07-05-2023

Hello @Vamsi26 ,

To set the Delivery time in the target table(u_catalog_refference) if it is empty in the source table(table created after importing the Excel file) you can use a transform map script, you can follow these steps:

Open the Transform Map associated with the import.
Go to the Transform Scripts-related list.
Click on --New-- button.
Create the "OnBefore" script, and enter the following script:

(function(source, map, log, target) {
  if (JSUtil.nil(source.getValue('u_delivery_time'))) {
    target.u_delivery_time = "2 day"; // set value here
  }
})(source, map, log, target);

Now, when you import an Excel file and map the Delivery time field, the script in the Transform Map will check if the Delivery time is empty in the source table. If it is empty, it will set the value which you configure in the script.

Note: Make sure to adjust the field names (u_delivery_time and target) in the code according to your actual field names in the transform map and target table.

Hit the like button if this helps you in any way. So, that it will help others to find the correct solution!

regards,

Prasad

Vamsi26 · ‎07-05-2023

Hello @Community Alums

The scripting is setting "2 days" for all the records. There are items with different days 1 day, 3days , 5days.

How to check the conditions of Catalog item "delivery_time" ??

Script:

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

    if (JSUtil.nil(source.getValue('delivery_time'))) {
        target.u_delivery_time = "2 days"; // set value here
    }

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

Thanks

Community Alums · ‎07-05-2023

@Vamsi26 ,

If I got you correctly, you want to set the value of delivery time on the basis of the Catalog item.

If yes,

For e.g., If the Catalog item is 'Software' then the Delivery time should be '2 days',

If the Catalog item is 'Hardware' then the Delivery time should be '3 days' and so on.

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

    if (JSUtil.nil(source.getValue('delivery_time'))) {
        if (source.catalog_item == 'Software') {
            target.u_delivery_time = "2 days"; // set value here
        } else if (source.catalog_item == 'Hardware') {
            target.u_delivery_time = "3 days"; // set value here
        } else {
            target.u_delivery_time = "4 days"; // set value here
        }
    }

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

Note: You can use a switch case for the above scenario. This might help you: How to use a switch case?

If No,

Please correct me!

thanks,

Prasad

Vamsi26 · ‎07-06-2023

@Community Alums

If the Excel sheet Delivery time is Empty then it should check the "Delivery Time from the Catalog item" and set the Target value on the table.

I written the script like this but it's not working.

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

    if (JSUtil.nil(source.getValue('delivery_time') == "02 00:00:00")) {
        target.u_delivery_time = "2 days"; // set value here
    } else if(JSUtil.nil(source.getValue('delivery_time') == "03 00:00:00")) {
        target.u_delivery_time = "3 days"; // set value here
    }

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

Thanks