Implementing Duplicate Validation in Oracle APEX Interactive Grids

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

Copy Code

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 identifyDeptDuplicates function collects all valid department numbers and checks for duplicates. It utilizes the forEach method to loop through records while filtering out any that are deleted or aggregated.
Error Handling: If duplicates are found, the validateDeptModel function 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

Copy Code

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 checkDeptDuplicates function 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.