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 Kieran,

Thanks for the above, sorted the issue with the objects.

I'm still fairly new to scripting.  You have any additional suggestions around my script include to improve it, or is it generally ok?

My only other questions - What would be the best way to catch if a user attaches a file in the wrong format - i.e. either not a CSV or a CSV but with incorrect headers?

Thanks for all your help on this

Sam

[Kieran Anson]

Hi Sam,

Nothing wrong with the script at all, it's exactly how I would have done it.

You can validate the attachment type by using the getAttachments function.

var att = GlideSysAttachment.getAttachment(donorTable,cartID);
if (att.content_type != 'text/csv')
return false;

You can then, as part of the UI macro add an if statement if the returned answer value is false/null.

if(answer){
g_form.setValue(firewall_changes,answer)
} else {
g_form.addErrorMessage(getMessage("Please attach a CSV with the correct format"));
}

[Sam Ogden]

Hi Kieran,

Just tried this but can't seem to get it to work.  Got my script include set up as below, If I add a non csv file, in my alert in the ui macro script I;m getting 'null' returned from the script include and it hits the error message.  I was expecting false to be returned.

If I add an attachment of csv it still returning 'null' and hitting the error message.

I added an info log message to the script include but it isn't getting printed, I get:

In sys_attachment I can see my file is correct:

script include:

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 = GlideSysAttachment.getAttachment(donorTable, cartID);
		gs.info('--> MRVUtils: Attachment Type - ' + att.content_type);
			if (att.content_type != 'text/csv')
				return false;
			
		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;
    },



    type: 'MRVUtils'
});

Thanks

Sam

[Sam Ogden]

Hi Kieran,

I tried in background scripts and just couldn't get it working, instead I've just added a small function to my script include to do a glide lookup.

	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;
		}
	},

And amended earlier in the script include to:

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')
				return false;

This is now correctly returning false when not csv and carrying on when is csv.  The only thing now is the error message doesn't seem to be working?

Cheers

[Sam Ogden]

I've amended the script include return instead of return false and seems to be working.

Thanks for your help

Sam