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

Sam Ogden · ‎06-10-2020

Hi All,

I was wondering if there is anyway to allow a user to upload a CSV to a catalog item that would populate a multi-row variable set on the same catalog item.

We have created a MRV on a Firewall rule change catalog item:

Use case:

Most of the time this is fine as the user will only need to add a handful of rows at a time. However there are certain times where a new server would require many rows to be added. Currently the team have some csv templates that they have with all the row information contained. They have asked if in these instances they could attach the template which would be added to the MRV?

Is this possible? Or any other suggestions for bulk upload to a MRV?

Any help is greatly appreciated

Thanks

Sam

Kieran Anson · ‎06-10-2020

Hey Sam,

Dam that was quick. Only one slight modification and that is to create an empty array within the while loop and also numerate the array

Changes Highlighted:

        var firewallArr = [];
        var i = 0; //Boolean to iterate

        var importRow = new GlideRecord('u_firewall_rule_upload');
        importRow.addQuery('sys_import_set', importSetRec.sys_id);
        importRow.query();

        while (importRow.next()) {
            var firewallObj = {}; //Moved obj to inside while loop so we have a new one each time.

            firewallObj.source_ip = importRow.getValue('u_source_ip');
            firewallObj.source_server_name = importRow.getValue('u_source_server_name');
            firewallObj.destination_ip = importRow.getValue('u_destination_ip');
            firewallObj.destination_server_name = importRow.getValue('u_destination_server_name');
            firewallObj.protocol = importRow.getValue('u_protocol');
            firewallObj.port = importRow.getValue('u_port');
            firewallObj.service = importRow.getValue('u_service');
            firewallArr[i] = firewallObj; //Set the object created to the array based on the value of i.

            i += 1; //increase value of i
        }

Full Code:

var MRVUtils = Class.create();
MRVUtils.prototype = Object.extendsObject(AbstractAjaxProcessor, {

    getCSVData: function() {
        gs.info('--> MRVUtils: Entered');
        var cartID = this.getParameter('sysparm_cart_id');
        gs.info('--> MRVUtils: cart ID - ' + cartID);
		var dataSource = this.createDataSource();

        var donorTable = 'sc_cart_item';
        var recipientTable = 'sys_data_source';
        var recipientID = dataSource.getUniqueValue();
        gs.info('--> MRVUtils: recipient ID - ' + recipientID);

        GlideSysAttachment.copy(donorTable, cartID, recipientTable, recipientID);

        var loader = new GlideImportSetLoader();
        var importSetRec = loader.getImportSetGr(dataSource);
        var ranload = loader.loadImportSetTable(importSetRec, dataSource);
        importSetRec.state = 'loaded';
        importSetRec.update();


        var firewallArr = [];
        var i = 0;

        var importRow = new GlideRecord('u_firewall_rule_upload');
        importRow.addQuery('sys_import_set', importSetRec.sys_id);
        importRow.query();

        while (importRow.next()) {
            var firewallObj = {};

            firewallObj.source_ip = importRow.getValue('u_source_ip');
            firewallObj.source_server_name = importRow.getValue('u_source_server_name');
            firewallObj.destination_ip = importRow.getValue('u_destination_ip');
            firewallObj.destination_server_name = importRow.getValue('u_destination_server_name');
            firewallObj.protocol = importRow.getValue('u_protocol');
            firewallObj.port = importRow.getValue('u_port');
            firewallObj.service = importRow.getValue('u_service');
            firewallArr[i] = firewallObj;

            i += 1;
        }

        var firewallJSONString = JSON.stringify(firewallArr);
        gs.info('--> MRVUtils: Result - ' + firewallJSONString);
        return firewallJSONString;

    },

    createDataSource: function() {
        var grDs = new GlideRecord('sys_data_source');
        grDs.name = 'Firewall Rule Change at: ' + new GlideDateTime();
        grDs.import_set_table_name = 'u_firewall_rule_upload';
        grDs.file_retrieval_method = 'Attachment';
        grDs.type = 'File';
        grDs.format = 'CSV';
        grDs.header_row = 1;
        grDs.sheet_number = 1;
        grDs.insert();
        gs.info('--> DataSource sysID is ' + grDs + ' and table val is ' + grDs.sys_class_name);
        return grDs;
    },



    type: 'MRVUtils'
});

View solution in original post

Kieran Anson · ‎06-10-2020

Hi Sam,

This could be done if the data is templated as mentioned. The way I would do it would be using a macro on the form to initialise the transform, a button such as "apply".

User adds file using the attachment function. This creates a record on sys_attachment table with cartID.
Macro to call a script include that copies this attachment to an already created data source.
Use GlideImportSetLoader to load the data into the table.
Create a JSON body to be passed back to the UI macro and applied to the MRV.

Sam Ogden · ‎06-10-2020

Hi Kieran,

Thanks for the above. I think I understand the logic above. The only part I am not used to are macros. Was wondering if you had any example of a Macro that runs on upload of an attachment to call a script include. I think once I understand this part I could write the script include and use the GlidImportSetLoader. I think I know howto create the JSON body. Not too sure on how to then pass this back to the UI macro so any more around this would also be very helpful

Would the above solution add the data into the MRV before the user has submitted the item so they could review the data in the MRV?

Thanks

Sam

Ash10Patil · ‎06-10-2020

Hi,

You can create a macro with button, onclick on button you can call script include function where you can put logic to load data and create JSON and sent json back to callback function in macro to populate multi row variable set.

You can add this macro just before multi row variable set on catalog item.

-----------------------------------------------------------------------------

<?xml version="1.0" encoding="utf-8" ?>
<j:jelly trim="false" xmlns:j="jelly:core" xmlns:g="glide" xmlns:j2="null" xmlns:g2="null">
<input type="button" onclick="applyVariables()" value="Apply Data"/>

function applyVariables(reference) {
var ga = new GlideAjax('ClientUtils');
ga.addParam('sysparm_name','getData');
ga.getXML(processResponse);
}

function processResponse(response)
{
var answer = response.responseXML.documentElement.getAttribute("answer");
alert(answer);
g_form.setValue(<mutli_row_var_set>,answer);
}
</script>
</j:jelly>

-------------------------------------------------------------------------------------------

Please mark this correct and helpful if resolves you query.

Thanks.

Sam Ogden · ‎06-10-2020

Hi ASHA,

Thanks for the above. In the UI macro how do I get the cart ID so that can be passed into the script include so I can lookup the attachment form sys_attachment ?

Thanks

Sam