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

[Sam Ogden]

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]

Hi Sam,

To get the attachment ID, can use some angular magic

var attachmentElements = angular.element("#sc_cat_item").scope().attachments;

attachmentElement.table_sys_id

[Sam Ogden]

Hi Kieran,

Would that sit inside the script section of the UI macro?  Apologies I'm self taught with scripts so only really used to javascript in business rules etc

Thanks

Sam

[Kieran Anson]

Hey Sam,

Yeah stick the above within the UI script tags similar to the below (using Asha's code as an example)

<?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"/>


<script>

var attachmentElements = angular.element("#sc_cat_item").scope().attachments;
var attach_sysID = attachmentElement.table_sys_id

function applyVariables(reference) {
var ga = new GlideAjax('ClientUtils');
ga.addParam('sysparm_name','getData');
ga.addParam('sysparm_attach_sysid',attach_sysID);
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>

[Sam Ogden]

Hi Kieran,

I half have it working but hitting a couple of issues at the end.  I have my UI Macro setup like:

<?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"/>
	
	<script>
		
function applyVariables(reference) {
	var cartID = g_form.getParameter("sysparm_item_guid");
	var ga = new GlideAjax('MRVUtils');
	ga.addParam('sysparm_name','getCSVData');
	ga.addParam('sysparm_cart_id', cartID);
	ga.getXML(processResponse);
	}

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

My Script include is set:

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 firewallObj = {};
		var importRow = new GlideRecord('u_firewall_rule_upload');
		importRow.addQuery('sys_import_set', importSetRec.sys_id);
		importRow.query();
		while(importRow.next()){
	
		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.push(firewallObj);
		
		}
		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();
		
		return grDs;
	},
	
    type: 'MRVUtils'
});

I'm uploading a file with 2 rows and it gets added to my import table as:

But when I click the UI macro the alert is showing as:

So it is building the array with the same object twice, rather than destination sever name and destination ip address being different.  Any help on what is causing this?

Also the source IP address column is not being set, instead it has created a new column of 

If I delete this colum and manually do a load data with the file the same happens so this is something outside of the script and either something with the import table or the file itself?

Any help Greatly appreciated.

Thanks

Sam

[Kieran Anson · Accepted solution]

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