How to create several catalog item requests from one form?

[gunishi]

Hi all, 

I have a requirement where there on a catalog item request form there is an attachment (of excel format). 

If there is one row I want to autofill a field on the form with that item. If there are multiple rows I want to create several catalog item requests that have the same 'requested for' value, but are then filled in with the value of the row in the excel spreadsheet. 

I am very confused as to how to go about this and would appreciate any pointers, hints or tips.

Thank you, 

G

[Brad Bowman · Accepted solution]

One approach is to populate a MRVS from the Excel contents.  Use a script like this as a workflow Run Script activity at the beginning of your workflow.

var att = new GlideRecord('sys_attachment');
//att.addQuery('file_name', 'serial number import.xlsx'); //use this line if the attachment will always have a known/same name
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]; use lines like this if you have multiple columns in the spreadsheet
    var mrvs = current.variables.network_gear_asset_mrvs; //internal name of your MRVS
	var newRow = '';
	while(imp.next()) {
		var row = imp.getRow();
        newRow = mrvs.addRow();
		newRow.v_mrvs_model = row[model];
	}
}

Now you have the REQ that was created when the Catalog Item was submitted, and ONE RITM with a MRVS populated for the one or many rows of the attached Excel file.  Next, use a workflow Run Script to update the one RITM to autofill a variable, if you want to do that, based on the first or only row in the MRVS, then create a new RITM under the same REQ for each subsequent row in the MRVS.  A script something like this will do that:

var ritmsysid = '';
var mrvs = current.variables.network_gear_asset_mrvs; //internal name of your MRVS
var rowCount = mrvs.getRowCount();

//get the sysid of EVERY Catalog Item variable that will need to be populated on new RITM(s)
var v_db_cat = '9f29df1bdbe48f004a29df6b5e9619c3';
var v_integration_status = 'bbfcbc9cdb298b00262950a45e961958';

//autofill the desired variable on the one, existing RITM
var row = mrvs.getRow(0);	
current.variables.variable_name = row.v_mrvs_var_name; //use your Catalog Item and MRVS variable names				

//if there is more than one row in the MRVS, create a new RITM under the same REQ for all of the other rows
if (rowCount > 1) {
    for (var i = 1; i < rowCount; i++) {
        var row = mrvs.getRow(i);	
        var ritm = new GlideRecord('sc_req_item');
        ritm.initialize();
        ritm.request = current.request;
        ritm.cat_item = current.cat_item;
        ritm.opened_by = current.opened_by;
        ritm.short_description = current.short_description;
        //Add variables to the new RITMs  
        addVariable(sysID, v_db_cat, row.v_mrvs_var_name,1); //the same variable autofilled above  
        addVariable(sysID, v_integration_status, current.variables.v_integration_status,2); //one line for every other variable to be populated on the new RITM  
    }  
    //Start the workflow on all the new RITMs  
    startWf(current.request);  
}  

function addVariable(ritm,varID,value,order) {  
	var variable = new GlideRecord('sc_item_option');  
	variable.initialize();  
	variable.item_option_new = varID;  
	variable.value = value;  
	variable.order = order;  
	var sysID = variable.insert();  

	var itemm2m = new GlideRecord('sc_item_option_mtom');  
	itemm2m.initialize();  
	itemm2m.request_item = ritm;  
	itemm2m.sc_item_option = sysID;  
	itemm2m.insert();  
}  

function startWf(request) {  
	var ritm = new GlideRecord('sc_req_item');  
	ritm.addQuery('request',request);  
	ritm.addQuery('sys_id','!=',current.sys_id);  
	ritm.addNullQuery('context');  
	ritm.query();  

	while (ritm.next()) {  
		ritm.setForceUpdate(true);  
		ritm.update();  
	}  
}

[gunishi]

Hi @Brad Bowman  , 

Thank you so much for your response. I will have a go at implementing this and update you on how I get on. I also really appreciate your answers from other posts; I'm at the beginning of my servicenow journey and your comments/articles have been really useful.

Kind regards, 

G

[Brad Bowman]

I appreciate hearing that!  I'm happy to help out where I can!

[gunishi]

Hi @Brad Bowman  

I'm still trying to peice together my workflow and was wondering if you could explain the last line of code in the first section:

newRow.v_mrvs_model = row[model];

I saw on another post of yours that v_mrvs_model is a varaible on the cmdb_model table, however, on my PDI I can't seem to find this. Would I replace this with the target variable on my MRVS, or would I replace this with the field name of the field on the catalog item request form that I want it to populate. 

Thank you, 

G