Filter Data block with a LOV value in Oracle Forms 10g

I need to implement a simple product form from a block of data called "PRODUCTS".

The products are related to a "Partner" through the field "COMPANIES_PARTNERS_ID".

This field will be represented by an LOV to select the Partner for which we want to visualize your products.

If there is not a partner currently selected, all your products should be displayed. And when a partner is selected, only their products should be displayed.

The form will look like this:

The button to the right of the search field should show the LOV and launch the query. I tried the following code as a "Smart Trigger" when I pressed the button. But it does not work well at all. The LOV list appears twice and when no partner is selected, no product appears.

Could someone help me to implement this functionality? Thank you

• You don't need to use ENTER_QUERY command.
• had better using DEFAULT_WHERE set with respect to COMPANIES_PARTNERS_ID. If not selected any of the rows of LOV by pressing CANCEL or dissmissing by X sign, then all of the products will be listed(In this case you'll see the first ID, most probably with value 1, since COMPANIES_PARTNERS_ID is not located at a CONTROL block but at the same block,namely PRODUCTS, with other items. As you go down by down-arrow you'll see the other ID values when your cursor is in COMPANIES_PARTNERS_ID field, seems that Number Of Items Displayed is set to 1 for this field, because PRODUCTS block has been set as 10 items displayed ).

So, you may use the following code in WHEN-BUTTON-PRESSED trigger :

DECLARE
    V_WHERE VARCHAR2(500);
BEGIN   
    GO_ITEM('COMPANIES_PARTNERS_ID');
  IF SHOW_LOV('COMPANIES_LOV') THEN
    V_WHERE:='COMPANIES_PARTNERS_ID='||:COMPANIES_PARTNERS_ID;
  ELSE  
    V_WHERE:='1=1';           
  END IF;           
    SET_BLOCK_PROPERTY('PRODUCTS',DEFAULT_WHERE,V_WHERE);
    CLEAR_BLOCK(NO_VALIDATE);   
    EXECUTE_QUERY;  
END;