Interactive Grid: Custom Select List on each row
I had a column in an editable interactive grid based on a Select List, which takes valid values from a table that supports “soft delete” – i.e. records could be marked as “deleted” without affecting existing references to those deleted records.
The SQL Query for the LOV was like this (my example is a view on a reference table of school “year levels”):
select name, id from year_levels_vw where deleted_ind is null order by sort_order
The problem is that if a year level is marked as deleted, the select list will not include it due to the where clause; since Display Extra Values is set to “Yes”, the item on the page will instead show the internal ID which is not very useful to the user. Instead, I want to show the name but appended with a string to show it has been deleted:
select name || case when deleted_ind = 'Y' then ' (DELETED)' end as disp_name, id from year_levels_vw order by deleted_ind nulls first, sort_order
So now the select list shows the name, even if it has been deleted. However, once users start using this system and they delete some year levels, each select list will include all the deleted values, even if they will never be used again. We’d prefer to only include a deleted value IF it is currently used by the record being viewed; otherwise, we want to omit it from the list.
If this was an APEX item in a single-record edit form, I’d simply change the SQL Query for the LOV to:
select name || case when deleted_ind = 'Y' then ' (DELETED)' end as disp_name, id from year_levels_vw where deleted_ind is null or id = :P1_FROM_YEAR_LEVEL_ID order by deleted_ind nulls first, sort_order
This way, the select list will only include the deleted year level if the underlying item was already set to that deleted ID. But we are now using an Interactive Grid – there is no page item to refer to.
The method I’ve used to solve this is to take advantage of the Cascading LOV feature in order to allow the query to refer to the value of the column. The SQL Query for the LOV on my Interactive Grid is:
select name || case when deleted_ind = 'Y' then ' (DELETED)' end as disp_name, id from year_levels_vw where deleted_ind is null or id = :FROM_YEAR_LEVEL_ID order by deleted_ind nulls first, sort_order
Now, we need to make sure that “FROM_YEAR_LEVEL_ID” is available to the query, so we need to put it in the Items to Submit attribute. To make this attribute available, however, we must set Cascading LOV Parent Column(s) to something; I set it to the PK ID of the table, or some other column which doesn’t get changed by the user and isn’t actually referred to in the LOV Query.
Now, records not referring to a deleted value show only valid values:
And records that refer to a deleted value include the deleted value in the list, as desired:
It should be noted that the design of the select list means that the user is allowed to save changes to the record while preserving the reference to the deleted year level. This is desired, in this case; if it wasn’t, however, I’d consider putting a validation on the page to stop the record being saved unless the user changes it to a valid value.
P.S. Concerned parents should note that this example was just testing the ability to delete a record from a database, and I’d just like to make it totally clear that there are no plans by the department to eliminate year 7 from schools. Honest!
There are two known issues:
- If the item is the child of a Cascading LOV, when the parent item is changed, APEX automatically clears out any value in the child before rendering the list of values – which means the column value submitted will be NULL – which means the “deleted” items disappear from the list immediately. This means the user will not be allowed to save the record with a reference to a deleted value from the list.
- The column filter list of values is empty – this is due to a known bug in APEX [Doc ID 2289512.1 FILTER NOT WORKING IN INTERACTIVE GRID WITH CASCADING LOV]. [thanks to Dejan for alerting me to this]
18 March 2018 - 7:27 pm
If you use cascading LOV on column in IG, column filter is not working. Filter, when clicking on column name is not present anymore. Do you have some workaround for that?
19 March 2018 - 9:48 am
Thanks for bringing this to my attention. Indeed, this method does stop the column filter from listing any values. I note you’ve already linked to the known APEX bug: https://support.oracle.com/knowledge/Middleware/2289512_1.html
23 November 2019 - 8:49 pm
Thanks Jeffrey for this example!, just want to add this approach works for updating the IG lov column with a refresh dynamic action as well.
6 February 2020 - 5:53 pm
Cascading View doesn’t seem to work in the “Single Row View”. Anyone to confirm and give me a workaround?
Thanks in advance.
11 February 2020 - 9:11 pm
To complete my previous thread, it is important to tell that the parent of my cascading lov is a select list itself. And that it doesn’t work for a select list only (if the cascading view is set in a pop up, it works).
29 July 2020 - 1:20 pm
Dear Jeffrey Kemp
Thanks for your helpful tutorial .
I try to use cascade LOV in IG (Apex 20.1 ) but getting error when select child LOV value.
Child LOV Query :
substr(inc.xdescription,1,1000) as xdescription,
from incategories inc
order by 1
Cascading List of Values :
Parent Column(s) = XCATEGORY_ID
Items to Submit =XCATEGORY_SET_ID
Please help me.
6 January 2022 - 7:17 pm
Thanks for your invaluable help.
10 June 2022 - 9:11 am
I’ve noticed the arrow is not displayed by default on the interactive grids.
is there any workaround to show them all the time ? ( not only when you click on them )
10 June 2022 - 9:14 am
Hi gusNZ, are you talking about the dropdown list indicator (down chevron)? That’s part of the rendering of the item in edit mode and won’t appear if the item is not in edit mode.