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:
- The legacy Form Pagination process is limited to a maximum 2 columns for the uniqueness constraint, and 2 columns for the navigation order.
- 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.
- 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. - 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
Cristian
12 June 2020 - 10:58 pm
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!
Jeffrey Kemp
12 June 2020 - 11:04 pm
Hi Cristian,
Nowhere! That code, intended for education purposes only, was pulled from the debug logs and is generated by APEX itself.
Jeff
shyam
28 June 2020 - 5:02 pm
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.
Edgar
3 July 2020 - 1:48 am
Is there any tips to get a First/Last page navigation?
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
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?
Jeffrey Kemp
18 August 2021 - 11:08 pm
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
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
Jeffrey Kemp
19 August 2021 - 10:11 pm
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
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
Lokesh
13 June 2022 - 9:35 pm
Hi Jeff,
Great article and well explained. I am working on a form region with a 2 column primary key. When using the next primary key item(s) field, I am trying to give 2 page items delimited by a comma. But Apex doesnt seem to like it and keeps saying it cannot find item id for “my_item1, my_item2” on the page. It is almost as if Apex is combining them into 1 long item id and trying to look for it!
I am using Apex version 20.2 – Any ideas what I may be doing wrong?
Thanks
Lokesh
Jeffrey Kemp
13 June 2022 - 9:47 pm
Hi Lokesh,
That doesn’t sound right – maybe there is a bug affecting APEX 20.2 – can you reproduce it on apex.oracle.com?
Lokesh
16 June 2022 - 5:35 am
Hi Jeff,
Thanks for the reply. I have used a PLSQL process to get the information and use it for pagination for now. I will see if I can replicate it on apex.oracle.com and let you know.
I was working on removing legacy stuff from an app and this app in question had the legacy form regions that I was converting to the new form region in Apex. Not sure if that had anything to do with this, though I did create a new region rather than editing the existing one, and I still ended up with the same problem.
Thanks
Lokesh