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!)

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