Populate MRV in a record producer with data by an imported excel

LT6 · ‎10-25-2022

Hello everyone,

I have a record producer in which I can upload an excel file which gets directly inserted in the sys_attachment table.

Now I have to insert that imported data (simple strings) in my MRV. I've found similar posts:

Automatically fill the Multi row variable set from data in the custom table

Attach csv to catalog item to populate a Multi Row Variable Set (MRVs)

The problem ist that in my situation all this has to happen before even submitting the record producer.

I tried using a combination of UI Macro and Script include, but the UI Macro has to be translated in a widget to work in the service portal, but it got too complex for me since I dont have much experience with widgets.

Does anyone have a simpler solution in mind how I can pull that off??

Thank you in advance

Brad Bowman · ‎10-26-2022

I didn't look at the links you referenced to see what they are doing, but here is a server script that I have used to read an Excel attachment and populate a MRVS. I modified it to fit your example and situation, so it's untested. This would be in a Client callable Script Include called from GlideAjax in the Client Script, passing in newValue. The Excel file should have column headers on row 1.

var mrvs = [];
var json = new JSON();
var attid = this.getParameter('sysparm_attachment'); //newValue passed in from Client Script
var imp = new sn_impex.GlideExcelParser();
var attachment = new GlideSysAttachment();
var attachmentStream = attachment.getContentStream(attid);
imp.parse(attachmentStream);
var headers = imp.getColumnHeaders();
var srcip = headers[0];
var srchost = headers[1]; 
var tcp = headers[2];
var port = headers[3];
var destip = headers[4];
var desthost = headers[5];
while(imp.next()) {
    var row = imp.getRow();
    mrvs.push({
        'source_ip_or_subnet' : row[srcip],
        'source_hostname_or_description' : row[srchost],
        'tcp_udp' : row[tcp],
        'service_port' : row[port],
        'destination_ip_or_subnet' : row[destip],
        'destination_hostname_or_description' : row[desthost]
    });
}
return json.encode(mrvs);

View solution in original post

Murthy Ch · ‎10-26-2022

Hi @LT6

You can achieve this using Glide Ajax as @Brad Bowman suggested.

I have tried this a long back and it worked you can take this as a reference and implement as per your scenario.

function onChange(control, oldValue, newValue, isLoading) {
    if (isLoading) {
        return;
    }
    if (newValue == '') {
        g_form.clearValue("user_details");
    }

    var ajax = new GlideAjax('Newglidingabc');
    ajax.addParam('sysparm_name', 'addExceldatainMRVS');
    ajax.addParam('sysparm_existing', g_form.getValue('add_attachment'));
    ajax.getXML(getRITMdata);

    function getRITMdata(response) {
        var answer = response.responseXML.documentElement.getAttribute("answer");
        //alert(answer);
        g_form.setValue('user_details', answer); // this is mrvs internal name
    }
}

Script Include:

  addExceldatainMRVS: function() {
        function getUserSysid(getName) {
            var grU = new GlideRecord("sys_user");
            grU.addQuery("name", getName);
            grU.query();
            if (grU.next()) {
                return grU.getUniqueValue();
            }
        }
        var mrvsObj = [];
        var attachment = new GlideSysAttachment();
        var attachmentStream = attachment.getContentStream(this.getParameter("sysparm_existing"));
        var parser = new sn_impex.GlideExcelParser();
        parser.parse(attachmentStream);
        while (parser.next()) {
            mrvsObj.push(parser.getRow());
        }
        var mrvsObjStr = JSON.stringify(mrvsObj);
        var getV = JSON.parse(mrvsObjStr);
        for (var i = 0; i < getV.length; i++) {
            //             gs.info("Length is:" + getV.length);
            //             gs.info("Name is:" + getV[i].user_name);
            getV[i].user_name = getUserSysid(getV[i].user_name);  //calling into function to get the user sys_id
        }
        return JSON.stringify(getV);  //returning into client side
    },

Hope it helps..

Thanks,
Murthy

Su522 · ‎08-18-2024

Can you please share the client script that work with this script?

ashwithaman · 3 weeks ago

HI,

Is it possible to do the reverse of this, I have a requirement to create an excel/csv file of the MRVS data of a RITM attach it to its child task.

Can you please help me if this is even possible ?

Brad Bowman · 3 weeks ago

yeahbsolutely! I use this as a UI Action as a Form link, so it shows under Related Links.

In this example, my MRVS internal name is network_gear_decomm_mrvs, and I'm only interested in the CSV containing the values in each row for the MRVS variable named v_mrvs_serial_number. If you want the entire MRVS you would push each row.varible_name + ','... The UI Action is on the sc_task table, and the CSV is attached to the current Catalog Task

var sn = [];
var mrvs = current.variables.network_gear_decomm_mrvs;
var rowCount = mrvs.getRowCount();
for (var i = 0; i < rowCount; i++) {
	var row = mrvs.getRow(i);
	sn.push(row.v_mrvs_serial_number.toString() + '\r\n');
}
var attachment = new Attachment();
var attachmentRec = attachment.write('sc_task', current.sys_id, 'serial numbers.csv', 'text/csv', sn.join(''));

var att = new GlideRecord('sys_attachment');
att.addQuery('file_name', 'serial numbers.csv');
att.addQuery('table_sys_id', current.sys_id);
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);
}