Skip to Main Content
Feature Request FR-3025
Product Area Shared Components
Status ROADMAP

77 Voters

Add where clause to LOVs on item properties when use shared components LOVs

eddie.molina Public · Mar 13 2023

Idea Summary
Add a new property to “List of Value” item properties when use LOV - Type: Shared Component, to filter the LOV just in the same way you can do it when you define a report or another type of regions based on a table.

Use Case
The typical case using the DEPTO – EMP tables: 

  • You define in Shared Components a LOV DEPTOS returning all departments on DEPTO table
  • You define a Shared Component LOV EMPLOYEES returning all employees on EMP table. Both lovs will be used on different pages to show department name or employee name.
  • You want to create a Cascading LOV on page X between items PX_DEPTO and PX_EMPLOYEE where this items are defined as LOVS and use lovs DEPTOS and EMPLOYEES respectively, and PX_EMPLOYEE shows on only employees for the department selected in item PX_DEPTO

Preferred Solution (Optional)
Add a “where clause” to item List of Value properties.

This is currently on the roadmap for a future release of Oracle APEX.

Comments

Comments

  • mike_kutz OP 1.9 years ago

    My use case

    • LOV needs to be a dynamic list based on a Page Item but Page Item Name are different on different pages
  • nicolas pilot OP 1.9 years ago

    Yes, another way would be to have LOV parameters that could be used in the query as bind variables. 
    So, when using a LOV on a page, we could specify the value of those parameters which could be static, page item, expression, etc.

  • hcarvajal OP 1.9 years ago

    Eddie  I think your request is excellent, I hope you can implement it

  • zayra.carvallo OP 1.9 years ago

    @pilot.nicolas, I totally agree with your suggestion, bind variable will be the way to improve shared component's LOV.  As for now, the workaround that we do, it's to use global variable in the where section of the Shared LOV, but it comes with some extra coding using dynamic action or processes to be able to assign the wanted value to the global variable before the LOV is executed, which is kind of tricky when the wanted value depends on another Shared LOV.

  • luciano.chiorato OP 1.9 years ago

    This idea is very important! my list of lov are greatest of my list the clients

  • ron.m OP 1.8 years ago

    Yes great idea. Having cascading LOV's only then having to copy the shared LOV sql and alter the code on the page to include the parent condition. By only having to pass the values of the parent would allow only the shared LOV to be needed.

  • eddie.molina OP 1.8 years ago

    Thanks APEX TEAM!

  • horluklgg OP 1.1 years ago

    I'm using this workaround till ths will be release:

    Use List of Values Type "Function Body returning SQL Query"

    with this code:

    declare
     v_sql varchar2(4000);
    begin
       select list_of_values_query 
       into v_sql
       from  APEX_APPLICATION_LOVS where application_id = :APP_ID and list_of_values_name = 'MY_LOV';
       return 'select display_value, return_value from ('||v_sql||') where return_value in (107,108) ';
    end;

    where all of my LOVs have the columns names display_value and return_value so I can use this snippet.

  • sarafuddin000 OP 1.1 years ago

    @horluklgg 

    declare
v_sql varchar2(4000);
begin
  select list_of_values_query 
  into v_sql
  from  APEX_APPLICATION_LOVS where application_id = :APP_ID and list_of_values_name = 'BRAND';
  return 'select display_value, return_value from ('||v_sql||') where com_code = '''||:P50_COMPANY_NAME||''' and dos_code = '''||:P50_DOSAGE||'''';
end;

    with Bind variables are working fine, but its very much looks like a select list with search functionality! Only single column is returns to display whether LOVs main feature is displaying multiple columns. Is there any ways to do this?

  • peter.heller OP 1.1 years ago

    Can't you put a where clause in the LOV ‘shared component’ definition pointing to an application item that you set in the application?

    Px_Field = :application_item

  • mswvette OP 10 days ago

    I have a utility function that gets the value of a page item using the current page id.  apex_util_pkg.valp generates the page item name using v('APP_PAGE_ID'), then returns the v(generated_page_item_name).

    select a.*
    from edw_dim_it_product_v a
    where a.src_deleted_in        = 'N'
    or a.edw_dim_it_product_id    = apex_util_pkg.valpn('EDW_DIM_IT_PRODUCT_ID')