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

[Sam Ogden]

Hi Rewanthrr,

It is a while since looked at this and I no longer work at the place where we implemented this, but think I have the basics of this in my PDI.  As mentioned in the above the basic of how I set this up are:

1. The user attaches a template csv to the item and has to click a macro.

2. The macro has an onClick function which calls a script include.

3. The script include grabs the attachment and populates a predefined data source.

4. Converts the information into a JSON format and then populates the MRV.

Macro

Script:

<?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 attachAnswer = response.responseXML.documentElement.getAttribute("answer");
	
	if(attachAnswer) {	
		g_form.setValue('firewall_changes',attachAnswer);
		} else {
		g_form.addErrorMessage(getMessage("Please attach a CSV with the correct format"));
		}
	}
		
	</script>
</j:jelly>

Script Include

script:

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

    getCSVData: function() {
        gs.info('--> MRVUtils: Entered');
        var cartID = this.getParameter('sysparm_cart_id');
		var donorTable = 'sc_cart_item';
        gs.info('--> MRVUtils: cart ID - ' + cartID);
		var att = this.checkAttachmentType(donorTable, cartID);
		gs.info('--> MRVUtils: Attachment Type - ' + att.content_type);
			if (att.content_type != 'text/csv'){
				gs.info('--> MRVUtils: Attachment Type - Hit return false');

				return;
			}
		var dataSource = this.createDataSource();


        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;
    },
	
	checkAttachmentType : function(recordTable, recordID) {
		var grAttachment = new GlideRecord('sys_attachment');
		grAttachment.addQuery('table_name', recordTable);
		grAttachment.addQuery('table_sys_id', recordID);
		grAttachment.query();
		if(grAttachment.next()){
			return grAttachment;
		}
	},



    type: 'MRVUtils'
});

Import Set:

Example Data Source:

MRV

Macro Variable

Catalog Form

This is only a rough version as was my POC in my PDI but I hope this gives you a start and helps

Thanks

Sam

[rewanthrr]

Hi Sam,

Thanks for the details, I have implemented this by attaching the attachment and submitting a request. Once the request is submitted, it reads the file and auto populte the MRVS variable values in RITM. It working great!

However, Your approach works great on the Native frameset and is not working for me in Service Portal. Could you please help me with how you have used UI Macros in Service Portal?

Thanks,

Rewanth

[Surabhi5]

Hi,

Were you able to get this working?

I have a similar requirement from management and would need all the help i can get for this.

Regards,

Surabhi

[rewanthrr]

Hi Surabhi,

Yes, I am able to complete the development. However, I am unable to use the same logic in SP. Since UI Marcros doesn't work in SP.

Thanks,

Rewanth

[Guna Shalini M]

Please explain the steps to create the import set, field map etc before moving to the script step by step. It would be really helpful

Thanks!