Next/Previous buttons from Interactive Report results

What could be simpler than a set of "Next" and "Previous" buttons?

What could be simpler than a set of “Next” and “Previous” buttons?

I love Interactive Reports, they make it easy to deliver a lot of power to users for very little development effort. However, with that power comes some complexity for building certain features not available in the base Apex toolset.

I had an IR with a fairly costly and complex query behind it, linked to another screen to view the details for a record. The users wanted to be able to view each record from the search result without having to click each record, go back to the Search, and click the next record, etc. Instead, they wanted “Next” and “Previous” buttons on the Single Record screen which would allow them to navigate the search results.

There are a few ideas on the web around how to implement this, and I considered two of them:

1. On the Single Record screen, run the query to determine what the next and previous records are in relation to the currently viewed record.

2. On the Search screen, instead of querying the tables/views directly, call a database procedure to store the query results in a collection; then, both the Search and the Single Record screens can query the collection.

Some problems with solution #1 include (a) the query might be quite expensive, so the Single Record screen may be too slow; and (b) if the data changes, the user might get unexpected results (“that record didn’t appear in my search results?”).

Another problem that both of these solutions share is that if the user has used any of the IR features such as custom filters, sort orders, etc. we won’t necessarily pick these up when determining what the Next/Previous records are. Certainly, the collection approach won’t pick these up at all.

Instead, I’ve gone for a different approach. Firstly, I’ve simplified the problem by dictating that the Next/Previous buttons will only allow the user to navigate the list of records they most recently saw on the search screen; therefore, if the results are paginated, we’ll only navigate through that page of results. If the user wants to keep going, they’ll go back to the Search screen and bring up the next page of results.

The solution is quite simple in concept but was a bit tricky to implement. The basic idea is that I encode the record IDs in the HTML generated by the report, and use some Dynamic Actions to grab the resulting list of record IDs, store them as a CSV in a page item, which can then be parsed by the View/Edit screen.

Here it is in detail, ready for you to try, critique or improve (in this example, my record’s id column is called ft_id, my Search screen is p23, and the Single Record screen is p26):

Part A: Save the IDs shown on the Interactive Report

1. In the Link Column on the interactive report region, add class="report-ft-id" to the Link Attributes.

2. In the Link Icon, add data-ft-id=#FT_ID# into the img tag.link_column
I added this to the img bit because the Link Column field doesn’t do the #FT_ID# substitution, unfortunately.
3. Add the following javascript function to the page’s Function and Global Variable Declaration:

function ft_id_list() {
  return $(".report-ft-id >img")
         .map(function(){return $(this).attr("data-ft-id");})
         .get()
         .join(",");
}

This function searches for any records shown on the page by searching for the report-ft-id class, extracts from each one the img node’s data-ft-id attribute, maps these into an array, and then squashes that array down to a comma-separated list.

4. Create a hidden item P23_FT_ID_LIST which will store the resulting list.

5. Create an application item FT_ID_LIST which will be read by the Single Record page.

6. Create a Dynamic Action triggered by the event Page Load, which saves the list of IDs into session state by performing two actions:
(a) Set Value based on a JavaScript Expression, ft_id_list();, assigned to the item P23_FT_ID_LIST
(b) Execute PL/SQL Code which runs the code :FT_ID_LIST := :P23_FT_ID_LIST;. Make sure to set Page Items to Submit to P23_FT_ID_LIST and set Page Items to Return to FT_ID_LIST.
This dynamic action will only fire when the page is initially loaded.

7. Copy the Dynamic Action, but this time set the event to Custom and the Custom Event to apexafterrefresh. This way, whenever the user changes the rows shown in the report (e.g. by paginating, or changing filters or sort order, etc.), the list will be refreshed as well.

Part B: Add the Next/Previous buttons

8. Create some procedures on the database (e.g. in a database package) which take a list of IDs and a “current” ID, and return the next or previous ID in the list:

FUNCTION next_id
  (id_list IN VARCHAR2
  ,curr_id IN VARCHAR2
  ) RETURN VARCHAR2 IS
  buf     VARCHAR2(32767) := ','||id_list||',';
  search  VARCHAR2(100) := ','||curr_id||',';
  pos     NUMBER;
  new_id  VARCHAR2(32767);
BEGIN
  pos := INSTR(buf, search);
  IF pos > 0 THEN
    -- strip out the found ID and all previous
    buf := SUBSTR(buf, pos+LENGTH(search));
    -- chop off the first ID now in the list
    IF INSTR(buf,',') > 0 THEN
      new_id := SUBSTR(buf, 1, INSTR(buf,',')-1);
    END IF;
  END IF;
  RETURN new_id;
END next_id;

FUNCTION prev_id
  (id_list IN VARCHAR2
  ,curr_id IN VARCHAR2
  ) RETURN VARCHAR2 IS
  buf     VARCHAR2(32767) := ','||id_list||',';
  search  VARCHAR2(100) := ','||curr_id||',';
  pos     NUMBER;
  new_id  VARCHAR2(32767);
BEGIN
  pos := INSTR(buf, search);
  IF pos > 0 THEN
    -- strip out the found ID and all following
    buf := SUBSTR(buf, 1, pos-1);
    -- chop off all but the last ID in the remaining list
    IF INSTR(buf,',',-1) > 0 THEN
      new_id := SUBSTR(buf, INSTR(buf,',',-1)+1);
    END IF;
  END IF;
  RETURN new_id;
END prev_id;

9. Add two hidden items to the Single Record screen: P26_FT_ID_NEXT and P26_FT_ID_PREV.

10. On P26_FT_ID_NEXT, set Source Type to PL/SQL Expression, and set Source value or expression to next_id(:FT_ID_LIST,:P26_FT_ID), and similarly for P26_FT_ID_PREV to prev_id(:FT_ID_LIST,:P26_FT_ID).

11. Add buttons Next and Previous, with Action set to Redirect to Page in this Application, pointing back to the same page, but setting the P26_FT_ID to &P26_FT_ID_NEXT. and &P26_FT_ID_PREV., respectively.

This method means that it doesn’t matter if the query behind the report changes, or if the user adds filters or uses different saved reports; the Single Record screen doesn’t need to know – it just needs to know what the list of IDs the user most recently saw on the Search screen were.

Some downsides to this approach include:

  • Server load – the dynamic actions on the report refresh, which causes it to do an ajax call to the database on every refresh of the IR. But at least it saves the View/Edit screen re-executing the query on every page load.
  • Rows Per Page limitation – since we save the list of IDs as a CSV in a single string variable, we may have issues if the user sets Rows Per Page to “All” with a large result set – so we need to limit the Maximum Rows Per Page to about 3,000 (this assumes that all the IDs will be less than 10 digits long) to fit in the 32,767 char limit. YMMV.
  • Duplicate records – this method assumes that the IDs shown in the report will always be distinct. If this is not true, the next/previous functions will not allow the user to navigate through the whole list.
Code can be scary when you simplify it
AUSOUG Conference Perth, November 2014

Comments

  1. Hi Jeff,

    Could you please post a demo video for this?

    Jeevan.

  2. Hi Jeff,

    I executed as you mentioned but facing issue in the below step.

    7. Copy the Dynamic Action, but this time set the event to Custom and the Custom Event to apexafterrefresh. This way, whenever the user changes the rows shown in the report (e.g. by paginating, or changing filters or sort order, etc.), the list will be refreshed as well.

    Could you please elaborate the steps how to perform this?

    Thanks,
    Jeevan.

    • Hi Jeevan,

      It’s not clear from your comment what exactly your issue is. You create a new dynamic action as a copy of the dynamic action created in the previous step; change the event to Custom and set the Custom Event to “apexafterrefresh”.

      If I had to guess, you might be on a later version of APEX. This post was written about APEX 4.2 over 5 years ago, so I’m not entirely sure that everything would still work exactly as described on more recent versions of APEX – some properties may have new names, for example. It might help to know what version of APEX you’re using.

      Jeff

  3. Hey Jeff,

    It worked am using 4.2 only got the desired output exactly..thanks a ton for this post.

    Jeevan.

  4. Hi Jeff,

    Is this still the way to go about solving this? The post was written some time ago so wondering if there is anything that “helps” with this out of the box now (20.1).

    Can see there are settings in the “Form – initalization” process for Next Primary Key Item(s)/Previous Primary Key Item(s), but not sure how these get used.

    David.

  5. Hi Jeff,
    I am using APEX 4.2 and I have a requirement like this which I’m struggling to solve. On the report page I have a list of managers and when I select a manger the list of employees under him will open. I am trying to create a navigation for these employees under a manger, but since the employee IDs are not in a sequence in emp table for this manager, the in built pagination functionality is not working. I am new to APEX and not sure how to go ahead with this.
    Sample data:
    Emp table:
    Id mgrid
    19459 240
    23456 240
    25678 240

    can you please help?

    • Jeffrey Kemp
      10 May 2020 - 9:02 pm

      Hi Venkata,

      This article is a sketch of a solution but it doesn’t pretend to connect all the dots, because there are so many ways this sort of thing can be done, depending on the constraints of each application.

      In your case the solution hinges on the query you use to populate the list of employee IDs. Your function that generates this list would, I think, need to have Manager ID as a parameter; it would then return a list of employee IDs under the selected manager.

      Your employee view would then show this list of employees and allow the user to navigate forward and backward through the list, using the technique described in this article.

      I hope this gives you a few hints on where to go next in your implementation.

      Jeff

Leave a Reply

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