APEX

I am working in an Oracle Apex project (Application Express 4.2.6.00.03). The area I am having trouble with concerns these two fields:

For simplicity in typing my problem I will be referring to the field on the left as A and the field on the right as B.

Both of these fields are select lists. On page load both fields are loaded with all available/relevant options. What I am trying to accomplish is if a selection is is made in field A then field B is updated with a filtered list based on the selection in A. The reverse applies for field B, if a selection is made in field B then field A should be updated with a filtered list. Any selection made should be retained by the field. My select list query for field A is:

SELECT display_value, return_value
FROM TABLE(F_GETSYSTEMNAMEBITMAP(:P78_MODBUS_ADDRESS));

For field B:

SELECT display_value, return_value
FROM TABLE(F_GETMODBUSADDRESS(:P78_SYSTEM_NAME_BITMAP));

The function call for the two items are:

create or replace function F_GETSYSTEMNAMEBITMAP (modbusaddress in NUMBER)

RETURN display_table PIPELINED IS

BEGIN

FOR rec IN (  
  SELECT a.NAME AS display\_value,  
       a.ParameterID as return\_value  
  FROM Tbl\_Parameter a  
  JOIN Tbl\_Modbus b on a.parameterid = b.parameterid  
  WHERE modbusaddress IS NULL  
  OR b.address = modbusaddress  
  ORDER by a.name)  
LOOP  
    PIPE ROW (display\_record(rec.display\_value, rec.return\_value));  
END LOOP;  
RETURN;  

END;

create or replace function F_GETMODBUSADDRESS (paramid IN NUMBER)

RETURN display_table PIPELINED IS

BEGIN

FOR rec IN (  
  SELECT a.address||' '||a.description as display\_value,  
         a.address as return\_value  
  FROM Tbl\_Modbus a  
    WHERE (paramid IS NULL and a.parameterid IS NOT NULL)  
    OR (paramid IS NOT NULL AND a.parameterid = paramid)  

    ORDER by a.address)  
LOOP  
    PIPE ROW (display\_record(rec.display\_value, rec.return\_value));  
END LOOP;  
RETURN;  

END;

These functions work properly when the page is initially loaded and if there is an existing value in one of the fields. There are dynamic actions for fields A and B that should trigger when a selection is made in the relevant field. In order to avoid a “refresh loop” I have created a field called Trigger_Source. The dynamic action for a selection in field A has the following true actions and conditions.

When System Name BitMap is selected:

1. True Action - set value of P78_TRIGGER_SOURCE to Parameter
2. True Action - Refresh :P78_MODBUS_ADDRESS
3. Condition - Value of Item/Column in Expression 1 is NULL

• Expression 1 = P78_TRIGGER_SOURCE

When Modbus Address is selected:

1. True Action - set value of P78_TRIGGER_SOURCE to Modbus
2. True Action - Refresh :P78_SYSTEM_NAME_BITMAP
3. Condition - Value of Item/Column in Expression 1 is NULL

• Expression 1 = P78_TRIGGER_SOURCE

What happens when I make a selection is that the trigger source field receives the correct value setting but both field A and B display the NULL value text and when you try to look at the lists both fields only display the NULL value in the LOV. Also, the SQL LOV queries, functions and dynamic actions are mirror images of each other except for the field information where necessary. But once I make a selection and both select lists only display NULL values if I made a selection in field B (this doesn't happen if I make a selection in field A) when I refresh the page (not saving changes to data tables, just refreshing page) the select list for field A now shows the filtered list based on the selection I made in field B earlier even though field B is still only showing the NULL value selection and nothing else.

Example dynamic action page:

Sample page item definition - P78_SYSTEM_NAME_BITMAP shown:

I have tried to be as complete as possible in what I have provided, please let me know if there is something I have left out.

I have tried some other solutions, this is where I am currently. Any help, guidance or insight would be greatly appreciated.