Cross Row Interactive Grid Cascading LOVs

One of my main gripes about the Interactive Grid is that it performing cross row validations is quite a painful process. By this I mean, the user can modify various records and add their own; but I want to check if the entries are valid in relation to each other before submitting to the DB. For example: upon completing user edits, I want to check if at least one Admin exists, or check if all entered values add up to 100, or check if there are an equal amount of records of each type been entered, or check if there are no duplicated records… that kind of thing.

The challenges comes when you need to cascade an LOV across records:  A select list showing a custom set of records based on other rows in the grid. The problem is that the select list is based on a SQL statement and just cannot be based on the Interactive Grid Model (which holds the modified record values) or indeed the IG column value – even with the PLSQL bind syntax.

One trick I have used here to create a hidden item and then use an on change event to populate that item and then refresh the select list – however you have to reset that hidden item when clicking through records (apexendrecordedit or interactivegridselectionchange should do it) and it can be tricky.

How do I do it? Let’s take the “check there are no duplicated records scenario” from my example list above. I want to prevent the entry of duplicate records by disallowing them to enter duplicates in the first place rather than validate on save (more on this later). In my example I want them to enter Regions (e.g. Europe, Americas, etc.) and Notes – but they can’t add more than one Region of the same name to the Grid. You can follow the example here and see my code.

So the way I do it is to create the grid as normal – my columns are

• REGION_ID column is a select list based on a query
• NOTES column is a text item

[php]

SELECT region_name d,
region_id r
FROM regions
ORDER BY region_name

…standard stuff so far.

I have also created a new Select List Page Item on the page called P1_REGION_SELECT_LIST which is based on the following “PL/SQL Function Body returning SQL”

DECLARE

lv_lov_source APEX_APPL_PAGE_IG_COLUMNS.<em>LOV_SOURCE</em>%TYPE;

BEGIN
SELECT LOV_SOURCE
INTO lv_lov_source
FROM APEX_APPL_PAGE_IG_COLUMNS
WHERE application_id = :APP_ID
AND page_id = :APP_PAGE_ID
AND name =’REGION_ID’;

RETURN lv_lov_source;

END;

I’m going to use this item as my template item – i.e. this item contains all the possible options. Rather than repeat the same SQL from the REGION_ID item, I’m going to reuse it by pulling the same SQL out from the meta-data from the REGION_ID IG Column. This way, if the item SQL requires a change… I just change it in one place. Also since I do not want to show this item to my users, I will add some CSS to hide it (I could’ve used a dynamic action – but it’s faster this way).

The idea is, when the user clicks a row in edit mode, a Dynamic Action on Row Initialisation fires which resets the select list entries within the IG column.

So in pseudocode, when I initialise my IG Row…

1. I remove all select list entries from my REGION_ID IG select list column
2. I set my REGION_ID IG select list column to have exactly the same entries from the template item P1_REGION_SELECT_LIST
3. Then I loop around the modal and remove any entries that already exist as non-deleted records.
4. If I get to the row I’m on; well I’ve just wiped out all the select list entries so I need to set the selected value back to the one held in the modal.

I have to do this for every click of a different record. This is because the select list REGION_ID is static for all rows; if I change the select list entries once, it the same changed-state for all record. That is why I have to destroy & rebuild the list every time. The good thing is that the model just holds values – so once the value is changed, regardless of how many entries are in the select list, it persists in the model.

You can see a working example, together with code examples here:
https://apex.oracle.com/pls/apex/f?p=116686

The deleted records cause great problems. Why? Because it’s possible the user can delete an entry, then add a duplicate non-deleted one, then Refresh Rows of the deleted record and press save… causing a duplicate. Furthermore, Duplicating Records and adding New Rows when the IG is at capacity requires custom built solutions.

Rather than explain the ins and outs of duplication preventing client side; here are a few things to consider.

1. Add a server side validation to prevent duplicates
2. At the very least a unique index protecting your table
3. Use a PL/SQL based DML process with duplicate detection
4. Add a before-submit dynamic action to check the modal for duplicates
5. Use the Save “button swap” technique, combined with a model check (see above)
6. Remove IG actions like duplicate from the IG in the advanced JS configuration area to reduce chances of duplicate creation (I have used this in my demo application)
7. Consider how the Add Button can be enabled/disabled to prevent new rows been added when the grid is at capacity (i.e. when the maximum number of distinct rows can be added)

As explained earlier; this is my gripe that cross model validations are not out-of-the-box, that I can’t easily compare user entered/modified records and I have to plumb all the routes that the user has available to them to cause problems. It would be really great if this sort of thing was available out of the box.

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.