Conditionally Show Inactive Records in an APEX LOV

List of Values (LOV) components are indispensable for presenting user-friendly selection lists when building Oracle APEX applications. However, there may be scenarios where inactive records should only appear in the LOV if they are the current selected value. In this post, I’ll show you how to set this up with a simple SQL query.

Why Would You Want to Do This?

Sometimes inactive records, like a discontinued product or a former employee, don’t need to show up in the lists. But if someone already selected it before and it’s currently active in the Popup LOV, you’d want to make sure it still appears—otherwise, it can get confusing.

In the List of Values (LOV) shown above, if a record has not been selected previously only active values will be displayed in the list. However, if a record was selected previously and it became an inactive record while it’s still the current selected value, it will continue displaying as selected. In this example, I have added the word inactive to make the user aware it needs to be updated. You could even customize your page a bit more by adding an alert.

The SQL Query Logic

To achieve this behavior, you can use a SQL query that combines active records with an additional condition to include a specific inactive record if it matches a pre-selected value. Here's an example query:

select name as d, 
       id as r
  from table_name
 where active = 'Y' 
    or id = to_number(:PX_POPUP_LOV_ITEM);

What This Does:

1. active = 'Y': Shows all the active records.

2. id = to_number(:PX_POPUP_LOV_ITEM): Adds the previously selected record (even if it’s inactive) back into the list.

Optional Improvements

• Label Inactive Records: To make things clear for users, you can add a marker like “(Inactive)”. For example:

select name || case when active = 'N' then ' (Inactive)' else '' end as d,
       id as r
  from table_name
 where active = 'Y' 
    or id = to_number(:PX_POPUP_LOV_ITEM);

• Customizable for Multi-Select LOVs: This approach can also be adapted for multiple-selection Popup LOVs by applying similar logic in the pre-rendering process for the relevant item.

Key Points

• Users only see what they need to, with inactive records hidden unless relevant.

• Users won’t lose access to records they’ve already selected if these become inactive.

• You can tweak the logic to fit your specific needs even for multi-select values.

This method is a great way to keep your LOVs clean and user-friendly while still handling edge cases where inactive records are relevant.