Popup Key LOVs are really useful, but ...
Popup Key LOV items let you have a page item that has an ID column as its source but which displays a text value in its place. Like this:
The SQL behind the LOV looks something like:
SELECT display_name d, id r
FROM source_table
ORDER BY 1
You can use them in forms and (as above) in tabular forms, but there's a bug when used in tabular forms that has been around for a few versions, and is still present in APEX 4.2.
The Problem
If you use the Add Row feature to create a blank record, then select a value from the Popup Key LOV and then Save Changes, if you have any validation processes that cause in-line error messages to be displayed, this causes the displayed value for the Popup Key LOV item to disappear. The selected ID value is still in the DOM, but the display value does not get re-rendered.
The Workaround
The idea is to scan the tabular form on Page Load, looking for Popup Key LOV items that have an ID value but do not have an associated display value, and call an AJAX function to retrieve the display value.
Limitations of this solution
In my case the Popup Key LOV is based on a named LOV. I use this name to retrieve the SQL for the LOV from APEX and execute it via AJAX. This means that the SQL is only defined once, in the LOV.
The code below is generic, apart from the Dynamic Action, for which you need to provide the name of the LOV. I only have one LOV item in my tabular form so the LOV name can only be one value. This could probably be derived from APEX views but I haven't done that here.
Dynamic Action
- Event: Page Load
- Condition: no condition
- Action: Execute Javascript code
- Code:
// Find any LOV items that have an ID value but do not have an
// associated display value. This will happen if new records have
// been added and there is an inline error displayed when the page
// is submitted.
// - <input type="hidden"> is the hidden ID value for the LOV
// - <input type="text"> is the displayed value
// - 'UIN_LOV' is the name of the named LOV for this item
$('span.lov').each(function(){
var vID = $(this).find('input[type="hidden"]');
var vVal = $(this).find('input[type="text"]');
if($(vVal).val()==""){
$(vVal).val(get_LOV_value('UIN_LOV',$(vID).val()));
}
});
Javascript
////////////////////////////////////////////////////////////////
// Get the display value from an LOV for the supplied ID
//
// This is used primarily when an inline error is displayed and
// there are Popup Key LOV items displayed in a tabular form.
// In this case APEX does not display the return values for
// the Popup Key LOVs on any newly added records.
//
function get_LOV_value(pLOV,pId){
var get = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=get_LOV_value',0);
get.addParam('x01',pLOV);
get.addParam('x02',pId);
return get.get();
}
Application Process
- Process Point: On Demand
- Type: PL/SQL Anonymous Block
- Source:
DECLARE
v_sql VARCHAR2(32767);
v_val VARCHAR2(32767);
CURSOR c_lov_sql IS
SELECT list_of_values_query
FROM apex_application_lovs
WHERE application_id = :APP_ID
AND list_of_values_name = apex_application.g_x01;
BEGIN
OPEN c_lov_sql;
FETCH c_lov_sql INTO v_sql;
CLOSE c_lov_sql;
v_sql := 'SELECT d '
||' FROM ('||v_sql||')'
||' WHERE r = '||apex_application.g_x02;
EXECUTE IMMEDIATE v_sql INTO v_val;
htp.prn(v_val);
EXCEPTION
WHEN OTHERS THEN
htp.prn(SQLERRM);
END;
