Create RITM from REQ records on submitting REQ with Excel file.

[Anish Reghu]

SCENARIO:

A user submits a request from a catalog item, which results in a REQ record. While submitting the request, the user uploads an excel file, which contains the value to be parsed and populated into the RITM.

Note - Each row in the excel has to generate a new RITM.

Following has been my approach:

- Validate if the REQ submitted has attachments in the Condition field.

current.hasAttachments();

Excel Content-Type is application/vnd.ms-excel - how to validate? - Then the script to create the RITMs:

var grRITM = new GlideRecord("sc_req_item");
grRITM.initialize();

var parser = new sn_impex.GlideExcelParser();
var attachment = new GlideSysAttachment();
var attSysID = <NEED_HELP_TO_READ_ATTACHMENT_SYS_ID_FROM_REQUEST_SUBMITTED>;

var attachmentStream = attachment.getContentStream("attSysID");
parser.setSource(attachmentStream);
var list_sheet_name = parser.getSheetNames();
parser.setSheetName(list_sheet_name[i]);

var j = 0;                           //Row counter
var rowVal = [];                       //Array for fetching rowValues

// Parse each worksheet set using setSheetName()
    if (parser.parse()) {

        //retrieve the column headers, assuming excel has only 3 columnns.

        var headers = parser.getColumnHeaders(); 
        var h1 = headers[0];
        var h2 = headers[1];
        var h3 = headers[2];

        // Iterate over each row in the worksheet

        while (parser.next()) {
            j++;
            var row = parser.getRow(); 
            rowVal[j].push(row[h1], row[h2], row[h3]);
        }
    } else
        gs.info(parser.getErrorMessage());

// return rowVal;
}

Now, I want the RITM initialized to be saved with the description populating these extracted values from the excel.

grRITM.description = 
"Input Label 1:"+rowVal[1].row[h1];
+"\nInput Label 2:"+rowVal[2].row[h2];
+"\nInput Label 3:"+rowVal[3].row[h3];

This is with the assumption that the values are in the excel in the below seen format:



Kindly suggest, where I have made a mistake and what better should I do to get this work?

Regards,

Anish

[Anish Reghu]

@Ankur Bawiskar/ anyone else available -  finally, I made my code, can you check if you find anything wrong below:

(function executeRule(current, previous /*null when async*/ ) {

    // Add your code here
    var grAtt = new GlideRecord("sys_attachment");
    if (grAtt.content_type == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") {
       
		gs.log("Validated content type");
        var grRITM = new GlideRecord("sc_req_item");

        var parser = new sn_impex.GlideExcelParser();
        var attachment = new GlideSysAttachment();

        /******* FETCHING ATTACHMENT SYS ID UPLOADED ON REQUEST ******/

        attachment.addQuery('table_sys_id', current.getUniqueValue());
        attachment.setLimit(1);
        attachment.query();
        while (attachment.next())
			{
            var attSysID = attachment.sys_id;
            gs.log("Attachment Sys ID is fetched");
			}
        
		var attachmentStream = attachment.getContentStream("attSysID");
        parser.setSource(attachmentStream);
        var list_sheet_name = parser.getSheetNames();
		for (var i = 0; i < list_sheet_name.length; i++) {
              parser.setSheetName(list_sheet_name[i]);

        var j = 0; //Row counter
        var rowVal = []; //Array for fetching rowValues

        // Parse each worksheet set using setSheetName()
        if (parser.parse()) {

            //retrieve the column headers, assuming excel has only 3 columnns.

            var headers = parser.getColumnHeaders();
            var h1 = headers[0];
            var h2 = headers[1];
            var h3 = headers[2];

            // Iterate over each row in the worksheet

            while (parser.next()) {
                j++;
                var row = parser.getRow();
                rowVal[j].push(row[h1], row[h2], row[h3]);
                
				grRITM.initialize();            // Open a new RITM record

                grRITM.description =
                    h1 + rowVal[1].row[h1]; +
                h2 + rowVal[2].row[h2]; +
                h3 + rowVal[3].row[h3];          // Fill the description

                grRITM.request.setValue(current.number); // Link RITM with request
                grRITM.update();                 // Save the RITM
            }
        } else
            gs.info(parser.getErrorMessage());

        
    }
	}

})(current, previous);

Cheers,

Anish

[Anish Reghu]

ExceltoRITM_AfterBR_WithoutCondition_OnREQTable

Can someone please review the above code in the link and tell me why RITMs are not getting created.

The excel values can be read and if I place the RITM creation (the for loop) within the while loop, one RITM gets created with Delivery date as empty.

@Ankur Bawiskar ?

Regards,

Anish

[Anish Reghu]

I am here right now:

All excel rows are read:

Generates only one RITM instead of three: (in total 4 including the default RITM)

The delivery date being blank above is fine, it is due to the workflow that is not triggered for the RITMs created via this script.

And then it gets me the values of the 1st row in that script generated RITM:

How do I fix the script to get the next RITMs generated?

Regards,

Anish

[Anish Reghu · Accepted solution]

It's done. https://code.jace.pro/?id=2022-05-08T12-04-05

Thanks Ankit for your help.

Cheers to all readers,

Anish

Slowly learning to Script in ServiceNow. 😉

[Vivi Brasil]

Hello Anish,

Would you mind to confirm if the link above is working, please?

I was looking for something similar to it.

So, just in case you could confirm if the URL is ok.

Thanks in advance!

Vivi Brasil