Wednesday, February 11, 2015

APEX - Changing the colour of a row based on the value

UPDATE:

I found another way to do this : https://community.oracle.com/thread/3739809
by Kiran Pawar

I have a page that shows application accounts and their status (OPEN, LOCKED, EXPIRED), and want the OPEN ones to appear in green, and the others to appear in red.

So I followed Kiran's instructions, and it worked like a charm!

This is copied from the forum post in case I can't find it in future:

Create a dynamic action (I called it 'locked')

 Name : give appropriate name
     Event : After Refresh
     Selection Type : Region
     Region : select your classic report region
     Condition : No condition
     Action : Execute JavaScript Code
     Fire on Page Load : Yes

This is my code:

$('td[headers="ACCOUNT_STATUS"]').each(function() {
  if ( $(this).text() === 'OPEN' ) {
    $(this).closest('tr').find('td').css({"color":"green"});
  }
 if ( $(this).text() != 'OPEN' ) {
    $(this).closest('tr').find('td').css({"color":"red"});
  }
});

The report column name is 'ACCOUNT_STATUS'

Thanks to Kiran for this.


I'm writing an app that shows the status of various database things, and I want the colour of the row to be red when there is an issue.

The solution is in an APEX forum post but it took some finding so I thought I'd put it here for my future reference.

The forum post is here : https://community.oracle.com/thread/2602690

by Jari.

In my app, I have a table (dbstatus) that is fed from a cron job that connects to the database and runs a script:

select instance_name,startup_time from v$instance;

The table just has the database name and the startup time as columns.

If the database is down and this fails, the startup_time will be null in the table, so that's what I check on.

 The query in the report looks like this:

select dbname,start_time,
case when start_time is null
then 'red'
end as fcolor
 from dbstatus;



Fairly straight forward.

You now need to edit the report columns. You will see a new column has appeared called FCOLOR - uncheck the 'SHOW' box so that it is hidden.

Click on the edit icon for the dbname column, and scroll down to the "Column Formatting section and enter this into the 'HTML Expression' box:




Do the same for the 'Start Time' column:





Now if the start time is null, the database name will show up in red:



 To make the others show up in green is a simple change to the SQL - just add the "else 'green'" line:

select dbname,start_time,
case when start_time is null
then 'red'
else 'green'
end as fcolor
 from dbstatus;




I think this is pretty neat.