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

            }

        }

    }

}

[Ankur Bawiskar · Accepted solution]

@Ankita9793 

-> push the details in object only when USER found

update script include function as this

  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()) {
                                obj.employee_id = emp_id.getUniqueValue();
                                obj.firstname = emp_id.getValue('first_name');
                                obj.lastname = emp_id.getValue('last_name');
                            }
                        }

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

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Ankur Bawiskar]

@Ankita9793 

Hope you are doing good.

Did my reply answer your question?

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Ankita9793]

Hi @Ankur Bawiskar 

Thank you for responding!
The script is still not working as expected as 'colum1' is coming as empty.

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

[Ankur Bawiskar]

@Ankita9793 

My assumption was that your earlier logic is already working.

I just shared the enhance logic.

You should debug what's the actual issue.

If my response helped please mark it correct and close the thread so that it benefits future readers.