Tuesday 28 April 2020

Oracle APEX Interactive Grid URL Filtering

Oracle APEX 20.1 IG filters using link


Interactive Grid URL Filtering

Linking to Interactive Grids

It has been a week since APEX 20.1 has been released which gave us some time to upgrade/install or visit apex.oracle.com to check out some of the new features. 

Many things to pick from where do you start:
  •  Report Printing generating PDFs directly from your Interactive Grids
  •  Session Timeout Alerts
  •  Friendly URLs
  •  Mega Menus
  •  Export App as Zip

If you by any chance visit apex.oracle.com for more detailed info and read latest release notes there are even more hidden gems in there. 

Since many blog posts already covered Mega menus and friendly URLs I wanted to bring to your attention mine most useful feature of APEX 20.1. 

Having worked with Interactive reports for a few years now and lately Interactive grids what I really like is that team decided to include feature that has been with us for some time on IRs which many of us found so handy and now it is finally available for Interactive Grids too - you guessed it, it is a URL filtering

So far we were able to filter our Interactive Grids using exposed JS APIs but now we can do this far simpler and declarative using standard APEX links. Similar to how we were able to do this using Interactive reports  
which enabled us to create/modify filters on IRs for our end users.  Now we can do the same with Interactive grids. Let's see how.
1. Setting filtering option on IG

To create a filter on your interactive grid region using a link we can simply apply similar logic IG<operator>_TARGET_COLUMN

Place this in the Item Names section, and pass the filter value in the corresponding location in the Item Values section of the URL our grids will be filtered out. Fantastic! Thank you Oracle APEX team.  

Sounds more complex than it really is. Create an interactive grid on your page decide a column on which you want to filter on
let's say now I want to create a button that will navigate to this page and automatically filter my report to say list all records where CREATED_BY equals to 'DANY'. 

Create new button under Behavior set Action to be redirect to Page in this Application and set these properties:
and if you Run your page your report would be filtered out.

If I wanted to filter rows under Group Size greater than 100 you would do

Notice IGGT_GROUP_SIZE syntax. You get the picture. :) 

So what other IG filters can we use. Pretty much same as with IRs before:
EQ = Equals
NEQ = Not Equals
LT = Less than
LTE = Less than or equal to
GT = Greater Than
GTE = Greater than or equal to
N = Null
NN = Not Null
C = Contains
NC = Not Contains
IN = SQL In Operator
NIN = SQL Not In Operator
If we inspect friendly URL generated for this page it is very easy to understand how this works.
Live example you can see here. 

2. Link to Saved Reports
Now best comes last what we can also do is link directly to any of our Saved Reports. To do this simply add static ID to your IG region in my case this is emp. Change your link settings to 
Notice Request is here: IG[static_region_ID]_<saved report name>.

Similar again friendly URL would look like: 
I am not sure if we can link directly to Detailed view or Icon view yet. It may be just something to look forward to in releases to come. ;)

If you need more info how it all works you can check out Sample Interactive Grid application which has been upgraded to include few examples of link filterings. Or visit lates API documentation to explore APEX_IG package and how to do this from withing your PL/SQL code.

Happy APEXing,

Saturday 4 April 2020

Interactive grid validation duplicated rows check

APEX and IG validation part 2


Interactive grid validation

Check for duplicates using JavaScript

In my first blog I tried to address the problem of checking for duplicated rows in IG but as you have seen it did not cover for most obvious problem which is immediate validation of your data on the client.

If you would to enter two of the same values at one go validation would still submit both to DB which is not what we want. 

Just recently I have seen post by Tobias Arnhold and AskMax on a same subject so......

it only means there is more people challenged with the same problem. 
It was on my to do list for a long time and now that everything slowed down I finally found time to see what we can do about it. This example raised so many questions on my previous post so hoping this will help solve some of these too.
With snippet of JS added to your page with standard IG based on EMP table with static ID set to 'emp':

Pretty much all of the code is taken from John Snyder's IG cookbook examples and I added a section to check for duplicates. 
Download the full JS.  

That should be all. 
Leaving you with live demo @here.

Happy APEXing,