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

lamtanphat · ‎12-13-2023

Hi there, can you share the client script that work with this script?
This help me alot.
Thank you

Community Alums · ‎04-01-2024

Hi ,

I have similar requirement to work on. Could you please share the Script include and client script that worked for you.

Thanks.!

akshayp · ‎03-27-2023

@Brad Bowman ,

What we can do if column headers are on row 2 ,

if you have any workaround, please reply on this post--

https://www.servicenow.com/community/developer-forum/how-to-reflect-data-from-excel-to-mrvs-if-colum...

Mark Wood · ‎04-02-2024

Hello @LT6 , is it working on the portal? I have the same requirement. I tried the Above script on the native UI which is working fine, but it's not working on the portal.

@Brad Bowman could you please provide guidance on this?

Thank you.

LT6 · ‎04-03-2024

Hello,

sadly I have no access to the catalog client script I've used to provide details. But what I can tell is that it did work on the portal. I even developed that exclusively for portal use. So it might be worth checking if you might have an error in another place.

I hope this helped