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?