A Review of Interactive Grid in APEX 5.1.1

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:

• It looks like an Interactive Report; this is probably a good thing if the users are familiar with the reporting features. But advanced users will miss subscriptions, pivots, group by reports and compute columns. If they want these features, you’ll have to instead deliver Interactive Reports and sacrifice edit mode, pagination options and Jet Charts.
• The default Interactive Grids seem a bit too large; the columns (even if I only have one column) hang off the side of the screen i.e. they all seem a bit too wide – but you can resize it and save as the Primary Report (every time you create an IG). Similarly, the Auto Rows per page is a pretty great feature as it adjusts to your browser height but again the region is too tall. More often than not, I have to use the mouse wheel to see those extra records.
• Edit Mode – I got a little lost at first because it’s not always clear what mode I’m in or how to exit Edit Mode. Protected columns and rows have the faintest grey background colour applied and it can be difficult to know what I can and can’t edit.
• Item Types – There is a great variety although some have issues in 5.1.0 (Text Item with Auto Complete and Popup LOVs) which are fixed in 5.1.1; and some are difficult to actually change. For example, If I select a value in a radio group how do I get the radio group to close? Double-Clicking on the selection – no. Pressing Tab – works (would users think of this though?). Clicking away – works. Clicking the icon – closes the dialog but then I can’t see my value until I click or tab away.

Menus

• There’s the Row Level Menu where I can Delete & Duplicate and Undo. I can also use it to see the Single Report View where many options are difficult to use e.g. Duplicate Row & Add Row. Some of these options are not working in 5.1.0 (e.g. Exclude Null Columns) and we’ve got new pagination issues introduced in 5.1.1.

• There’s also the Selection Actions Menu where I can perform group functions. I like that there are these two types of menus, each performing actions on single or multiple records. A user pointed out to me they don’t like that the menu visibility because they have difficulty noticing it.

• Pagination choices are superb. It’s a big improvement over Interactive Reports and even surpasses Classic Reports in this area, although in 5.1.0 it can seem to mask the page ranges/totals when the scroll bar hits the bottom. This has been fixed in 5.1.1.
• In 5.1.0 certain combinations (which seem to be worse using Page Level Pagination) of user filtering, control breaks and aggregations seem to hide the data from displaying and cannot be fixed without an F5. This has been fixed in 5.1.1
• Jet Charts are just brilliant here, they look amazing and the variety is fantastic. Editing works as it should… kind of… apart from Horizontal reports wanting to default back to Vertical (5.1.0 only) all is fine. Well fine as long as you don’t use Page Level Pagination in 5.1.0 – as the chart sometimes appears below your report whilst switching report views and you have to F5. Speaking of F5….
• F5 – I push F5 way too many times; I perform multiple edits, deletes, add rows and I get lost, very lost, I’m on pagination page 4, I’m not sure what changes I’ve made. Pressing CTRL+Z to undo (and CTRL+Y to redo) is laggy in both 5.1.0 & 5.1.1 and only performs the undo when I move away – I might have pressed CTRL+Z too many times…. and…. F5 has fixed it.

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.

Developer Perspective

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.

• Simple: Using the declarative features
• Tricky: Changing an option using the JavaScript Config file.
• Tough: More complex changes to the Config File, or complex JavaScript using the IG AP

However, what you might expect to be in one category is different to what you might expect. For example:

Simple: Using the declarative features

• Switching from automated table updates to a manual PL/SQL method
• Setting up cascading master-detail relationships with other IGs
• Protecting certain rows from update

Tricky: Changing an option using the JavaScript Config file

• Turning Flashback off
• Disabling the ability to hide a column
• Showing a certain amount of page ranges

Tough: More complex changes to the Config File, or complex JavaScript using the IG API

• Adding a Button to the toolbar
• Adding an entry to a Menu
• Validating across records in the grid

Ultimately the business is going to ask for something in the tough category and we’ll need to branch out at some point and do something more complex. Those complexities are accomplished in JavaScript which you place in a handy section of the Interactive Grid attributes – well not always.

The IG is rather quite JavaScript intense compared to other APEX components and so this leads on to the main difference between Interactive Reports (IR) and Interactive Grids (IG). In an IR the developer, using Page Designer, has declarative access to all IR functionality through switches, check boxes, etc. Whilst an IG has many IR settings included, plus a few of its own – but there’s many more missing settings; for example: where is the setting to enable/disable sorting of columns? The answer is in the IG JavaScript Config setting. I struggled at first to get my head around why Oracle would introduce more JavaScript to APEX – a tool renowned for shielding JavaScript complexities from the Oracle Developer. Now the reason for this is obvious, there are so many settings, including some I’ve just discovered today, that it would make the attributes pane within Page Designer about 10 times longer than the current longest. In fact, I’ve used IG so much, that when a colleague asked me an IR question, I had to remind myself that the JavaScript Config doesn’t exist for IRs and I kind of felt disappointed and somewhat limited by this.

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.

• Create Columns: One feature I want to get out of the way first is the ability to create columns. I love this; the ability to add columns to the IG not listed in the SQL query is ground-breaking and I instantly wanted to use it. The typical use case would be a Display Item or HTML Expression i.e. something not editable (set the column to Query Only for this) to concatenate a first name and surname together for example. If you want to mix it up with HTML, then the HTML Expression is the column type to go for. If you use a Display Item, you can set the Source to SQL Expression for a more complex fetch; sounds good but it’s really let down by poor implementation. The problem is that the SQL Expression references other columns by their database column name – not the IG Column name and doesn’t use any #COLUMN#,  :COLUMN or &COLUMN. notation. The exact problem is that if my IG is based on the EMPLOYEES table and I want my Display Column to fetch a count of employees within the department then my query is:

( 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:

fn_get_department_count( DEPARTMENT_ID)

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.

• Grid Saving Issues: I’ve had many Grid Saving issues with some interesting error messages, which can be solved by understanding a little more about the Target Type option. Consider that some of the time your IG source SQL will be more than just selecting columns from a single table. Perhaps you’ll add a pseudo-column concatenating first_name and last_name together – this won’t save unless you set the Column Query Only to Yes. How about I add a pseudo-column ROWNUM to my query; this will not save no matter what I set the Query Only to. Certain types of SQL, including views, joins, ROWNUM, Analytical Functions (a possible solution to the point above), Group By’s and more won’t save using the ‘Region Source’ option even if those offending columns are set to Query Only. The solution to this is set the Target Type to ‘Table / View’ and, providing we have a PK, that should solve our saving issues. You can also change it to PL/SQL Code – It works well and it works like it should. Its powerful and I’d really miss it if it wasn’t there.
• Plugins – If you didn’t already know, all existing item type plugins need to be adapted to work with the Interactive Grid. At the time of writing, the only plugin I’m aware of that supports the Interactive Grid is the Modern Star Rating Plugin in the Sample Interactive Grid Application – and the version available doesn’t work in 5.1.0. If you look at it, it shows 4 thumbs ups. But if you edit the page it’s supposed to be showing 5. Some of the selected thumbs up are bleeding in to the next thumb. I fixed the 5 thumbs issue using CSS, but the bleeding issue makes this plugin unusable. It is however fixed in 5.1.1
• Keys in General. Once you switch the Grid to “Editable = Yes” then you get a row selector checkbox, a Row action menu and possibly a ROWID column. If you have already set a column to be a Primary Key; then you don’t get an automated ROWID column. If you want to recreate the ROWID column, then ensure all your columns are set to “Primary Key = No” and edit the SQL statement a bit and it will recreate it for you (5.1.0 only) or you can create it manually (5.1.1). The advice here is you must have a Primary Key assigned for an editable IG; this can either be the ROWID or another IG Column. I have no preference here; I tried to think of one which would be better over the other – but they are both win-win as far as I’m concerned. Either way, you need your IG PK Column to be hidden else you’ll see strange codes when you add a new row (oddly the ROWID is created as text column in 5.1.0, and you see weird codes, but changed to a more appropriate hidden item in 5.1.1). However, what if you have PKs which are meaningful to the users and need to be typed in? No problem, just create a ROWID column set it to be hidden and set it as the PK. Is your PK set via a sequence? Just set the sequence name in the column defaults.

The ‘tough’ category

Before I summarise, I want to end this blog with something from the tough category above; this is interesting because it’s a mix of workarounds, bugs and JavaScript code to achieve something which should be so straightforward.

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.

I know the only way to do this is to interrogate the model in JavaScript and cause an error – let’s call this the ‘validation code’.

I’m unable to run my validation code in an APEX validation because it doesn’t evaluate JavaScript, So I need to run it in some other place. At this point I’m looking for an event and run my ‘validation code’.

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.

1. It doesn’t fire when in edit mode and the save button is immediately clicked i.e. I could have changed the user type to ‘User’ and clicked Save without moving off the record. I also can’t, double this up with an APEX validation because, again, those can’t evaluate JavaScript.
2. It doesn’t fire in View mode and the delete option is used. i.e. I could delete all the Admin Users using the record actions menu in View mode.

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:

1. Remove the Save button using the IG Advanced JavaScript Config (you can also remove it using CSS). But you cannot remove it by just un-ticking the Toolbars>Buttons>Save checkbox in the attributes as this menu option not only removes the Button but also the facility to save the IG using the API!
2. Add a new Save button using the IG Advanced JavaScript Config which first calls my Validation Code (modified to ignore all records marked as deleted) and then issues the IG API save command. If errors were detected I can display to the user by using apex.message.showErrors.

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

https://apex.oracle.com/pls/apex/f?p=118853

Summary

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.

Happy Gridding

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.