Introduction
Maintaining accurate data is vital in any application, particularly with databases. Duplicate entries can create confusion, hinder productivity, and compromise data integrity. This article presents two straightforward methods for validating duplicates in Oracle APEX Interactive Grids. By implementing these techniques, you can enhance data quality and improve user experience. Let’s explore these approaches in detail.
Why we need to do:
Duplicate entries in a database can lead to significant challenges:
- Data Confusion: Users may struggle to identify the correct records.
- Reduced Productivity: Time is wasted resolving duplicate-related issues.
- Compromised Integrity: Decisions based on inaccurate or redundant data can lead to errors.
By implementing effective duplicate validation, you ensure data integrity, improve operational efficiency, and enhance the overall user experience. Oracle APEX Interactive Grids provide an excellent platform for real-time data validation, ensuring users can focus on accurate and reliable data entry.
How do we solve:
Approach 1: Validate Duplicates Using identify Emp ID Duplicates
Step 1: Create Interactive Grid with Emp Table
Step 2: Now, Use the below code in Function and global variable Declaration section
var ui = this.data;
// Create a private scope with jQuery
(function($) {
// Function to identify duplicate department numbers
function identifyDeptDuplicates(model) {
const EMP_IDKey = model.getFieldKey(“EMP_ID”);
const records = [];
// Collect valid records
model.forEach((record, index, id) => {
const EMP_ID = parseInt(record[EMP_IDKey], 10);
const meta = model.getRecordMetadata(id);
if (!isNaN(EMP_ID) && !meta.deleted && !meta.agg) {
records.push({ ID: id, EMP_ID: EMP_ID });
}
});
// Determine duplicates
const duplicates = records.filter((item, index, self) =>
self.findIndex(i => i.EMP_ID === item.EMP_ID) !== index
);
return duplicates;
}
// Function to handle model updates and error messaging
function validateDeptModel(model) {
const duplicates = identifyDeptDuplicates(model);
if (duplicates.length > 0) {
const EMP_ID = duplicates[0].EMP_ID; // Get the EMP_ID 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 EMP_ID: ${EMP_ID}’,
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 === “EMP_ID”) {
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);
- Functionality:
- The identify EMP ID Duplicates function collects all valid department numbers and identifies duplicates.
- It uses the for Each method to loop through records, filtering out those that are deleted or aggregated.
- Error Handling:
- If duplicates are found, the validate EMP ID Model function displays a clear error message.
- The save button is hidden to prevent submission of invalid data.
- Real-Time Feedback:
- Subscribing to model changes enables real-time validation as users input data.
- Immediate feedback helps users correct errors promptly, maintaining data integrity throughout the process.
Approach 2: Broader Validation Using check Dept Duplicates
Step 1 : Now use below code and get the duplicate error message
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);
- Functionality:
- The check Dept Duplicates function expands validation by evaluating both department number and department name fields.
- This ensures a more thorough detection of duplicate entries.
- Error Handling:
- Similar to Approach 1, existing error messages are cleared.
- The save button is hidden, and specific error details are displayed if duplicates are detected.
- Data Validation:
- The approach maps and filters records effectively to identify duplicates.
- Users receive detailed feedback on data integrity issues, enabling them to make corrections easily.
Conclusion:
Both approaches effectively address duplicate validation in Oracle APEX Interactive Grids:
- Approach 1: Focuses on Employee numbers, offering a simple and efficient solution.
- Approach 2: Expands validation criteria to include department names, providing a more comprehensive approach.
By selecting the appropriate method based on your application’s requirements, you can significantly enhance data quality and user experience. These strategies offer robust foundations for maintaining clean and accurate data in your Oracle APEX applications.
