Forum Stats

  • 3,767,858 Users
  • 2,252,726 Discussions
  • 7,874,366 Comments

Discussions

19.2 LOV based on Web Source - Limit the result set

Tomek
Tomek Member Posts: 221 Bronze Badge
edited Nov 30, 2019 3:34PM in APEX Discussions

I have a form that uses Cascading LOV's which are based on the Web Source Module (REST calls).

The first item selects the Department and the second one needs to bring "Aliases" based on the department selected. I tried many variations and cannot get it to work properly. It seems like I cannot reference an item (in this case P11_ORG_NUMBER) within the "Post Processing SQL Query". If I hard code the "org number" to specific value it works fine but adding "org = :P11_ORG_NUMBER" results in no data at all. I tried various options to reference an item "&P11_ORG_NUMBER." or V('P11_ORG_NUMBER') and nothing seems to be working.

Am I doing something wrong or is it a bug in 19.2.

Here is my setup:

Screen Shot 11-27-19 at 10.31 AM.JPG

Screen Shot 11-27-19 at 10.32 AM.JPG

LOV setup based on Web Source:

Screen Shot 11-27-19 at 10.35 AM.JPG

An page item setting to use the LOV

Screen Shot 11-27-19 at 10.37 AM.JPG

Screen Shot 11-27-19 at 10.36 AM.JPG

Tagged:

Best Answer

  • Carsten Czarski-Oracle
    Carsten Czarski-Oracle Member Posts: 1,257 Employee
    edited Nov 29, 2019 3:29PM Accepted Answer

    Hi Tomas,

    I just created an LOV on a REST service

    https://earthquake.usgs.gov/earthquakes/feed/v1.0/summary/all_week.geojson

    Post Processing Query is (as yours) simply selecting all columns and adding a WHERE ROWNUM <= PX_MAX_ITEMS filter.

    On the page ...

    • Create a page item named PX_MAX_ITEMS
    • Create a Popup LOV item which uses the shared LOV and which uses PX_MAX_ITEMS as the Cascading LOV Parent item. You always need a Cascading LOV parent item, since APEX needs to send the value of (here: PX_MAX_ITEMS) to the server after it changed in the browser. Otherwise your Post Processing Query would not see the current value of PX_MAX_ITEMS.

    But either way: I won't be able to debug screen shots. I would need to look into a test case in order to see whether there is a bug or not. You might use SQL Workshop > REST Services to create some simple REST services on top of the EMP table. Based on these we can create web source modules, LOV and so on in order to illustrate the issue ...

    Best regards

    -Carsten

Answers

  • Carsten Czarski-Oracle
    Carsten Czarski-Oracle Member Posts: 1,257 Employee
    edited Nov 28, 2019 5:59AM

    Hi Tomas,

    I just built a simple cascading LOV example using Post Processing SQL and the EMP table - it worked just fine. In the Post Processing query, you need to reference the item using the normal colon-syntax (:PX_ITEM). In the LOV, make sure to use the Cascading LOV Parent Item and Items to Submit attributes.

    However, since your LOV depends on the P11_ORG_NUMBER item I would consider that as the Cascading LOV Parent Item ....

    If that does not help, I would need to have a look into this ... can you stage a test case on apex.oracle.com ...?

    Best regards

    -Carsten

  • Tomek
    Tomek Member Posts: 221 Bronze Badge
    edited Nov 29, 2019 3:03PM

    Hello Carsten,

    I will argue that there is an issue with the LOV based on REST service and limiting the result set  by using the Post Processing SQL option. Maybe it works fine for local LOV's as I would expect to (as it always did) but not so for the Web Source. I would love to create a test case but really not sure how to do it since I'm using the REST calls which are internal to my company.

    Anyway here is another proof/case:

    I create new item "Fiscal Authority" based on the Web Source (simple REST) - it is not cascading LOV. It accepts the "Billing Alias" as query parameter which I'm passing via LOV Web Source Parameter. The "Amount Limit" is additional parameter I would like to limit the result set using the Post Processing SQL entry.

    Here is the part of the page:

    Screen Shot 11-29-19 at 11.46 AM.JPG

    If I edit the query and hard code 100000 as a value all works fine

    Screen Shot 11-29-19 at 11.51 AM.JPG

    As you see the LOV return values

    Screen Shot 11-29-19 at 11.53 AM_LI.jpg

    Now I change the SQL query to reference the page item

    pastedImage_3.png

    I save the change and refresh the page and I get NO RESULT from the LOV. There is no error on the page.

    pastedImage_4.png

    Now if I reverse the latest change and go back to hard coding the value all works fine again.

  • Carsten Czarski-Oracle
    Carsten Czarski-Oracle Member Posts: 1,257 Employee
    edited Nov 29, 2019 3:29PM Accepted Answer

    Hi Tomas,

    I just created an LOV on a REST service

    https://earthquake.usgs.gov/earthquakes/feed/v1.0/summary/all_week.geojson

    Post Processing Query is (as yours) simply selecting all columns and adding a WHERE ROWNUM <= PX_MAX_ITEMS filter.

    On the page ...

    • Create a page item named PX_MAX_ITEMS
    • Create a Popup LOV item which uses the shared LOV and which uses PX_MAX_ITEMS as the Cascading LOV Parent item. You always need a Cascading LOV parent item, since APEX needs to send the value of (here: PX_MAX_ITEMS) to the server after it changed in the browser. Otherwise your Post Processing Query would not see the current value of PX_MAX_ITEMS.

    But either way: I won't be able to debug screen shots. I would need to look into a test case in order to see whether there is a bug or not. You might use SQL Workshop > REST Services to create some simple REST services on top of the EMP table. Based on these we can create web source modules, LOV and so on in order to illustrate the issue ...

    Best regards

    -Carsten

  • Tomek
    Tomek Member Posts: 221 Bronze Badge
    edited Nov 30, 2019 3:34PM

    Carsten, I rest my case. You are absolutely right. The problem was related to not using the referenced item without adding it to a Cascadin LOV parent item. Thank you for pointing it out. I was not aware I must do it in order to get the value visible in the Post Processing Query.

    Regards, Tomek