Validation after Excel upload

[Dipali_Pawar]

Hi, I have requirement to populate data on MRVS via excel upload that is working. but validation part I need to do suppose i have fields x , y and in MRVS x has regex which accept 9 digits and y has regex 2 decimal places so same validation should take place after uploading excel. 

suppose user enters only 3 digits in x column in excel then after uploading it should show error like please enter up to 9 digits. 

please let me know if anyone worked on similar requirement. 

Thanks

[Ankur Bawiskar]

@Dipali_Pawar 

I already told why onChange won't work here.

I clearly explained the reason yesterday.

Did you get a chance to check?

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

[Dipali_Pawar]

yes I tried with on load script but as i mentioned it's not at all working so again deleted the code 

[Dipali_Pawar]

Hi @Ankur Bawiskar , Everything I have done using one script only where i was populating the data so on same script validation is working 

[Ankur Bawiskar]

@Dipali_Pawar 

use this updated scripts

Script Include:

getExcelData: function() {
    var arrObj = [];
    var errorMessages = [];
    var file_id = this.getParameter('sysparam_attSysId');
    var att = new GlideSysAttachment().getContentStream(file_id);
    var parser = new sn_impex.GlideExcelParser();
    parser.parse(att);
    var headers = parser.getColumnHeaders();

    var regexX = /^\d{9}$/; // x must be exactly 9 digits
    var regexY = /^\d+(\.\d{1,2})?$/; // y up to 2 decimal places

    var rowNumber = 0;

    while (parser.next()) {
        rowNumber++;
        var obj = {};
        var row = parser.getRow();

        var xValue = String(row[headers[0]] || '').trim();
        var yValue = String(row[headers[1]] || '').trim();

        // Validate x (9 digits)
        if (!regexX.test(xValue)) {
            errorMessages.push("Row " + rowNumber + ": Field 'x' should be exactly 9 digits, found '" + xValue + "'");
        }

        // Validate y (up to 2 decimals)
        if (!regexY.test(yValue)) {
            errorMessages.push("Row " + rowNumber + ": Field 'y' should be a number with up to 2 decimal places, found '" + yValue + "'");
        }

        obj.x = xValue;
        obj.y = yValue;

        // Populate other fields similarly, example:
        obj.length_cm = row[headers[2]];
        obj.width_cm = row[headers[2]];
        obj.weight_kg = row[headers[4]];
        obj.case_height_cm = row[headers[5]];
        obj.case_length_cm = row[headers[6]];
        obj.case_width_cm = row[headers[7]];
        obj.case_gross_weight_kg = row[headers[8]];
        arrObj.push(obj);
    }

    // Compose response with data and error message together
    var response = {
        data: arrObj,
        errors: errorMessages
    };

    return JSON.stringify(response);
}

Client Script:

function onChange(control, oldValue, newValue, isLoading) {
    if (isLoading || newValue == '') {
        return;
    }

    try {
        parseXLS();
    } catch (e) {
        alert("Exception caught in " + e.message);
    }

    function parseXLS() {
        var att = new GlideAjax('sn_customerservice.ExcelParse');
        att.addParam('sysparm_name', 'getExcelData');
        att.addParam('sysparam_attSysId', newValue);
        att.getXML(excelParser);

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

            var resultObj = JSON.parse(answer);

            // Set the MRVS field value with parsed data
            g_form.setValue('pack_dimensions_and_weight', JSON.stringify(resultObj.data));

            // Show info messages for all errors found
            if (resultObj.errors && resultObj.errors.length > 0) {
                resultObj.errors.forEach(function(msg) {
                    g_form.addInfoMessage(msg);
                });
            } else {
                g_form.clearMessages(); // Clear any previous messages if none now
            }
        }
    }
}

Note: I believe I have answered your question and you can enhance it based on your requirement and developer skills

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

[thippeswamy]

You are populating data on the MRVS based on the attachment variable.

• For rows that pass validation, add them into the MRVS.

• For rows that fail validation, display an info message to the user indicating which Excel row failed and why, so they can correct it.

If my response helped, please mark it as correct and close the thread so it can benefit future readers.

Regards,
Swamy