Skip to Main Content
Feature Request FR-2755
Product Area Shared Components
Status CLOSED

11 Voters

Ability to use a generic bind variable name in shared component LOV

alawlor Public · Sep 16 2022

Idea Summary
The SQL in a shared component LOV can only use a bind variable if it matches a page item name. 

If my LOV is to be used as cascading from a page item value, the page item's name will be different on each page

Use Case
I have an LOV for departments, which should show values that are different depending on the prior selection of a Division:

SELECT dept_name, dept_code

FROM Departments

WHERE DIvision = :DIVISION

However, the division on page 10 will be called P10_DIVISION, on page 11 will be called P11_DIVISION, etc

When I attach my Shared Component LOV to a Page item P10_DEPT_CODE, I specify P10_DIVISION as my cascading parent item

When I attach my Shared Component LOV to a Page item P11_DEPT_CODE, I specify P11_DIVISION as my cascading parent item

Preferred Solution (Optional)
Apex should positionally match my bind variables within the LOV's SQL, with the list of cascading parent items, even though it is not possible to match by name  

EG. my LOV SQL has two bind variables :B1 and :B2

If my defined list of cascading parent items on the page item using the LOV is  P10_DIVISION and P10_MAKE , then P10_DIVISION is substituted for :B1 and P10_MAKE is substituted for :B2, according their their sequence/position in the SQL on the LOV

This is a great idea! You can already achieve this in APEX today with a slightly different approach.

Known Duplicates

Known duplicates

FR-3194
FR-3194 Want to pass parameter to List of Values without depending on another LOV.
FR-2797
FR-2797 Additional where clause for shared components LOV on page item

Comments

Comments

  • tony.austin OP 2.8 years ago

    You can use the existing function returning SQL in LOVs right now though.  So in your case you could do something like 

    return 
'SELECT dept_name, dept_code
FROM Departments
WHERE DIvision = :P'||:app_page_id||'_DIVISION';

    Each page would need to adhere to the Pxx_DIVISION naming standard, but it solves your problem.

  • jayson hanes Admin OP 2.8 years ago

    you could also use an application item which is global, and reference it in the query, or even P0_ITEM if you wanted it to be more clear

  • raakeshbc OP 2.2 years ago

    Neither solutions are useful if the same LOV is used in a report column as "Plain Text(based on List of Values). Should I submit a new idea for that case? @jayson hanes

  • jlam OP 2 years ago

    I like to add my support to this feature. 

    Currently, I have to define a Page specific LOV so that I can have a query that has the where clause that looks like:

    WHERE field = <Page item name such as :P1_VALUE>;

    If the LOV can be parameterized, I will write the where clause like the following:

    WHERE field = <LOV_Parameter_1>

    And then when you add the LOV to the page, whichever page, I can then map my Page Item to the LOV_Parameter_1 to pass value into it.

    Even with the suggested dynamic SQL approach, this is not structural and it is not “low-code” design philosophy.  It should allow the designer to add parameter to the LOV and use it as such.  Declarative design is what the low-code design philosophy supposed to be.

    Besides, if you have different Items that named differently try to use the same LOV components, you can't use the Dynamic SQL approach.

    I know that you can use the Function Body Returning SQL Query LOV Type as another alternative.  But again, this is not necessarily low-code.