This blog has been a long time in the making… Of course before writing a blog on any subject, you have to do your research. In my case, my research involved creating dozens (maybe in to the 100s) of Interactive Grids (IG) and writing a new Explorer 59-page course just on the features of Interactive Grids. I’ve been amazed by some of the features, frustrated at the error messages and seen a fair few bugs along the way.
This blog describes the good, the bad and the ugly of Interactive Grids from its initial release in Oracle APEX 5.1.0 to its patch release version 5.1.1
I’ve seen 15 Interactive Grid bugs which I’ve either logged or seen confirmed by the APEX Team on the forums.
Reading the responses, the APEX team has fixed a bunch of these for release 5.1.1. However, don’t be dissuaded, there are some tremendously good features! – which I’ll cover in this blog. I aim to be fair and neutral.
It’s worth mentioning that the theme of this blog is that there’s some really great features in the Interactive Grid – none that I dislike. But sometimes there are some things that let those really cool features down.
I would normally jump straight in to the technical, showing the developer how to use a particular setting or providing code samples, but there’s another angle here that is often overlooked by tech blogs… the user view point.
The questions I ask myself are: How good is the Interactive Grid to use as a tool for multi-record modification i.e. Is it good enough for my users? will they love it or hate me for it?
As a user, just looking at it here are some observations:
Is it a better than an Oracle Forms multi-row Data Block? They’re two very different things. Data Blocks are better for faster record scrolling and faster keyboard controls e.g. arrow keys, Page Down. IGs are better for everything else e.g. Charting, Searching, Filters, Item Types – the list is endless here.
In Summary, there are some hiccups but nothing too major here. As I’ve said earlier, the APEX Team is on the case – and the IG will get better and better with each release.
Let’s start with a little summary – and remember, most of us are PL/SQL developers.
I’m going to create 3 categories for features you might want to add to your IG.
However, what you might expect to be in one category is different to what you might expect. For example:
Simple: Using the declarative features
Once you follow John Synders’ first blog on IGs, and better still actually repeat the code snippets on your APEX instance you can get up to speed with how to configure the IG relatively well. Once you get a little practice you’ll want to reach out for the documentation…. But there isn’t any documentation on the JS configuration at the moment and in my opinion it’s badly needed. For example, I wanted to create a select list on the toolbar. I know it’s possible because, through trial & error I found that the config accepts a type of SELECT. However, because I don’t have access to the documentation it has been impossible to guess how I provide the select options. During this period, developers are reaching out to John through his blog or through the Oracle APEX community forum. John’s responses are swift and excellent.
Sticking with the developer’s perspective let’s discuss a few of the features available.
( SELECT count(*) from EMPLOYEES where DEPARTMENT_ID = DEPARTMENT_ID )
APEX does not know which DEPARTMENT_ID is the table column and which is the IG column. I’ve tried aliasing and several other solutions. A custom function will work:
I also used a WITH clause as a workaround. The code editor shows an error, but the page actually works. In my opinion, a future version of APEX should use a column notation system for Display Item SQL Expressions.
A scenario is: I have an IG of users; I want to ensure when the SAVE button is clicked, I have at least one user of type ‘Admin’.
Seems a simple request; but it’s actually quite complex to code. First thing is that this is a cross-record validation type – i.e. we have to look across multiple records in the grid model to find out if we have an ‘Admin’ user.
There are three use-cases where the validation can fail
a) All the Admins are deleted
b) The type on all the ‘Admin’ users is set to ‘User’
c) It’s a blank grid so I need at least 1 ‘Admin’ user when I click save.
Approach 1: Let’s try a Dynamic Action i.e. some sort of Client Side validation
The custom event I want to use is apexendrecordedit (this fires when an edit is complete). However, we have two problems with apexendrecordedit.
So apexendrecordedit is pretty useless to be honest especially because of issue #1 but also issue #2 as well. Issue #1 is such a problem that I can’t think of any place apexendrecordedit will ever be useful.
Approach 2: The Change Event
This is promising, the Change event can run my validation code when I immediately click Save and will fail the column before the IG is saved. But It could be annoying in Use Case C where it’ll demand I create an Admin first.
The change event doesn’t fire when I delete users either, so I can’t use this.
Approach 3: Solving the Delete Record issue
We need to find an answer to Use Case a). There isn’t a dynamic action which fires when a record is deleted. There isn’t an APEX validation which I’ve found which can fire when a record is marked for deletion or actually deleted (even an ‘always execute’ one). I can’t use Allowed Row Operations to hide the delete option – because it doesn’t make sense i.e. I should be able to delete some Admins of my choice (but not all). On top of this, even if I find an error, which record do I fail if they are all deleted? – all of them? Some of them? The first one? The last one? – I can’t fail an entire IG, I can only fail records or columns within those records.
OK here is the solution- What I have to do is replace the Save button with one of my own. The steps I took are:
By the way, we can’t add Dynamic Action Click event to Save button to validate the IG first because the Dynamic Action fires after the IG is saved.
This solution actually works; but we have one more problem. An IG can also be saved using a page submit button. If the users submit the page to save the IG then my validation will be bypassed! So what I additionally have to do is create a Before Submit Dynamic Action which also calls my validation code and cancels the event should the validation code fail.
It’s an appropriate solution for smaller data sets but not suitable for larger sets as the IG model only contains a subset of the data.
To me; an over the top complex solution to a simple requirement.
Click below to see the example
I’ve mentioned some great features, I’ve mentioned some problems and I’ve discussed the blurring between complexity & perception. I’ve learned the IG through hands-on experience and have used many of the available options. Do I like it? Yes, it’s amazing; I recommend you get started on it and move away from an IRs and think more in terms of IGs. I’m encouraging my colleagues to adopt it where appropriate and I encourage you to do the same. Was the validation example above over the top complex to do? Yes, it was, but it was also a fun problem solving exercise and I learnt quite a bit. Don’t forget the APEX Team is improving this region type with each release.
Author: Matt Mulvaney
Job Title: Senior Oracle APEX Development Consultant
Bio: Matt is an experienced APEX solution designer having designed numerous complex systems using a broad range of Oracle Technologies. Building on his previous experience of Oracle Forms & PL/SQL, he is entirely focused on providing functionally rich APEX solutions. Matt promotes APEX as a software platform and openly shares best practises, techniques & approaches. Matt has a passion for excellence and enjoys producing high quality software solutions which provide a real business benefit.