Tag: tips-&-tricks

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");})

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
  buf     VARCHAR2(32767) := ','||id_list||',';
  search  VARCHAR2(100) := ','||curr_id||',';
  pos     NUMBER;
  new_id  VARCHAR2(32767);
  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;
  RETURN new_id;
END next_id;

FUNCTION prev_id
  (id_list IN VARCHAR2
  ,curr_id IN VARCHAR2
  buf     VARCHAR2(32767) := ','||id_list||',';
  search  VARCHAR2(100) := ','||curr_id||',';
  pos     NUMBER;
  new_id  VARCHAR2(32767);
  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;
  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.

Show an animated “Please wait” indicator after page submit

My application normally responds to button clicks with sub-second performance, but there were a few operations where users can initiate quite long-running transactions (e.g. up to 15 seconds long in one case where it was hitting an eBus interface thousands of times).

When the user clicks the button, I want the page to show a “Please Wait” message with an animated running indicator (I won’t call it a “progress bar” even though it looks like one, because it doesn’t really show progress, it just rotates forever) until the page request returns.


To do this I added the following to my application, based largely on this helpful article.

1. Add a HTML region on Page 0 (so it gets rendered on every page) at Before Footer, with:

<div id="runningindicator">
Processing, please wait...
<div id="runningindicator-img"></div>

2. Add the following to the global CSS file for my application:

div#runningindicator {
  display: none;
  background-color: #FFF;
  padding: 30px;
  border: 1px solid;
  border-color: #CCC;
  box-shadow: 2px 2px 2px #AAA;
  border-radius: 4px;
  position: absolute;
  top: 100px;
  left: 50%;
  margin-left: -110px;  /* the half of the width */
div#runningindicator-img {
  background-image: url(/i/processing3.gif);
  background-repeat: no-repeat;
  width: 220px;  /* the exact width of the image */
  height: 19px;  /* the exact height of the image */

3. Add the following to the global javascript file for my application:

function run_long_request (request, warnmsg) {
  if (!warnmsg || confirm(warnmsg)) {
    // disable all buttons on the page
    var btns = $("a[role='button']");
    $x_disableItem(btns, true);

4. Change the button:

Action = Redirect to URL
URL Target =

  'Are you sure you wish to approve this transaction?');

When clicked, the button runs my javascript function which first prompts the user to confirm, and if they do, it disables all the buttons on the page, shows the running indicator, and submits the request (which might be the name of the button, for example).

If I omit the second parameter, the function skips the confirm popup and submits straight away.

Known Issue: the animated gif doesn’t seem to animate in IE8. So far I haven’t worked out how to solve this, except to burn IE8 with fire and extreme prejudice. I’ve tried using setTimeout to delay showing the div but it stubbornly stays frozen.

EDIT: thanks to Peter Raganitsch who alerted me to a simpler option, that doesn’t need the region or the CSS, and animates in IE8:

function run_long_request (request, warnmsg) {
  if (!warnmsg || confirm(warnmsg)) {

Mind you, building this sort of thing from scratch was a useful exercise to learn the CSS and javascript tricks necessary. And another thing re-learned: there’s almost always a simpler way.

Add a Dynamic Total to a Tabular Report

I have a Tabular Report with an editable Amount item. When the page loads, the total amount should be shown below the report; and if the user updates any amount on any row, the total amount should be updated automatically.

Note: this method does not work if you have a tabular report that might have a very large number of records (as it relies on all records being rendered in the page at one time).

1. Make sure the report always shows all the records. To do this, set the Number of Rows and the Maximum Row Count to a large number (e.g. 1000).

2. Add an item to show the total, e.g. P1_TOTAL_AMOUNT. I use a Number field, and add “disabled=true” to the HTML Form Element Attributes so that the user won’t change it.

3. Examine the generated HTML to see what ID is given to the amount fields in the tabular report. In my case, the amount field is rendered with input items with name “f04” and id “f04_0001”, “f04_0002”, etc.

4. Add the following code to the page’s Function and Global Variable Declaration:

function UpdateTotal () {
  var t = 0;
  $("input[name='f04']").each(function() {
    t += parseFloat($(this).val().replace(/,/g,''))||0;

This strips out any commas from the amounts before parsing them as Floats and adding them to a running total; it finally formats the total using my formatMoney function and updates the total amount item.

5. Add the following to the page’s Execute when Page Loads:


To prime the total amount field when the page is loaded, I have a Before Header process that calculates the total based on a simple query on the table.

Now, in my case I want to have two running totals: one for “Cash” lines and another for “Salary” lines. My tabular report renders a radio button on each record which the user can select “Cash” or “Salary”. So instead of just the one total amount field, I have two: P1_TOTAL_CASH and P1_TOTAL_SALARY. The radio buttons have hidden input items with the value, rendered with id “f05_nnnn” (where nnnn is the row number).

My UpdateTotal function therefore looks like this:

function UpdateTotals () {
  var sal = 0, cash = 0, amt, rownum, linetype;
  $("input[name='f04']").each(function() {
    amt = parseFloat($(this).val().replace(/,/g,''))||0;
    // determine if this is a Cash or Salary line
    rownum = $(this).prop("id").split("_")[1];
    linetype = $("input[id='f05_"+rownum+"']").val();
    if (linetype == "SALARY") {
      sal += amt;
    } else if (linetype == "CASH") {
      cash += amt;

And my Execute when Page Loads has an additional call:


Now, when the user changes the amounts or changes the line type, the totals are updated dynamically.

EDIT: simplified jquery selectors based on Tom’s feedback (see comments) and use the hidden field for the radio buttons instead of querying for “checked”


UPDATE: If the tabular form has an “Add Row” button, the above code won’t work on the newly added rows. In this case, the Execute when Page Load should be this instead:

$(document).on("change", "input[name='f05']", function(){UpdateTotals();});

Show/Hide Multi-row Delete button for a Tabular Report

I have a standard tabular report with checkboxes on each row, and a multi-record delete button called MULTI_ROW_DELETE.

If the user clicks the button before selecting any records (or if there are no records), they get an error message. Instead, I’d rather hide the button and only show it when they have selected one or more records.


To do this:

1. Edit the MULTI_ROW_DELETE button to have a Static ID (e.g. “MULTI_ROW_DELETE”).

2. Add this function to the page’s Function and Global Variable Declaration:

function ShowHideMultiRowDelete () {
  if ($("input[id^='f01_']:checked").length==0) {
  } else {

This looks to see if there are any checkboxes selected, if none are found it hides the delete button, otherwise it shows it.

3. Add this code to the page’s Execute when Page Loads:


This does the initial check on form load (i.e. it initially hides the button, since none of the checkboxes will be selected yet), and adds a listener to the checkboxes so that if any of them are changed, the function is re-run to show or hide the button as needed.

Unfortunately this doesn’t work with the “all rows” checkbox that was generated by the tabular report, so I’ve added a step to hide that checkbox (“check-all-rows”) until I can find a solution for that.

Autoformat ANY amount item, anywhere

If you’re building a “finance-ey” application you probably have plenty of fields that should show and accept monetary amounts – and quite possibly these items may be implemented in a variety of ways – ordinary apex number items, edit fields in tabular reports, or even dynamically generated items using APEX_ITEM.text.

In my case I had all three, scattered throughout the application. Our users routinely deal with multi-million dollar amounts and they had trouble checking the amounts visually, especially when there are a lot of zeros, e.g. “10000010.00” – so they asked for them all to be formatted with commas, e.g. “10,000,010.00”.

Step 1. SQL number format

So in my first release of the apex application I applied the “FM999G999G999G999G990D00” format to all the money amount items, including in reports etc. The users were reasonably happy with this, but thought it wasn’t working in all cases – e.g. they’d type in a new amount, and the item wouldn’t get formatted until after they Saved the record. This is because the format is only applied when the Apex rendering engine is formatting the page for display – it doesn’t apply it dynamically as the items are changed.

Step 2. Dynamic Actions using SQL

So I started adding dynamic actions to all the apex items which would call the database to format the amount every time the item was changed. This was ok, but performance wasn’t that great – there was a visible sub-second delay while the page did an ajax call to the database just to do the formatting.

Step 3. Dynamic Actions using Javascript

So then I found a Javascript money formatter and modified my dynamic actions to call that instead. The only downside is that it is not internationally-aware. In my case this application’s target users are all here in Australia, are in the education industry, and they haven’t complained about the lack of international money-formatting support (yet).

Number.prototype.formatMoney = function(decPlaces, thouSep, decSep) {
  var n         = this
     ,decPlaces = isNaN(decPlaces = Math.abs(decPlaces)) ? 2 : decPlaces
     ,decSep    = decSep == undefined ? "." : decSep
     ,thouSep   = thouSep == undefined ? "," : thouSep
     ,sign      = n < 0 ? "-" : ""
     ,i         = parseInt(n = Math.abs(+n || 0).toFixed(decPlaces)) + ""
     ,j         = (j = i.length) > 3 ? j % 3 : 0;
  return sign
    + (j ? i.substr(0, j) + thouSep : "")
    + i.substr(j).replace(/(\d{3})(?=\d)/g, "$1" + thouSep)
    + (decPlaces ? decSep + Math.abs(n - i).toFixed(decPlaces).slice(2) : "");

That worked really well, there was no visible delay, and the users were pleased. But I wasn’t satisfied – this trick doesn’t work on the tabular reports or on my APEX_ITEM-generated items.

Step 4. jQuery to the rescue!

So I’ve gone back to the drawing table and decided that I don’t want to have to add Dynamic Actions to each and every item that needs it, which doesn’t work for the items that are generated dynamically (e.g. when the user adds a record to a tabular report). This formatting should be applied automatically to each item, and the only thing I’m going to add to each item is a CSS class. I needed to use some jquery to dynamically bind some javascript to every item that has a particular class, even if the item is added after the page has loaded.

This stackoverflow question came in useful. I added the following to my global javascript file:

$(document).ready(function() {
  $( document ).on('change', '.edit_money', function(){
    var i = "#"+$(this).attr("id")
       ,v = parseFloat($(i).val().replace(/,/g,''))||0;
    $(i).val( v.formatMoney() );

All I have to do is add the “edit_money” class to all my money items. For ordinary Apex items, you put the class in the HTML Form Element CSS Classes attribute. For items in a tabular report, the same attribute is under Column Attributes, called Element CSS Classes.

For items generated using APEX_ITEM, I just had to add some extra parameters (p_attributes and p_item_id), e.g.

  (p_idx        => 2
  ,p_size       => 16
  ,p_maxlength  => 22
  ,p_attributes => 'class="edit_money" style="text-align:right"'
  ,p_item_id    => 'f02_'||TO_CHAR(ROWNUM,'fm0000')

So, that was a reasonably good couple of hour’s work, I think. I’m not the world’s expect on javascript or jquery by any stretch of the imagination, but I’m quite happy with the result so far. I’m sure there are even better ways of doing this, so if you know of a better way please comment.