APEX 5.1 – Interactive Grid – Master Detail

For as long as I’ve been using APEX, a constant cry is “But what about Master Detail?”, well you’ve always been able to do that with Tabular Forms and for complex examples you’ve always been able to create your own editable reports using the APEX_ITEM API. However it’s always been just that little bit more difficult than it should be (not to mention long term maintainability of the code wasn’t always straight-forward – if for example the underlying table definitions changed).

This has been one of the promises of the Interactive Grid feature in APEX 5.1, so let’s take a look at how easy it is.

Once again, we’ll use the ubiquitous EMP and DEPT tables, so let’s create a Master Interactive Grid using the query –

select d.*
  from dept d

Let’s also add another Interactive Grid for our Employees, using the query –

select e.empno,
       e.ename,
       e.job,
       e.mgr,
       e.sal,
       e.comm,
       e.deptno       
  from emp e   

Ok great, so we have 2 Interactive Grids, but they aren’t linked in any way.

To setup the relationship between the two Grids, we need to do a few steps –

• Configure the Employee Grid so that it “knows” the Departments region is the Master (hence the Employee region is a Detail region)
• Link the Master / Detail columns between the two regions

So firstly lets define the Master Detail relationship

Now we link the DEPTNO column in the Employees Interactive Grid with the DEPTNO column from the Master region (Departments)

If we run the page now, we’ll get an error –

this is quite self-explanatory but basically the DEPT table didn’t have a Primary Key defined (for whatever reason), so let’s go and define the DEPTNO column as the Primary Key.

Now when we run our page, if we select a row in the Departments Grid, we’ll see the corresponding Employees for that department in the Employees grid.

This might have seemed like a few steps to go through, but really it’s just a couple of additional steps to what you’d need to do to define your reprts anyway. Congratulations to the Oracle APEX team for making this so declarative and simple to implement.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s