I’m working on an Oracle APEX app, using Master/Detail drill down forms. I’m having difficulty working out how APEX uses a value looked up as the result of an SQL statement run in one event in a subsequent SQL statement. In JavaScript, this is async callbacks and/or Promises; from the difficulty I’m having here it’s pretty clear that there’s an async mechanism at work behind the scene.

I’m working with APEX version 22.2.

Where I’m having trouble is when inserting a new record. I’d like to be able to look up some values based on a value that the user enters, when the user leaves the field.

The fields in question are:

PO_NUM

Ok VENDOR_CODE
VENDOR_NAME

I would like, when the user enters a PO_NUM, to look up the VENDOR_CODE, and from there look up the VENDOR_NAME in a subsequent SQL statement that uses the looked-up VENDOR_CODE.

These are all items on a form. I have this Dynamic Action set up for PO_NUM:

1. Event: Lose Focus
2. Selection Type: Item(s)
3. Item(s): PO_NUM

And under the True section:

1. Action: Set Value
2. Set Type: SQL Statement
3. SQL Statement: (SQL statement that looks up VENDOR_CODE using the value entered in PO_NUM)
4. Items to Submit: PO_NUM
5. Affected Elements Selection Type: Item(s)
6. Item(s): VENDOR_CODE

This all works as intended: user enters a PO number in the text field, tabs off of it, and the Vendor Code shows up in the VENDOR_CODE item on the form. (Note: I don’t know if this is relevant, but on the PO_NUM field properties themselves, I have a custom attribute readonly. I wanted this field to be read only, but for denormalization purposes I also need to insert its value into a field on submit.)

Where I’m running into trouble is using that Vendor Code to look up the Vendor Name in a subsequent SQL statement, and put the result in the VENDOR_NAME item. I’ve tried a number of things, without any luck. My most recent theory has been to use the Change event for VENDOR_CODE, with the idea that the value won’t change until it comes back from the server, and then I can use it for the next SQL statement. But doesn’t work.

For the record, here’s the Dynamic Action I have in the VENDOR_CODE item:

1. Event: Change
2. Selection Type: Item(s)
3. Item(s): VENDOR_CODE

And in the True section:

1. Action: Set Value
2. Set Type: SQL Statement
3. SQL Statement: (SQL statement that uses :VENDOR_CODE to look up the associated VENDOR_NAME in a vendor table`)
4. Items to Submit: (none)
5. Affected Elements Selection Type: Item(s)
6. Item(s): VENDOR_NAME

I’m thinking that there’s a problem with the async part of this, but I wonder if I’m missing something simple. How do I get this running?