Workaround for issue setting a default value for the APEX 19.2 Popup LOV

Introduction

​Since the introduction of the new and improved Popup LOV in APEX 19.2, I have been working with our clients to migrate them from various Plugin LOV solutions. During this effort I encountered an issue with setting a default value for a Popup LOV column in an Interactive Grid. Specifically, the return value of the LOV was being set by the defaulting code but the display value was not refreshed to show the newly defaulted value. 

---

Background

​At one of our clients, we are building an Order Capture application in APEX. This application allows users to enter orders through APEX and then invoices those orders in Oracle e-Business Suite (EBS). Using APEX, we were able to build a custom fit User Interface which significantly increases the number of orders a user can enter in a day. We were also able to leverage standard EBS views and APIs to interact directly with other EBS data (customers, items, purchase orders etc.). This is just one of the many scenarios where APEX can be used to extend standard EBS functionality.
 
In the order capture APEX page, the user selects a Part Number and the system should default a 'Finish' for the selected Part. Nine times out of ten the default finish is correct, so applying the default saves a significant amount of time for the user. Using the new Popup LOV for the Part and Finish fields also means the user can enter an entire order line without having to use their mouse (which they would have to if we used Select Lists).
 
Here is a simplified view of the Interactive Grid where the user selects a Part and Finish.

The Problem

The problem is when the user selected a Part, the code to set the default Finish was setting the hidden return value OK but the display value was not getting refreshed/changed. What was even more strange was that in about 1 in 10 cases it would refresh the display value.
 
This is how the two columns on the Interactive Grid were configured before we implemented the work around (described later in the post). This screen shot shows you the overall structure of the grid, with the two columns for Part (CUST_ITEM_ID) and Finish (FINISH_CODE).

​The Part field (CUST_ITEM_ID) is setup as a Popup LOV. it has a Dynamic Action which calls PL/SQL code to fetch the default Finish (if there is one). Here is the definition of the Dynamic Action.

The default finish was being set in column FINISH_CODE via the ‘Items to Return’ setting seen in the screen shot below.

​The Finish field is setup as a Cascading LOV, so that when the Parent LOV (CUST_ITEM_ID) is changed, the Finish LOV is also refreshed.

​Kudos to the APEX Development Team

After several attempts at implementing a workaround myself (and lots of Googling), I reached out to the APEX development team. Before doing this, however, I put together a simplified example demonstrating the issue on apex.oracle.com. I can’t stress the importance of this step enough. The APEX development team's time is valuable, I wanted them to focus on finding a solution to the issue, not trying to re-create the issue.
 
At around 10:30pm his time, Joel Kallman received my email and passed it on to Anthony Rayner (based in the UK). When I woke up the next morning, I had an email from Anthony with my sample App updated with a workaround (described below). By 10am the same day, the workaround was implemented in the client's application. If that is not an advertisement for a highly competent, community led development team that cares about their customers then I do not know what is.

​The Workaround

So, what did Anthony come up with? It ended up being just a couple of minor changes to what I described above. Firstly, create two new page items P12_FINISH_CODE and P12_FINISH_NAME. These will temporarily store the derived default Finish (return and display value) when the Part field is changed in the grid. These should be hidden fields with ‘Value Protected’ set off.

​Change the ‘Set Default Finish’ Dynamic Action PL/SQL code to set the two new page items with the default Finish return and display values. Make sure to include these in the ‘Items to Return’ setting.

​The next change is reliant on the FINISH_CODE column being assigned a ‘Static ID’ so that it can be referenced more easily from JavaScript.

Finally, add a step to the Dynamic Action with an ‘Action’ of ‘Execute JavaScript Code’. The JavaScript code below sets the display and return value of the FINISH_CODE column in the Interactive Grid (identified using the static ID ‘finishCode’) to the values in the two page items (P12_FINISH_CODE and P12_FINISH_NAME).

​After doing the above, both the display and return values for Finish are set every time the Part number field is changed.

Conclusion

​First of all, I hope this issue gets addressed in the standard APEX product soon. Workarounds like this are great but the additional page items and JavaScript complicate and bloat the page un-necessarily. Apart from that, it also looks hokey.
 
Having said that, I should say that the APEX development team never cease to impress me with their commitment to the customer. I worked for Oracle for nearly 20 years and never encountered this kind of willingness to engage directly with customers from a product development team. Bravo!

Author

Jon Dixon Co-Founder JMJ Cloud