1 Reply Latest reply on Feb 12, 2019 10:00 AM by Anthony Rayner-Oracle

    APEX Equivalent to an Oracle Forms POST-QUERY Trigger?

    elmo j h
    elmo j h Feb 5, 2019 10:38 PM

      I would like to create an interactive grid in APEX 5.1 to replace this Oracle Form:

      szawait.JPG

      The form is based on a Waitlist table, which contains the columns that are circled above. 

      The Waitlist table also contains a non-displayed Person Key field.

       

      I am looking for a way to replicate the following behavior:

      1. When existing Waitlist records are displayed, the page should display the ID and Name from the Person table, using the Person Key.
      2. When a new record is created, the page should allow the user to enter the ID, then retrieve and populate the Person Key field and Name fields.

       

      In Oracle Forms, these tasks are accomplished with a POST-QUERY and POST-CHANGE trigger, respectively.

       

      I know how to create similar functionality in APEX using a Form with a Report. 

      I was hoping to use an Interactive Grid, however, so the user has the ability to easily click in the Select box of multiple records to perform other functions on those records (i.e. e-mailing those students, admitting them, etc.)

       

      I'm trying to accomplish #1 using a "GET_ID_AND_NAME" Dynamic Action on the Person Key field.  I've tried an After Refresh event and a Change event.  Is that the wrong approach?  I'm wondering if I'm stuck in Oracle Forms thinking (relating Dynamic Actions to Triggers) and there is another way I should be retrieving these fields for existing records, such as using a Process, Computation, or Validation?

       

      There are over 300,000 records in the Person table, so an LOV is, unfortunately out of the question.

       

      Thanks

        • 1. Re: APEX Equivalent to an Oracle Forms POST-QUERY Trigger?
          Anthony Rayner-Oracle
          Anthony Rayner-Oracle Feb 12, 2019 10:00 AM (in response to elmo j h)

          Hi,

           

          Let's first try the simlest approach. Have you tried the standard Popup LOV item type, and combine this information into one column? You could set it to 'Fetch = No Fetch' to avoid immediately fetching rows when the popup opens, requiring the user to enter a search term, which could help with performance.

           

          It would look something like this...

           

           

          I used the emp table in place of you person table, but the idea is the same, have a Popup LOV defining an LOV that displays both the ID and NAME (and returns the ID). I set the Popup LOV to 'Not enterable' as you don't want to allow entering new people here, and because the ID and NAME are both in the display column, they can both be searched for in the popup. Would that work for you?

           

          Also, regarding your 'the user has the ability to easily click in the select box of multiple records to perform other functions on those records...', John Snyders has a good example of this in the IG cookbook app, see page 9 'Update Selection'.

           

          Hope that helps.

           

          Regards,

          Anthony.