Introduction:-

When working with Interactive Grids in Oracle APEX, it’s often important to ensure that users select valid and consistent values—especially for key fields like Department Number. In this blog, we’ll look at how to add a simple validation to make sure users choose a Department Number from an existing list, helping maintain data accuracy and prevent unwanted changes.

The following technologies has been used to achieve the same.

Oracle APEX
Java Script
Plsql

Why We Need to Do This?

1.Ensure Data Accuracy:-

Validating the Department Number ensures that users can only select from existing departments, reducing the risk of incorrect data being entered into the system.

2.Prevent Invalid Entries:-

Without validation, users might input department numbers that don’t exist, leading to broken references and potential issues in reports or backend processes.

3.Maintain Consistency Across Modules:-

Consistent data across all modules is essential. This validation helps keep department-related information synchronized and reliable throughout the application.

4.Improve User Guidance:-

By restricting values to valid options, the application guides users to make correct choices, reducing confusion and the need for manual corrections later.

5.Reduce Backend Errors:-

Valid department numbers help avoid data errors during processing, integrations, or exports—minimizing the chances of system failures or data mismatches.

How Do We Solve This?

Step 1: Create Hidden Page Items

Create two hidden page items on the page:
1.One to store the existing Department Numbers (e.g., P5_DEPT_UNIQUE_VAL)
2.Another to store any validation error message (e.g., P5_ERROR_MSG)

Step 2: Add JavaScript on Page Load

Go to Page Designer → Page Attributes → Execute When Page Loads

Add JavaScript code to store or process the existing Department Numbers for later validation.

    var deptNumber = new Set();
    var model = apex.region("emp").widget().interactiveGrid("getViews","grid").model;
   model.forEach(function(record) {
   var deptno = record[model.getFieldKey("DEPTNO")];
   deptNumber.add(deptno);
   });
   var uniqueDeptno = Array.from(deptNumber);
   $s("P5_DEPT_UNIQUE_VAL", uniqueDeptno.join(":"));

Step 3: Create a Dynamic Action on DeptNo Column

Create a Dynamic Action triggered by Change event on the Department Number column in the Interactive Grid. Add Items to return and Items to submit.

True Action 1: Server-side Code

IF INSTR(:P5_DEPT_UNIQUE_VAL,:DEPTNO,1)= 0 THEN
:P5_ERROR_MSG := 'The Department Number must match one of the existing departments.Assigning a new Department Number is not allowed';
END IF;

Items To Submit : DEPTNO,P5_DEPT_UNIQUE_VAL
Items To Return : P5_ERROR_MSG

True Action 2: Java-script Code

if ($v('P5_ERROR_MSG') != '')
{
apex.message.confirm($v('P5_ERROR_MSG'), function(okPressed) {I
f (okPressed) {
var grid   = apex.region("emp").widget().interactiveGrid("getViews", "grid");
var model  = grid.model;
var record = grid.getSelectedRecords()[0];

var value;I
f (record) {
model.setValue(record, 'DEPTNO','');
}}else
{
var grid   = apex.region("emp").widget().interactiveGrid("getViews", "grid");
var model  = grid.model;
var record = grid.getSelectedRecords()[0];
var value;
If (record) {
model.setValue(record, 'DEPTNO','');}
}
});}

True Action 3: Server-side Code

:P5_ERROR_MSG := null;

Items To Return : P5_ERROR_MSG

 

Conclusion:

Adding validation to the Department Number field in an Interactive Grid is a simple yet effective way to maintain data integrity in our Oracle APEX applications. By ensuring users select only from existing departments, you reduce the risk of errors, improve consistency across modules, and enhance the overall reliability of our data. Implementing this type of validation not only safeguards our application but also improves the user experience by guiding them toward valid input.

Output:-