Validations on MRVS - validate Employee ID

[Ankita9793]

Hi All,

I have a MRVS with 3 fields, Employee ID, First Name and Last Name. 
It gets populated from Excel attachment, I want to validate the Employee ID against sys_user table. How can I achieve it?

Also, is it possible to set First name and last name based on the employee id's populated thought attachment?

[Brad Bowman]

You could change the Employee ID variable to a reference, or add a new reference variable then either have the Excel import UI Action use the ID to do a GlideRecord on the sys_user table to return the sys_id to populate in the reference variable, or run another script after the import.  The same script can return the name fields from the user record to populate the other MRVS variables.  If you're having trouble with the script, post the attempt with the current process that is populating the Employee ID using the insert code </> icon and we'll get it sorted.

[Ankur Bawiskar]

@Ankita9793 

how are you populating?

if you make Employee ID as reference to sys_user then you are sorted.

When excel is loaded, ensure only those user records are considered in MRVS which are present in sys_user table.

the logic which sets the MRVS rows simply set the user sysId, user first name and user last name in those MRVS columns.

please share some details on how that excel data is getting stored in MRVS rows

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

[Ankita9793]

Hi @Ankur Bawiskar 

We are using the below Script include and Client script to populate the MRVS based  on attachment upload.

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 checktabresult = this.checktab(parser);

        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 = {};

            row = parser.getRow();

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

                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 

then in script include itself have the logic I said

-> make Employee ID as reference to sys_user then you are sorted.

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

                // GlideRecord to sys_user with employee ID and get sysId, firstName, lastName and set in below object

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

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

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

            }

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