Monthly Archives: January 2017

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.

APEX 5.1 – Interactive Grids – Controlling Allowed Operations

In the last post I showed how easily you can enable an Interactive Grid for editing, however what if you want to only allow the user to edit certain rows?

Lets imagine a scenario – given the standard EMP table, I should only be able to change the salary of Employees who report to me (i.e. I can’t change the salary of an employee if they report to someone else).

So, let’s create an Interactive Grid with the following query –

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

Now, for the purposes of this example, given the hierarchy of the EMP table –

I’m going to “pretend” to be BLAKE – since BLAKE is the manager of 5 people.

If you look at the properties of the Interactive Grid you’ll see the “Allowed Row Operations Column” setting, this allows us to define a column in the query which will be used to define whether I can Update and / or Delete that row.

The way you do this is simply by returning ‘U’, ‘D’, or ‘UD’ in that column, so for example we can adapt our query to say –


select e.empno,
       e.ename,
       e.job,
       e.mgr,
       e.sal,
       e.comm,
       e.deptno,
       case
         when mgr = 7698 then 'UD'
         else null
       end allowed_operations
  from emp e

Notice the use of the CASE statement to return ‘UD’ if the Manager of the current record is 7698 (i.e. BLAKE).

We’ll also need to set the “Allowed Row Operations Column” attribute to use our new column.

Now if we try and edit a row of a record who doesn’t report to BLAKE then the row is read-only, otherwise if the employee does report to BLAKE then I can edit the row!

In the next post we’ll take a look at Master -> Detail (so we can finally replace those Forms Applications!)

APEX 5.1 – Interactive Grids

Now that APEX 5.1 has been released, I wanted to take a look at the Interactive Grid and see how easy it is to use.

So, let’s add a new Interactive Grid region to an application, using the standard EMP table as our query –

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

Now running the application gives us this

So by default it gives you many options similar to an Interactive Report (e.g. filtering, breaking, highlighting etc), but it doesn’t allow editting by default (hands up if this caught you out first time you used it?).

To enable editing pop into the Attributes section and click Enabled.

this will give you a few more options which we’ll explore later. For now running the application gives me what I want – An Editable Report!

By default all the columns are text fields, but we can easily change the DEPTNO column to be a select list based on the following query

select d.dname,
       d.deptno
  from dept d
 order by d.dname

You might also have noticed the little “hamburger menu” next to each row. This gives you some context specific control against each row.

The nice thing here is, as I mentioned earlier, we get all the capabilities of Interactive Reports, but with the power of easily being able to update the data. So for example I can do a break on Deptartmnent and can still update all the data easily.

I’m pretty sure I’ve heard that perhaps Interactive Reports will be ‘retired’ and Interactive Grids will become the replacement, there have however been a bunch of quirks I’ve noticed with the grid which should hopefully get patched soon, but in the meantime it’s an awesome productivity enhancement for developers to get this functionality out of the box (and declarative)!

In the next blog posts we’ll explore some more of the functionality of Interactive Grids.