How to create several catalog item requests from one form?

gunishi · ‎09-22-2023

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 · ‎09-22-2023

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();  
	}  
}

View solution in original post

Brad Bowman · ‎09-27-2023

Sorry, this made more sense in the example I pulled it from. The previous line newRow is creating a new (empty) MRVS row for each line of the spreadsheet, so this line is to populate the first MRVS variable (my variable name in the MRVS is v_mrvs_model) with the corresponding column headers[x] from the spreadsheet. The MRVS is just a holding spot for the spreadsheet contents until you can create an RITM for each row.

gunishi · ‎09-27-2023

Hi @Brad Bowman

That's brilliant, I can now keep pushing on with my task, thank you!

G