Oracle APEX Master-Detail Interactive Grids with Cascading Filters

How I Built Master Detail Interactive Grid with Cascading Filter in Oracle APEX

Posted by Jefith Shalin in Oracle APEX

I was building a page where I needed two Interactive Grids, one Master and one Detail, and I wanted the Detail grid to automatically filter when I click a row in the Master grid. Sounds simple on paper but I got stuck at almost every step. Writing this down mainly so I don't forget how I fixed it, and maybe it helps someone else too.

What I was trying to do

I had two tables, one Department table and one Employee table. I wanted a page where the top grid shows Departments and the bottom grid shows only the Employees of whichever Department row I click. Basic master detail, nothing fancy, but Interactive Grid does not give you this out of the box like Interactive Report region does with some settings, so I had to wire it manually.

Step 1, Setting up both grids

First I created two Interactive Grid regions on the same page. Top one Master IG on Department table, bottom one Detail IG on Employee table. Gave Static ID to both regions from the Advanced section, this part is important, without static id nothing else will work.

Master IG static id set to masterig

Detail IG static id set to detailig

Created one hidden page item P21_DEPT_ID to hold selected department

Step 2, Filtering the detail grid

I changed the SQL query of Detail IG to filter using the hidden item.

SQL

SELECT empno, ename, job, deptno, sal
FROM emp
WHERE deptno = NVL(:P21_DEPT_ID, deptno)

I used NVL here so that when no department is selected, the grid just shows all employees instead of staying blank. Small trick but saved me from a confusing empty grid on page load.

Step 3, The part where things broke

I created a Dynamic Action on Master IG with event Selection Change. Inside it I added a Set Value action to capture the clicked row and store it in P21_DEPT_ID, then a Refresh action on Detail IG. I tried this first.

JavaScript, first attempt

apex.region("masterIG").call("getSelectedRecords")[0].DEPTNO

Issue I faced : I got a console error saying Cannot read properties of null reading call. Took me a while to notice that I typed masterIG in code but my actual static id was masterig, small letters. APEX static id is case sensitive so this one character difference broke everything.

I fixed the casing and the error went away, but the value still was not getting stored properly. So I went one level deeper.

Step 4, Second problem, wrong way of reading the row data

Once the region reference was fixed, I checked what getSelectedRecords actually returns by printing it in console. I expected an object like row.DEPTNO but that is not what I got.

Console output

["30", "7654", "MARTIN", "SALESMAN", "7698", "9/28/1981", "1250", "1400"]

I tried row.DEPTNO but it didn't work, it kept returning undefined. Turns out IG stores selected record as a plain array of values by column position, not as a named object like row.DEPTNO. I had to use the grid model and its getValue method instead of reading the property directly.

Finally this approach worked : use the model's getValue method with the column name as a string, instead of trying to access it like a normal object property.

JavaScript, working version

var ig = apex.region("masterig")
var records = ig.call("getSelectedRecords")
if (records.length > 0) {
  var grid = ig.call("getViews", "grid")
  var model = grid.model
  return model.getValue(records[0], "DEPTNO")
}
return null

Step 5, Third problem, value showing in browser but not on server

After fixing the above, I checked with $v('P21_DEPT_ID') in console and it showed the right department number. So I assumed everything is working. But Detail IG was still showing old data or sometimes all rows, not the filtered one. This one confused me the most because there was no error anywhere.

One issue I faced : Set Value action only changes the item in the browser, it does not push the value to session state on the server by itself. Since Detail IG refresh happens through Ajax, it only sends whatever items are listed under Page Items to Submit for that region. My item was not listed there, so the server was still using the old or empty value.

I opened the Detail IG region, went to its attributes, and added P21_DEPT_ID under Page Items to Submit. After this one change, everything started working properly.

Final working steps, summary

1. Give Static ID to both Master and Detail IG regions, keep the exact casing in mind.
2. Create hidden page item to hold the selected master row value.
3. Update Detail IG SQL to filter using that hidden item with NVL fallback.
4. Add Dynamic Action on Selection Change of Master IG.
5. Set Value action using model.getValue, not direct property access.
6. Refresh action on Detail IG, placed after the Set Value action.
7. Add the hidden item under Page Items to Submit on the Detail IG region, this is the step most people miss.

Things I would tell my past self

• Always double check static id casing before debugging anything else.
• Selected records in IG are arrays, not objects, use the model to read values.
• Set Value updates browser only, Page Items to Submit is what actually sends it to server.
• Test every single step separately instead of assuming the whole chain is broken.

I have hosted the working version on oracleapex.com if anyone wants to check how the final behaviour looks like, clicking a department row on top instantly filters the employee grid below.

Live Demo Try It Yourself

That is basically the whole journey, from a plain error in console to a working cascading filter. Nothing here is complicated once you know it, the hard part was just figuring out where exactly it was breaking.