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:
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:
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:
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:
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:
(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:
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.
