Hi All,
I was thinking, if there is any way to allow upload a CSV or Excel format files to a catalog item which need to be populate a multi-row variable set (MVRs) on the same catalog item.
We have created a MRVS on the hardware disposal catalog item:
Use case:
Most of the time this is fine as the user will only need to add a 5 to 10 records at time. However there are certain times where we need to add multiple rows i.e (500 to 1000) records many rows to be added. So, we have certain templates with those data types in a Excel and CSV formats with all the row information contained. They have asked if in these cases this could attach the template which would be added to the MRV variables?
Is this possible? Or any other suggestions for bulk upload to a MRV?
Any help is greatly appreciated
Thanks
Sagar Mukkamula
I have done something similar with a UI Action. Our use case is a MRVS that is populated manually, then the task processors export the MRVS via a UI Action, update it in Excel, save the file, attach it to the task, then click a different UI Action to update or basically replace the MRVS with the Excel file. It sounds like in your case they would just attach an Excel file with the expected columns, then run the UI Action which could be simplified to just add rows.
On the form we provide this guidance:
The Bulk Update UI Action:
current.update();
action.setRedirectURL(current);
var att = new GlideRecord('sys_attachment');
//att.addQuery('file_name', 'serial number import.xlsx');
att.addQuery('content_type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
att.addQuery('table_sys_id', current.sys_id);
att.orderByDesc('sys_created_on');
att.query();
if(att.next()){
var imp = new sn_impex.GlideExcelParser();
var attachment = new GlideSysAttachment();
var attachmentStream = attachment.getContentStream(att.sys_id.toString());
imp.parse(attachmentStream);
var headers = imp.getColumnHeaders();
var model = headers[0];
var snt = headers[1];
var sn = headers[2];
var count = 0;
while(imp.next()) {
var row = imp.getRow();
var mrvs2 = current.variables.network_gear_asset_mrvs;
var mrvsRowCount2 = mrvs2.getRowCount();
for(var i = 0; i < mrvsRowCount2; i++){
var row2 = mrvs2.getRow(i);
//update SN where temp sn matches
if(row[snt] != '' && row[snt] != null && row[snt] != undefined){
if (row[snt] == row2.v_mrvs2_serial_number_temp) {
row2.v_mrvs2_serial_number = row[sn];
count++;
break;
}
}
if((row[sn] != '' && row[sn] != null && row[sn] != undefined) ||(row[snt] != '' && row[snt] != null && row[snt] != undefined)){
//update SN or temp sn on matching model row
var mdl = new GlideRecord('cmdb_model');
mdl.addQuery('display_name', row[model]);
mdl.query();
if(mdl.next()){
if(row2.v_mrvs2_model.toString() == mdl.sys_id.toString()){
if(row2.v_mrvs2_serial_number == '' || row2.v_mrvs2_serial_number == null || row2.v_mrvs2_serial_number == undefined){
if (row[sn] != '' && row[sn] != null && row[sn] != undefined) {
row2.v_mrvs2_serial_number = row[sn];
count++;
break;
}
}
if(row2.v_mrvs2_serial_number_temp == '' || row2.v_mrvs2_serial_number_temp == null || row2.v_mrvs2_serial_number_temp == undefined){
if (row[snt] != '' && row[snt] != null && row[snt] != undefined) {
row2.v_mrvs2_serial_number_temp = row[snt];
count++;
break;
}
}
}
}
}
}
}
if(count > 0){
gs.addInfoMessage(count + ' serial numbers updated in Network Gear Assets table.');
}
else{
gs.addErrorMessage('Matching model name with blank serial number not found.');
gs.addErrorMessage('0 serial numbers updated in Network Gear Assets table.');
}
current.update();
action.setRedirectURL(current);
mrvs2 = current.variables.network_gear_asset_mrvs;
var key = 'v_mrvs2_serial_number';
var array = JSON.parse(mrvs2);
var finalObj = sort_by_key(array, key);
current.variables.network_gear_asset_mrvs = JSON.stringify(finalObj);
}
else{
gs.addErrorMessage('xlsx file not found attached to this task.');
gs.addErrorMessage('0 serial numbers updated in Network Gear Assets table.');
}
current.update();
action.setRedirectURL(current);
function sort_by_key(array, key){
return array.sort(function(a, b){
if(a[key] == null || a[key] == undefined){
var x = ' ';
}
else{
var x = a[key];
}
if(b[key] == null || b[key] == undefined){
var y = ' ';
}
else{
var y = b[key];
}
var mdl1 = new GlideRecord('cmdb_model');
mdl1.addQuery('sys_id', a['v_mrvs2_model']);
mdl1.query();
if(mdl1.next()){
if(x==' '){
x = ' '+mdl1.display_name.toString();
}
else{
x = mdl1.display_name.toString() + x;
}
}
var mdl2 = new GlideRecord('cmdb_model');
mdl2.addQuery('sys_id', b['v_mrvs2_model']);
mdl2.query();
if(mdl2.next()){
if(y==' '){
y = ' '+mdl2.display_name.toString();
}
else{
y = mdl2.display_name.toString() + y;
}
}
return ((x < y) ? -1 : ((x > y) ? 1 : 0));
});
}This has an extra function to sort the MRVS so that the rows without serial numbers were at the top. Here's the export UI Action in case in helps this all make sense:
var mrvsexp = [];
var mrvs = current.variables.network_gear_asset_mrvs;
var rowCount = mrvs.getRowCount();
mrvsexp.push('Model,Temp SN,Serial Number\r\n');
for (var i = 0; i < rowCount; i++) {
var row = mrvs.getRow(i);
var mdl = new GlideRecord('cmdb_model');
mdl.addQuery('sys_id', row.v_mrvs2_model);
mdl.query();
if(mdl.next()){
if(row.v_mrvs2_serial_number == '' || row.v_mrvs2_serial_number == null || row.v_mrvs2_serial_number == undefined){
mrvsexp.push(mdl.display_name + ',' + row.v_mrvs2_serial_number_temp + '\r\n');
}
}
}
var attachment = new Attachment();
var attachmentRec = attachment.write('sc_task', current.sys_id, 'network gear assets.csv', 'text/csv', mrvsexp.join(''));
var att = new GlideRecord('sys_attachment');
att.addQuery('file_name', 'network gear assets.csv');
att.addQuery('table_sys_id', current.sys_id);
att.orderByDesc('sys_created_on');
att.query();
if(att.next()){
var snEnv = gs.getProperty('instance_name');
var attsysid = att.sys_id.toString();
var URL = "https://" + snEnv + ".service-now.com/sys_attachment.do?sysparm_referring_url=tear_off&view=true&sys_id=" + attsysid;
current.update();
action.setRedirectURL(URL);
}
else{
current.update();
action.setRedirectURL(current);
}In our case the UI Actions are only on the Catalog Task table as the requestor does not populate/update the MRVS. It is hidden on the request form, for the task processors to populate later. If you want it done on the initial submit you could convert it to a Flow or Business Rule that runs after the request is submitted.
