Improving Data Accuracy: Custom Validation Strategies for Oracle APEX Interactive Grids

Oracle APEX offers a powerful platform for building dynamic web applications, and the Interactive Grid is one of its most flexible components. Ensuring data integrity within Interactive Grids is crucial for maintaining accurate and reliable information. In this blog post, we’ll walk through enhancing validation for Interactive Grid fields using custom JavaScript functions. We will focus on validating employee names and commission values and ensure that these validations are applied effectively.

Overview

We will implement validation for two key fields:

Employee Name Validation: Ensuring names should not be null.
Commission Validation: Ensuring commission values are numeric and not empty.

The approach involves creating custom validation functions and integrating them with Oracle APEX’s Interactive Grid. We’ll also handle validation when the page loads and rows are initialized.

Validation Functions

Employee Name Validation

We need to ensure that employee names are uppercase and alphabetic or should be not null. The following regular expression validates this requirement:

Copy Code

const EMP_NAME_REGEX = /^[A-Z]+$/;

function validateEmployeeName(name) {
    return !EMP_NAME_REGEX.test(name) ? "#LABEL# must be uppercase and contain only alphabetic characters" : "";
}

or
function validateEmployeeName(name) {
    const trimmedName = name.trim();
      if (trimmedName === "") {
        return "#LABEL# must have a value";
    }

    return "";
}

Commission Validation

Commission values should be numeric and not empty. We trim any leading or trailing spaces before performing validation:

Copy Code

const COMMISSION_REGEX = /^\d+$/;

function validateCommission(commission) {
    const trimmedCommission = commission.trim();
    if (trimmedCommission === "") {
        return "#LABEL# must have a value";
    }
    return !COMMISSION_REGEX.test(trimmedCommission) ? "#LABEL# must be a valid number without spaces" : "";
}

Setting Custom Validity

We need to set custom validity messages for the input fields to integrate these validation functions with the Interactive Grid.

Check Employee Name Validity

This function sets a custom validity message for the employee name input field:

Copy Code

function checkEmployeeNameValidity(inputElement, name) {
    if (inputElement.validity.valid) {
        inputElement.setCustomValidity(validateEmployeeName(name));
    } else {
        inputElement.setCustomValidity("");
    }
}

Check Commission Validity

Similarly, this function sets a custom validity message for the commission input field:

Copy Code

function checkCommissionValidity(inputElement, commission) {
    if (inputElement.validity.valid) {
        inputElement.setCustomValidity(validateCommission(commission));
    } else {
        inputElement.setCustomValidity("");
    }
}

Integrating with Interactive Grid

To apply validation rules to the Interactive Grid, integrate the functions with grid events.

Validate Grid Data

The following code validates each record in the Interactive Grid by applying our custom validation rules:

Copy Code

(function($) {
    function validateGridData(model, records = null) {
        const empNameFieldKey = model.getFieldKey("ENAME");
        const commFieldKey = model.getFieldKey("COMM");

        function setFieldValidity(recordId, fieldName, errorMessage) {
            let fieldMeta = model.getFieldMetadata(fieldName);
            if (errorMessage) {
                errorMessage = apex.util.applyTemplate(errorMessage, {
                    placeholders: {
                        LABEL: apex.util.escapeHTML(fieldMeta.label || fieldMeta.heading)
                    }
                });
            }
            model.setValidity(errorMessage !== "" ? "error" : "valid", recordId, fieldName, errorMessage);
        }

        function validateRecord(record, index, recordId) {
            let errorMessage,
                meta = model.getRecordMetadata(recordId),
                fields = meta.fields;

            if (!fields) {
                fields = meta.fields = {};
            }
            if (!fields.ENAME) {
                fields.ENAME = {};
            }

            errorMessage = validateEmployeeName(record[empNameFieldKey]);
            setFieldValidity(recordId, "ENAME", errorMessage);

            if (!fields.COMM) {
                fields.COMM = {};
            }

            errorMessage = validateCommission(record[commFieldKey]);
            setFieldValidity(recordId, "COMM", errorMessage);
        }

        if (records) {
            records.forEach((record) => {
                let recordId = model.getRecordId(record);
                validateRecord(record, null, recordId);
            });
        } else {
            model.forEach((record) => {
                let recordId = model.getRecordId(record);
                validateRecord(record, null, recordId);
            });
        }
    }

    $(function() {
        $("#emp").on("interactivegridviewmodelcreate", function(event, ui) {
            if (ui.viewId === "grid") {
                const model = ui.model;
                model.subscribe({
                    onChange: function(changeType, changeData) {
                        if (changeType === "addData") {
                            validateGridData(model);
                        } else if (changeType === "refreshRecords" || changeType === "revert") {
                            validateGridData(model, changeData.records);
                        }
                    }
                });
                validateGridData(model);
            }
        });
    });
})(apex.jQuery);

Execute on Page Load

To ensure validation functions are applied when the page loads and whenever rows are initialized, add the following code to the “Execute on Page Load” section:

Copy Code

apex.items.C_ENAME.element.on("change", () => {
    let ename = apex.items.C_ENAME,
        value = ename.getValue();

    checkEmployeeNameValidity(ename.node, value);
});

apex.items.C_ENAME.reinit = function(value, display) {
    this.setValue(value, display, true);
    checkEmployeeNameValidity(this.node, value);
};

apex.items.C_COMM.element.on("change", () => {
    let comm = apex.items.C_COMM,
        value = comm.getValue();

    checkCommissionValidity(comm.node, value);
});

apex.items.C_COMM.reinit = function(value, display) {
    this.setValue(value, display, true);
    checkCommissionValidity(this.node, value);
};

This code binds the change event of items C_ENAME and C_COMM to their respective validation functions. It also overrides the reinit method to ensure validation when rows are initialized.

Summary

In this blog post, we demonstrated how to enhance validation for fields in an Interactive Grid in Oracle APEX. By defining custom validation functions and integrating them with the Interactive Grid, we ensure that employee names and commission values meet the required criteria. The provided code handles validation on page load, row initialization, and during data entry, improving data integrity and user experience.

Feel free to adapt these functions and integration methods to fit your specific needs in Oracle APEX applications.