Validate Excel Data and populate in MRVS

[Ankita9793]

Hi All,

I have a MRVS with 3 fields i.e. Employee ID, First Name and Last Name. 
It gets populated from Excel attachment, I want to validate the Employee ID.

Employee ID is a reference field, I am trying to validate employee_id's with User table and if valid then it should be populated in MRVS along with First and Last name. 

I am trying the logic in SCRIPT INCLUDE below, please suggest. 

Script include : 

var GenesysMapExcelDatatoMRVS = Class.create();

GenesysMapExcelDatatoMRVS.prototype = Object.extendsObject(AbstractAjaxProcessor, {

    getXLData: function() {

        var arrObj = [];

        var file_id = this.getParameter('sysparam_attSysId');

        var req_type = this.getParameter('sysparam_req_type');

        var row = '';

        var att = new GlideSysAttachment().getContentStream(file_id);

        var parser = new sn_impex.GlideExcelParser();

        parser.parse(att);

        var headers = parser.getColumnHeaders();

        // var list_sheet_name = parser.getSheetNames();

        // gs.log("MapExcelDatatoMRVS Sheet Names " + list_sheet_name.join(", ") + checktabresult + list_sheet_name.length);

        while (parser.next()) {

            var obj = {};

            var idobj = {};

            row = parser.getRow();

            var colum1 = parser.getColum();

            if (req_type == 'Modify' || req_type == 'Add' || req_type == 'Change / Move') {

            gs.log('Ankita_del colum1' +colum1);

            //Validate EMP ID

            colum1 = colum1.trim();

            var idnew = colum1.split(',');

            var delarray = [];

            for (var i = 0; i < idnew.length; i++) {

            var emp_id = new GlideRecord('sys_user');

            emp_id.addQuery('employee_number', idnew[i]);

            emp_id.addQuery('active', true);

            emp_id.query();

            if (emp_id.next()) {

            delarray.push(emp_id.employee_number + '');

            var delemailid = delarray;

            gs.log('Ankita_del emailid' +JSON.stringify(delemailid));

            }

        }

            var delempid = delarray;

            var arr = Array.from(delempid);

            var test = delempid.toString();

            gs.log('Ankita_del delempid>>> ' + delempid);

            //

                obj.employee_id = delempid;

                // obj.employee_id = row[headers[0]];

                obj.firstname = row[headers[1]];

                obj.lastname = row[headers[2]];

            }

            if (req_type == 'Delete') {

                obj.mediabar_genesys_application_username_to_be_delete_disable = row[headers[0]];

                // obj.accountnumber11 = row[headers[1]];

            }

            arrObj.push(obj);

        }

        return JSON.stringify(arrObj);

    },


OnChange Client Script on Attachment field : 

function onChange(control, oldValue, newValue, isLoading) {

    if (isLoading || newValue == '') {

        return;

    }

    var req_type = g_form.getValue('request_type');

    try {

        parseXL();

    } catch (e) {

        console.log("Exception " + e.message);

    }

    function parseXL() {

        var data = new GlideAjax('GenesysMapExcelDatatoMRVS');

        data.addParam('sysparm_name', 'getXLData');

        data.addParam('sysparam_attSysId', newValue);

        data.addParam('sysparam_req_type', req_type);

        data.getXML(excelParser);

        function excelParser(response) {

            var answer = response.responseXML.documentElement.getAttribute('answer');

            // console.log('answer' + answer);

            if (req_type == 'Modify' || req_type == 'Add' || req_type == 'Change / Move') {

                // console.log('answer' + answer);

                g_form.setValue('multiple_staff_id', answer);

                g_form.setValue('json_data', answer);

            }

            if (req_type == 'Delete') {

                // console.log('answer' + answer);

                g_form.setValue('delete_multiple_users', answer);

                g_form.setValue('json_data', answer);

            }

        }

    }

}

[Sachin Gavhane]

hi @Ankur Bawiskar 

this onchange client script is for the field.
I want to trigger onchange catalog client script on the catalog item variable.
Some how for the attachment variable in the catalog item - script is not getting triggered

[Ankur Bawiskar]

@Sachin Gavhane 

please post a new question and share all the details there, also tag me there

[Sachin Gavhane]

hi @Ankur Bawiskar 

This requirement applies to catalog item variables, specifically an attachment variable.

When a user uploads an Excel file into this attachment variable:

• The system must validate the contents of the Excel file against the User ID of the logged-in user.

• If even a single invalid or incorrect entry is detected in the file, the user must not be allowed to submit the catalog request.

• The catalog form should display the list of incorrect entries clearly to the user, so they can review and correct the file before resubmitting.