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

[Ankur Bawiskar]

Hi,

your task is to parse the excel row by row but you are just creating it directly

variables are not required for this RITM?

Regards
Ankur

[Anish Reghu]

Hi Ankur,

Thanks. No variables are required.

The RITM could hold one variable for name sake asking whether you want to create new users? Yes/No.

And if yes, attachment becomes mandatory and on attaching the excel and on submission, RITMs must be generated, filling in the description with the row values parsed from it.

Regards,

Anish

[Anish Reghu]

I need help on this line of code as well:

var attSysID = <NEED_HELP_TO_READ_ATTACHMENT_SYS_ID_FROM_REQUEST_SUBMITTED>;

//Here the intent is to fetch the sys_id of the attachment
// in the request that got generated, which would fetch the attachment,
// to be processed in the below lines of code.

Guess you overlooked this portion where I am in need of syntax to fetch the attachment sys_id. So, I am not creating the attachment or the rows here.

Regards,

Anish

[Anish Reghu]

I think I got it. I was confused by the table name populated with prefix ZZ_YY on the sys_attachment table.

But, when I attached an excel in a REQ - it clearly showed table name as sc_req.

** IGNORE THE ABOVE SAID, JUST SHARED A CONFUSION **

So, the missing code would be:

// var attSysID = <NEED_HELP_TO_READ_ATTACHMENT_SYS_ID_FROM_REQUEST_SUBMITTED>;

// CAN BE REPLACED WITH THE FOLLOWING TO FETCH THE ATTACHMENT.

attachment.addQuery('table_sys_id',current.getUniqueValue());
attachment.setLimit(1);
attachment.query();
while(attachment.next())
var attSysID = attachment.sys_id

// And then continue with the rest of the code shared above