Wednesday, July 20, 2016

Conditional column linking in APEX

Sometimes there is a requirement to have a column in an APEX report that acts as a link to another page for some rows but not for others like this:
Here, only when a program's status is 'VALID' can we link to another page by clicking on the program name.

Until now I only knew a rather bad way of doing this, which would be to write code in the report query like:

select case when program_status='VALID'
       then '<a href="f?p=MYAPP:123:&SESSION.::&DEBUG.:123:P123_PROGRAM_NAME:' 
            || program_name || '">' || program_name || '</a>'
          else program_name
          end as program_name,

Not only is it ugly and hard to write, it has some other issues too:

  1. I need to change the column's "Display As" from the default to "Standard Report Column" to prevent the link HTML being escaped and displayed literally.  And this may be frowned upon as a potential security risk.
  2. If using session state protection (SSP) then I need to add a checksum to the URL by calling apex_utils.prepare_url.  Now I have a function call in the SELECT clause, which may cause performance issues.
I now have a different solution, based partly on Tyler Muth's Conditional Column Formatting post.

Here is the query for my report above:

select program_name
     , program_status
     , case when program_status != 'VALID' then 'nolink' end as class_name
  from spl_programs

The third column CLASS_NAME will be set to 'nolink' on all rows that do not have the 'VALID' status.  This column is then set to not show in the report.

In the column attributes for the PROGRAM_NAME column, I define the column link in the usual manner, except that I add class="#CLASS_NAME#" in the Link Attributes:

I can add some CSS to make "nolink" links look like plain text, and also not have the cursor change when hovering over the link:

a.nolink {
    color: #000;
    cursor: default;

But the user could still click on the link.  To prevent it redirecting to the other page I can change the onclick event for all "nolink" links with some "Execute when page loads" Javascript:

$('a.nolink').attr("onclick","return false;");

Now if the user clicks on the link, nothing happens.  But in theory they could disable Javascript and reinstate the default link action. Or they could view the page source or use Inspect Element to see the link's URL, and copy/paste it into the browser.  In some cases that may not matter, but if it does matter then it should be prevented, for example by adding an appropriate Authorization Scheme to the target page.

One possible issue (a colleague just reminded me) is accessibility - although it no longer looks like a link, and does nothing when clicked, to a screen reader program it is still a link, and the screen reader user may be mystified as to why it doesn't work. Perhaps instead of "return false;" it should have"alert('You cannot open this program as it is invalid');".  Or there may be a better solution?


Scott Wesley said...

I think you've replaced a security risk that can be mitigated with apex_escape.html with a link that could be reenabled with a few clicks in browser tools.
Your concern on performance with a probably deterministic function call is outweighed by using substitution strings to populate session id. This will flood your shared pool.

Tony Andrews said...

Scott, I agree about the link being reenableable (that's a word now!) and mentioned other ways users could circumvent this. In my particular scenario, security is not a concern (I don't mid the user clicking the link, there is just no point for them in doing so).
Also agree I shouldn't have used &SESSION. But I'm not sure about your deterministic function point though: every row will call the function with a different argument so the function will have to be actually run for each and every row.

Unknown said...

Hi Tony,
I am missing something, when I do this way, get the cell value of , '<a href="f?...', not value from database. Can you tel me please where I am going wrong?

Tony Andrews said...

@dragy Sorry I only just saw your comment. It looks like you are implementing my old code rather than my new code? If so the problem is likely to be that you have Escape HTML on for that column, it needs to be off.

Shital said...

HI! I am relatively new to APEX. I am using APEX 18 version and my question is --IN region I have link column on clicking it may redirect to page base on condition. For this, we are using branching. But now on clicking, I am not able to open modal page. It just redirects to the page. I want to open a modal or Popup.

Unknown said...

Thanks, this has been very helpful. I was using the url option but have changed to this one. Do not understand the concern about memory.
Thanks for sharing.
In Daventry