Maintaining accurate data is vital in any application, particularly with databases. Duplicate entries can create confusion and hinder productivity. This article presents two straightforward methods for validating duplicates in Oracle APEX Interactive Grids. By implementing these techniques, you can enhance data integrity and improve user experience. Let’s dive into each approach!
Approach 1: Enhanced Duplicate Validation
Code Overview
var ui = this.data;
// Create a private scope with jQuery
(function($) {
// Function to identify duplicate department numbers
function identifyDeptDuplicates(model) {
const deptnoKey = model.getFieldKey("DEPTNO");
const records = [];
// Collect valid records
model.forEach((record, index, id) => {
const deptno = parseInt(record[deptnoKey], 10);
const meta = model.getRecordMetadata(id);
if (!isNaN(deptno) && !meta.deleted && !meta.agg) {
records.push({ ID: id, deptno: deptno });
}
});
// Determine duplicates
const duplicates = records.filter((item, index, self) =>
self.findIndex(i => i.deptno === item.deptno) !== index
);
return duplicates;
}
// Function to handle model updates and error messaging
function validateDeptModel(model) {
const duplicates = identifyDeptDuplicates(model);
if (duplicates.length > 0) {
const deptno = duplicates[0].deptno; // Get the deptno from the first duplicate
apex.message.clearErrors();
$('#emp button[data-action="save"]').hide();
apex.message.showErrors([{
type: "error",
location: "page",
message: 'Duplicate row found for deptno: ${deptno}',
unsafe: false
}]);
} else {
apex.message.clearErrors();
$('#emp button[data-action="save"]').show();
}
}
// Set up model notification for the interactive grid
$(function() {
$("#emp").on("interactivegridviewmodelcreate", function(event, ui) {
const model = ui.model;
if (ui.viewId === "grid" && model) {
model.subscribe({
onChange: function(type, change) {
if (type === "set" && change.field === "DEPTNO") {
validateDeptModel(model);
} else if (type !== "move" && type !== "metaChange") {
validateDeptModel(model);
}
}
});
// Initial check for duplicates
validateDeptModel(model);
model.fetchAll(function() {
// Optional: Call resize if necessary
if (model.resize) {
model.resize();
}
});
} else {
console.warn("Model is not defined or view is not a grid.");
}
});
});
})(apex.jQuery);
Detailed Explanation
- Functionality: The
identifyDeptDuplicatesfunction collects all valid department numbers and checks for duplicates. It utilizes theforEachmethod to loop through records while filtering out any that are deleted or aggregated. - Error Handling: If duplicates are found, the
validateDeptModelfunction displays an error message and hides the save button, preventing the user from submitting invalid data. - Real-time Feedback: By subscribing to model changes, the grid automatically validates entries as users input data, providing immediate feedback. This proactive approach helps maintain data integrity throughout the data entry process.
Approach 2: Comprehensive Duplicate Validation
Code Overview
var validity, message,
ui = this.data;
// Create a private scope where $ is set to apex.jQuery
(function($) {
// Function to calculate and validate department numbers
function checkDeptDuplicates(model) {
var deptnoKey = model.getFieldKey("DEPTNO"),
dnameKey = model.getFieldKey("DNAME"),
recObj = [],
recArray = [];
model.forEach(function(record, index, id) {
var deptno = parseInt(record[deptnoKey], 10),
dname = parseInt(record[dnameKey], 10),
meta = model.getRecordMetadata(id);
if (!isNaN(deptno) && !meta.deleted && !meta.agg) {
recObj = {ID: id, deptno: deptno, DNAME: dname};
recArray.push(recObj);
}
});
// Identify duplicates
var duplicateIds = recArray
.map(e => e['deptno'])
.map((e, i, final) => final.indexOf(e) !== i && i)
.filter(obj => recArray[obj])
.map(e => recArray[e]["deptno"]);
var duplObjects = recArray.filter(obj => duplicateIds.includes(obj.deptno));
if (duplicateIds.length > 0) {
apex.message.clearErrors();
$('#emp button[data-action="save"]').hide();
// Display error messages
apex.message.showErrors([
{
type: "error",
location: "page",
message: 'Duplicate row found for deptno: ${deptno}',,
unsafe: false
}
]);
} else {
apex.message.clearErrors();
$('#emp button[data-action="save"]').show();
}
}
$(function() {
$("#emp").on("interactivegridviewmodelcreate", function(event, ui) {
var sid, model = ui.model;
if (ui.viewId === "grid") {
sid = model.subscribe({
onChange: function(type, change) {
if (type === "set") {
// Only update for specific field changes
if (change.field === "DEPTNO") {
checkDeptDuplicates(model);
}
} else if (type !== "move" && type !== "metaChange") {
checkDeptDuplicates(model);
}
}
});
checkDeptDuplicates(model);
model.fetchAll(function() {});
}
});
});
})(apex.jQuery);
Detailed Explanation
- Functionality: The
checkDeptDuplicatesfunction broadens the validation criteria by evaluating both the department number and department name fields. This allows for a more thorough detection of duplicates. - Error Handling: Similar to Approach 1, this function clears existing error messages, hides the save button, and presents specific error details if duplicates are detected.
- Data Validation: The approach maps and filters records to identify duplicates effectively, ensuring that the user receives clear and concise feedback on data integrity issues.
Conclusion
Both approaches effectively handle duplicate validation in Oracle APEX Interactive Grids. Approach 1 uses a simple method that focuses solely on department numbers. In contrast, Approach 2 broadens the validation criteria to include additional fields, offering a more complete solution. This flexibility allows you to choose the method that best fits your application’s needs.
By implementing these strategies, developers can significantly enhance data integrity and improve the user experience. Selecting the right approach depends on the application’s specific requirements, but both solutions serve as robust foundations for maintaining clean and accurate data.
