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

[LT6]

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]

Are you able to use a variable with the type of Attachment to handle attaching the Excel file to the RP?  If so, you can create an OnChange Catalog Client Script when this variable changes, passing the value (sys_id of the attachment record) via GlideAjax to a Script Include to read the attachment and populate the MRVS.  I haven't had time to try this all the way through yet to see if the MRVS refreshes once the script runs so that the user can see it populated before they Submit the RP.

[LT6]

Hello Brad, thanks for the quick response!

Yes currently I'm using an attachment variable for upload (attach_firewall_rules).

I created this OnChange Catalog Client Script for adding a row filled with static values to the MRV when I upload an attachment. And it works just fine!

function onChange(control, oldValue, newValue, isLoading) {
    if (isLoading)
        return;

    var mrvs = (g_form.getValue("firewall_rule").length != 0) ? JSON.parse(g_form.getValue("firewall_rule")) : [];
    mrvs.push({
        source_ip_or_subnet: "Test1",
        source_hostname_or_description: "Test2",
        tcp_udp: "Test3",
        service_port: "Test4",
        destination_ip_or_subnet: "Test5",
        destination_hostname_or_description: "Test6",
    });
    g_form.setValue("firewall_rule", JSON.stringify(mrvs));
}

Now what I'm not quiet sure about is how to write the script include or better said weather and how to use the whole data source/ transform map functionality inside script to read out the datain the Excel and correctly insert it for my case..

[Brad Bowman · Accepted solution]

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

[LT6]

Thank you, this worked for me like a charm!