Restrict Input to Existing Values in a Specific Column of APEX Interactive Grid

Oracle APEX · Javascript

Validate DEPTNO in an Interactive Grid

Users can freely edit an Interactive Grid — which means they can accidentally enter a department number that doesn't exist. Here's how to catch that in real time and block the invalid entry before it reaches your database.

Step 1

Create IG region

Step 2

Hidden page items

Step 3

Page load JS

Step 4

Dynamic Action

1

Create the Interactive Grid region

Add an Interactive Grid region to your page and set its Static ID to emp. This ID is used throughout the JavaScript to reference the grid.

!

Without a static ID, APEX generates a dynamic region ID that changes between page loads — your JavaScript will break unpredictably.

2

Create two hidden page items

Add these two hidden items to your page:

P3_DEPT_UNIQUE_VAL P3_ERROR_MSG

P3_DEPT_UNIQUE_VAL stores valid department numbers from the grid. P3_ERROR_MSG carries the validation result between server and client.

3

Capture valid DEPTNO values on page load

In Execute when Page Loads, add this JavaScript. It reads all existing department numbers from the grid model and stores them as a colon-separated list.

JavaScript Execute when page loads

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("P3_DEPT_UNIQUE_VAL", uniqueDeptno.join(":"));

4

Create a Dynamic Action on DEPTNO column change

Create a Dynamic Action that fires on the Change event of the DEPTNO column. Add three actions in this exact order:

Action 1 — Check the value (PL/SQL)

Submits the entered DEPTNO to the server and sets an error message if it's not in the valid list.

PL/SQL Submit: DEPTNO, P3_DEPT_UNIQUE_VAL  |  Return: P3_ERROR_MSG

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

Action 2 — Show dialog and clear value (JavaScript)

If an error message was returned, shows a confirmation dialog. Either way, the invalid value is cleared from the cell.

JavaScript

if ($v('P3_ERROR_MSG') != '') { apex.message.confirm($v('P3_ERROR_MSG'), function(okPressed) { if (okPressed) { var grid = apex.region("emp").widget().interactiveGrid("getViews", "grid"); var model = grid.model; var record = grid.getSelectedRecords()[0]; if (record) { model.setValue(record, 'DEPTNO',''); } } else { var grid = apex.region("emp").widget().interactiveGrid("getViews", "grid"); var model = grid.model; var record = grid.getSelectedRecords()[0]; if (record) { model.setValue(record, 'DEPTNO',''); } } }); }

!

Both OK and Cancel clear the field — once flagged as invalid, there's no valid path to keep the value in the cell.

Action 3 — Reset the error message (PL/SQL)

Clears P3_ERROR_MSG so the next change starts fresh.

PL/SQL Return: P3_ERROR_MSG

:P3_ERROR_MSG := null;

With these three actions in place, any DEPTNO that doesn't exist in the grid is caught immediately, the user is informed, and the field is cleared — keeping your data clean without any extra database constraints or plug-ins.

Live Demo View on GitHub