How to read the attached excel file from workflow.

[Munna1]

Hi All,

After raising the request I need to read the attached excel file through workflow ?and I need to set conditions for it. How can I achieve this any suggestions please..

[Karan Bhatia1]

Hi Munna, 

You can use 'Run Script' to obtain values in the Excel sheet. 

Below script will obtain the headers of the excel sheet.

var gr = new GlideRecord('sys_attachment');
gr.addEncodedQuery('table_sys_idSTARTSWITH'+current.sys_id);
gr.query();
if(gr.next()){
var parser = new sn_impex.GlideExcelParser();
var attachment = new GlideSysAttachment();
var attachmentStream = attachment.getContentStream(gr.sys_id);
parser.parse(attachmentStream);
var headers = parser.getColumnHeaders();
gs.print(headers.toString());
gs.print(headers[0]);
gs.print(headers[1]);
}

What do you mean by 'set conditions for it'?

Regards,

Karan

[Munna1]

Hi Karan,

Thanks for your response .

I need to set the below condition to satisfy the attached records.

In excel sheet I have only one column called serial number. I need to check that in the Hardware table is if the passing serial number need to satisfy this condition    ->     'u_support_owner = '+current.variable.select_group+'^state=6^substate=pending^u_vendor=false'.

If the serial number is not satisfied the above condition then not satisfied number need to be displayed in worknotes.

Thanks

[Munna1]

Hi Karan,

I am passing serial numbers through attachment while raising request, Here I want to check that serial numbers in hardware table. If the numbers are present and satisfies the condition mentioned in IF block.  If not satisfies it has to populate the serial numbers in worknotes . I am trying with the below run script code but it's not working. Any suggestion please.

var gr = new GlideRecord('sys_attachment');
gr.addQuery('table_sys_id', current.sys_id);  

if (gr.next()) {
var parser = new sn_impex.GlideExcelParser();
var attachment = new GlideSysAttachment();
var attachmentStream = attachment.getContentStream(gr.sys_id);
parser.parse(attachmentStream);
var headers = parser.getColumnHeaders();
var coloumn = headers[0];
while (parser.next()) {
var inStock = false;
var row = parser.getRow();
gs.log(row[coloumn] + "@@@");
//The query on the hardware table
var grHardware = new GlideRecord('alm_hardware');

if( grHardware.addQuery('serial_number', row[coloumn]));//to check if serial number is found pass the serialnumber
{

if('u_support_owner='+current.variables.select_group+' && install_status=6 && substatus=pending_disposal && u_vendor_disposed=false')  //If serialnumber is present in a table then it has to satisfies this condition

}
else{       // If not satisfies then it has to display serialnumber in requested item worknotes.

grHardware.setValue('work_notes',row[coloumn]);

}
grHardware.query();
if (grHardware.hasNext()) {
inStock = true;
}
}
}
}