Next/Previous Record: how APEX does it

It’s not a common requirement in my experience, but it does come up every now and then: a customer is happy with a simple Report + Form but when they open a record, they want to be able to make their changes and go to the “next” record in one button click, instead of having to go back to the report and select the other record.

In the Sample Database Application, page 29 (Order Details) implements “Next” and “Previous” buttons which allow the user to save and open another record in one action.

These buttons are implemented using a legacy Form Pagination process. This process sets some hidden items (P29_ORDER_ID_NEXT, P29_ORDER_ID_PREV, P29_ORDER_ID_COUNT) based on a query on a specified table (DEMO_ORDERS) with a specified unique identifier (ORDER_ID) associated with a primary key item (P29_ORDER_ID). The process requires a navigation order, specified by one or two columns (ORDER_ID, in this instance) in order to know what would the “next” and “previous” records be. If there is no next or previous record is found, the buttons are hidden.

When the page is submitted, the Next and Previous buttons submit a request (GET_NEXT_ORDER_ID or GET_PREVIOUS_ORDER_ID, respectively). After the ApplyMRU process has run, one of the relevant Branches will redirect the client back to the same page (p29) and set P29_ORDER_ID to either &P29_ORDER_ID_NEXT. or &P29_ORDER_ID_PREV. which causes the page to load the relevant record.

Some things to note with this approach:

  1. The legacy Form Pagination process is limited to a maximum 2 columns for the uniqueness constraint, and 2 columns for the navigation order.
  2. The navigation order of records will not match any custom sort order or filtering the user might have used on the report; so after the user opens the “first record” in the report, the form will not necessarily navigate to the “next record” that they might expect.
  3. The Next / Previous record IDs are queried when the page is initially loaded, so if anything has changed prior to the user clicking “Next” or “Previous”, it’s possible the user will inadvertently be directed to a record that is not actually “next” or “previous” to the record as it is now.
    In the worst case, if someone had deleted a record, the page would show “record not found”.
    In a perhaps less problematic case, if someone else has just inserted a new record with a unique identifier that happens to fall between the user’s previous record and the record they are navigating to, the user will effectively “skip over” the newly inserted record and might be led to believe it doesn’t exist.
  4. For large datasets there may be a performance penalty whenever each user loads the form since the page must issue additional queries to find the IDs for the “previous smaller” and “next larger” record, as well as to get the total number of records, and the position in the overall dataset of the current record (so it can set the “count” item to something like “8 of 10”). This involves the execution of a second query (in addition to the original query which gathered the data for the record being viewed).

These are not necessarily insurmountable or showstopping issues but should be kept in mind for forms using this approach.


With the new APEX Form feature, the above approach can still be used in much the same way – the attribute settings are a little different.

The page has a process before header of type Form – Initialization. This process has the following optional settings: Next Primary Key Item(s), Previous Primary Key Item(s), and Current Row/Total Item. To use this feature you must first create items (usually hidden) and then set these attributes to the item names.

Note that the Next / Previous Primary Key Item(s) attributes accept a comma-delimited list of items which allows them to support a compound key; I haven’t tested it but I expect this means it can support more than 2 columns.

The form will automatically populate these hidden items with the Order ID of the next and previous record, and will set the Current Row/Total Item to something like “8 of 10”. You can then use these items how you wish, e.g. as per the legacy pagination scheme, add the “Next” and “Previous” buttons, and create the navigation Branches to open the form with the relevant records.

To control the navigation order, you must set the Order By attribute on the form region. If this is not set, the navigation order is essentially unpredictable (APEX uses null as the sort order).

Same as the legacy pagination process, the form executes two queries: one to load the data for the form, and a second to gather the Next, Previous, Position and Count for the record, using SQL like this (I’ve simplified it a bit):

select i.*
from (
    select ORDER_ID, CUSTOMER_ID, ORDER_TOTAL, ...
           lead(ORDER_ID,1) over (order by ORDER_ID) as APX$NEXT_1,
           lag(ORDER_ID,1) over (order by ORDER_ID) as APX$PREV_1,
           row_number() over (order by ORDER_ID) as APX$ROWN,
           count(*) over () as APX$TOTAL
    from (
        (select /*+ qb_name(apex$inner) */
                d.ORDER_ID, d.CUSTOMER_ID, d.ORDER_TOTAL, ...
         from (select x.* from DEMO_ORDERS x) d
    )) i 
) i where 1=1 
and ORDER_ID=:apex$f1

It should be noted that the comments above about how concurrent record inserts and deletes by other users, and about report filters and sorting, also apply to the new form process.


In the past I built a system where it was important that the “Next” / “Previous” buttons should allow the user to navigate up and down the records exactly as shown in the report, respecting user-entered filters and sort order. The approach I took was to gather the IDs into a collection and pass this to the form when the user opened a record. I described the implementation and limitations of this approach in an older blog post which I expect still works today: Next/Previous buttons from Interactive Report results

Loading large GeoJSON objects
Friendly URL structure

Comments

  1. Hello! A very useful article. I have only one question. Where does the last code need to be written? I am referring to: select i.* from (…) i where 1=1
    and ORDER_ID=:apex$f1

    Thank you in advance!

    • Hi Cristian,

      Nowhere! That code, intended for education purposes only, was pulled from the debug logs and is generated by APEX itself.

      Jeff

  2. Hi Jeffrey,

    I read the article next, prev in form but i am unable to implement the same.

    if any demo app is available to learn fast.

    Thanks.

  3. Is there any tips to get a First/Last page navigation?

  4. ahmed , abu mahmoud
    13 October 2020 - 3:45 am

    Hi,
    to get demo example, create master detail drill down, and get the detail page. thats all.
    thank you,

    yours, Ahmed

  5. Fernando Palomeque
    18 August 2021 - 10:49 pm

    Hi, this code generated by apex, Is it possible through some ordinary process to apex that does not build it like this? Also, does this form of query construction affect performance?

    • Hi Fernando,

      If you’re asking if you can supply your own process to compute the Next/Previous IDs, the answer is Yes – simply don’t set those highlighted Settings to the items and the Form process will not compute them. You can then write your own process to compute them using whatever method works for you.

      The query performance will depend largely on the volume of data and on whether there are suitable indexes that allow the query to quickly find the records.

      Jeff

  6. Fernando Palomeque
    19 August 2021 - 3:13 am

    Hi Jeffrey, thanks for your answer, my question is focused on the following:

    I have the following query that is loaded in Classic Report:

    SELECT a.emp_descripcion, a.emp_id
    FROM cbe.employee a
    WHERE a.emp_status = 0
    AND a.rol_id = 58
    AND :f_ses_id IN (677,457,1124)

    But, analyzing from the session monitor, I can see that the query is being constructed differently:

    “select * from(select a.*,row_number() over (order by null) apx$rownum from(select i.*, count(*) over () as APEX$TOTAL_ROW_COUNT
    from (select *
    from ((select /*+ qb_name(apex$inner) */* from (SELECT a.emp_descripcion, a.emp_id
    FROM cbe.employee a
    WHERE a.emp_status = 0
    AND a.rol_id = 58
    AND :f_ses_id IN (677,457,1124)
    ) d
    )) i
    ) i where 1=1
    order by 1 asc nulls last
    )a
    )where apx$rownum<=:p$_max_rows"

    I would like to know if this is normal? and if this causes any effect on the performance of the database .

    Thanks

    • Hi Fernando,

      Yes, that’s what I’d expect – APEX wraps your query in extra select statements that determine the total number of records and their sequence so it can derive the Next, Previous and Total item values.

      This may very well impact the performance of your query. Whether this is an issue will depend on your query, the data volumes and patterns, and indexes available to the query optimiser.

      If you are experiencing performance issues with it, one thing you might do is run your page in debug mode level 9 – i.e. go into debug mode, and change the “debug=YES” URL parameter to “debug=LEVEL9” and then reload the page. When you examine the debug log you will find (after a large number of log entries) the execution plan for your query. The execution plan details exactly how the database executed the query, including statistics of how many records and bytes were retrieved.

      Jeff

  7. Fernando Palomeque
    20 August 2021 - 5:15 am

    Thanks Jeffrey for your answer, the subject is clearer to me and I will take into account the recommendations

Leave a Reply

Your email address will not be published / Required fields are marked *