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.

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.

pleasewait

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

1. Add an 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>
</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);
    $("div#runningindicator").show();
    apex.submit(request);
  }
}

4. Change the button:

Action = Redirect to URL
URL Target =

javascript:run_long_request('APPROVE',
  '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)) {
    apex.submit({request:request,showWait:true});
  }
}

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;
  });
  $s("P1_TOTAL_AMOUNT",t.formatMoney());
}

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:

$("input[name='f04']").change(function(){UpdateTotal();});

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;
    }
  });
  $s("P52_TOTAL_SALARY",sal.formatMoney());
  $s("P52_TOTAL_CASH",cash.formatMoney());
}

And my Execute when Page Loads has an additional call:

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

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”

tabularreportdynamictotal

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.

showhidemultirowdelete

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) {
    $x_Hide("MULTI_ROW_DELETE");
  } else {
    $x_Show("MULTI_ROW_DELETE");
  }
}

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:

ShowHideMultiRowDelete();
$("input[id^='f01_']").change(function(){ShowHideMultiRowDelete();});
$x_Hide("check-all-rows");

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.

SELECT APEX_ITEM.text
  (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.


APEX: Save a user’s checkbox selection on local PC

You want a checkbox item on a page which is a preference, you want it to be remembered for the user across login sessions, but you don’t want the overhead of storing it in a database table. You might choose to store the value in a cookie instead. It may be lost (e.g. when the user clears their cookies or changes to a different browser or another computer), but we don’t mind – it’s just a preference.

stayonpage

1. Create checkbox item, e.g. P1_STAY_ON_PAGE

Display As = Checkbox
Template = No Label
List of values definition = STATIC2:Stay on page;Y

2. Add dynamic action to the checkbox item to save it when it’s changed

Event = Change
Selection Type = Item(s)
Item(s) = P1_STAY_ON_PAGE
Condition = (none)
True Action = Execute JavaScript Code
Fire On Page Load = (No)
Code = SetCookie("APEX_P1_STAY_ON_PAGE",$v("P1_STAY_ON_PAGE"));

3. Add dynamic action on page load to load it

Event = Page Load
True Action = Execute JavaScript Code
Code = $s("P1_STAY_ON_PAGE",GetCookie("APEX_P1_STAY_ON_PAGE"));

Note that the cookie name (“APEX_P1_STAY_ON_PAGE” in this example) is up to you to choose. Probably best to try making it specific to your application so it doesn’t clash with anything else.


Don’t (always) call v()

Instead of calling a function, when you can get the same effect by accessing a documented PL/SQL variable, you should. For example:

v('APP_USER')    = APEX_APPLICATION.g_user
v('REQUEST')     = APEX_APPLICATION.g_request
v('APP_ID')      = APEX_APPLICATION.g_flow_id
v('APP_PAGE_ID') = APEX_APPLICATION.g_flow_step_id
v('DEBUG')       = APEX_APPLICATION.g_debug

(Note – g_debug is a boolean, unlike the v() equivalent)

UPDATE: If you’re using APEX 5, you can now get the User and Session ID from the APEX$SESSION application context.

There’s more here: documentation for the APEX_APPLICATION package

I suspect that the implementation of v() is something like this [EDIT: read the comments for more commentary on this, and a more accurate picture of what v() actually does]:

FUNCTION v (p_name IN VARCHAR2) RETURN VARCHAR2 IS
  res VARCHAR2(4000);
BEGIN
  CASE p_name
  WHEN 'APP_ID' THEN
    res := APEX_APPLICATION.g_flow_id;
  WHEN 'APP_USER' THEN
    res := APEX_APPLICATION.g_user;
  WHEN 'DEBUG' THEN
    IF APEX_APPLICATION.g_debug THEN
      res := 'YES';
    ELSE
      res := 'NO';
    END IF;
  WHEN 'REQUEST' THEN
    res := APEX_APPLICATION.g_request;
  ... etc. ...
  ELSE
    BEGIN
      SELECT s.item_value
      INTO res
      FROM wwv_<session-values-or-something> s
      WHERE s.item_name = p_name
      AND s.flow_id = APEX_APPLICATION.g_flow_id
      AND s.session_id = APEX_APPLICATION.g_instance;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        RETURN NULL;
    END;
  END CASE;
  RETURN res;
END v;

In addition, instead of calling v('APP_SESSION') / v('SESSION'), you could call the undocumented function APEX_APPLICATION.get_session_id instead, which is probably faster, or refer to the global variable APEX_APPLICATION.g_instance instead. I would suspect that the function normally just returns g_instance anyway, but it’s possible there’s some more logic behind the function.

Disclaimer: use undocumented bits at your own risk.

Some other undocumented goodies that may be useful include (and a lot of these are not available at all via v()):

APEX_APPLICATION.g_flow_alias = application alias
APEX_APPLICATION.g_flow_name = application name
APEX_APPLICATION.g_flow_version = application version string
APEX_APPLICATION.g_flow_status = app availability status code, e.g. AVAILABLE_W_EDIT_LINK
APEX_APPLICATION.g_build_status = app build status code, e.g. RUN_AND_BUILD
APEX_APPLICATION.g_base_href = the base URL for the site, not including the f?p=... bit
APEX_APPLICATION.g_printer_friendly = TRUE if the page was requested with Printer Friendly flag
APEX_APPLICATION.g_excel_format = TRUE if the page’s report is being rendered in CSV format
APEX_APPLICATION.g_date_format = Application default date format
APEX_APPLICATION.g_date_time_format = Application date time format
APEX_APPLICATION.g_timestamp_format = Application default timestamp format
APEX_APPLICATION.g_timestamp_tz_format = Application default timestamp with time zone format

You can have a peek at all the globals in this package with this query (but be warned, any undocumented ones may change, and may not necessarily be set to any meaningful value when your code is running):

select owner, trim(text)
from dba_source
where name = 'WWV_FLOW'
and type = 'PACKAGE'
and ltrim(text) like 'g%'
order by owner desc, line;

Proposed wrapper for APEX_UTIL.set_session_state

NOTE: update for APEX 5 (see below)

I decided to try using a wrapper procedure to isolate calls to APEX_UTIL.set_session_state in an autonomous transaction. I’m currently using it in a project and seeing how it goes in terms of performance.

DISCLAIMER: Don’t just throw this into your mission-critical system without at least testing it thoroughly first.

Since I had Morten Braten’s Alexandria library handy, I simply modified his APEX_UTIL_PKG. If you’re not using this library you can create your own wrapper quite simply:

create or replace procedure sv
  (p_name  in varchar2
  ,p_value in varchar2 := NULL) as
PRAGMA AUTONOMOUS_TRANSACTION;
begin
  APEX_UTIL.set_session_state
    (p_name => p_name
    ,p_value => p_value);
  COMMIT;
end sv;

Since my system has many schemas (one for each application), I would compile this in a “common” schema and then grant execute on it to the schemas that need it, and create local synonyms in each one so that my applications just need to call sv.

ADDENDUM:

As Joel Kallman rightly points out, putting set_session_state in an autonomous transaction means that the new value will not be visible to the rest of the calling code, so for example the call to v() will not return ‘Joe’ here:

sv('P1_NAME', 'Joe');
x := v('P1_NAME'); -- will not be 'Joe'

Therefore, it is intended that sv() be used as the final step in any procedure, e.g.:

PROCEDURE p1_controller IS
  p1_name VARCHAR2(100);
BEGIN
  p1_name := v('P1_NAME');
  
  sv('P1_NAME', p1_name);
END;

UPDATE for APEX 5

As of Oracle APEX 5.0, APEX_UTIL.set_session_state supports a new optional parameter, p_commit (documentation). It is defaulted to true which preserves the old behaviour (i.e. it might or might not commit).

If you set p_commit to false, the procedure will not issue any commit. This removes the need for the autonomous transaction, and leaves the responsibility for committing to the developer; if it’s called from an APEX page process, it will be committed automatically.


Change an item Label dynamically

Get it? “an item with many hats”… yeah ok.

Need to change the label of an item on-the-fly? When I run my Apex page it renders item labels like this:

<label for="P1_CONTACT_NUMBER">
  <span>Contact Number</span>
</label>

If the label needs to change based on another item, I could set the label with the value of another item, e.g. “&P1_CONTACT_NUMBER_LABEL.” and when the page is refreshed it would pick up the new label. But at runtime, if the label needs to change dynamically in response to changes in other items, we need to do something else.

Caveat: The need for changing the label should be very rare – it’s bad practice to overload one field with multiple meanings. But if you must, this is what you can do.

It’s easy with a Dynamic Action running some Javascript. This changes the label text for the P1_CONTACT_NUMBER item depending on the value chosen for P1_CONTACT_METHOD, which might be a radio group or select list. The method uses jquery to search for a “label” tag with the attribute “for” that associates it with the desired item; we then navigate down to the “span” element, and call the “text” function to change the label text:

if ($v("P1_CONTACT_METHOD")=='SMS') {
    $("label[for=P1_CONTACT_NUMBER]>span").text("Contact Mobile")
} else if ($v("P1_CONTACT_METHOD")=='EMAIL') {
    $("label[for=P1_CONTACT_NUMBER]>span").text("Contact Email")
} else {
    $("label[for=P1_CONTACT_NUMBER]>span").text("Contact Number")
}

The Dynamic Action is set up as follows:

Event = Change
Selection Type = Item(s)
Item(s) = P1_CONTACT_METHOD
Condition = (no condition)

True Action = Execute JavaScript Code
Fire On Page Load = Yes
Selection Type = (blank)
Code = (the javascript shown above)


Parallel Development in APEX

Source: http://paulhammant.com/files/multi-branch.jpgMy current client has a large number of APEX applications, one of which is a doozy. It is a mission-critical and complex application in APEX 4.0.2 used throughout the business, with an impressively long list of features, with an equally impressively long list of enhancement requests in the queue.

They always have a number of projects on the go with it, and they wanted us to develop two major revisions to it in parallel. In other words, we’d have v1.0 (so to speak) in Production, which still needed support and urgent defect fixing, v1.1 in Dev1 for project A, and v1.2 in Dev2 for project B. Oh, and we don’t know if Project A will go live before Project B, or vice versa. We have source control, so we should be able to branch the application and have separate teams working on each branch, right?

We said, “no way”. Trying to merge changes from a branch of an APEX app into an existing APEX app is not going to work, practically speaking. The merged script would most likely fail to run at all, or if it somehow magically runs, it’d probably break something.

So we pushed back a bit, and the terms of the project were changed so that development of project A would be done first, and the development of project B would follow straight after. So at least now we know that v1.2 can be built on top of v1.1 with no merge required. However, we still had the problem that production defect fixes would still need to be done on a separate version of the application in dev, and that they needed to continue being deployed to sit/uat/prod without carrying any changes from our projects.

The solution we have used is to have two copies of dev, each with its own schema, APEX application and version control folder: I’ll call them APP and APP2. We took an export of APP and created APP2, and instructed the developer who was tasked with production defect fixes to manually duplicate his changes in both APP and APP2. That way the defect fixes were “merged” in a manual fashion as we went along – also, it meant that the project development would gain the benefit of the defect fixes straight away. The downside was that everything worked and acted as if they were two completely different and separate applications, which made things tricky for integration.

Next, for developing project A and project B, we needed to be able to make changes for both projects in parallel, but we needed to be able to deploy just Project A to SIT/UAT/PROD without carrying the changes from project B with it. The solution was to use APEX’s Build Option feature (which has been around for donkey’s years but I never had a use for it until now), in combination with Conditional Compilation on the database schema.

I created a build option called (e.g.) “Project B”. I set Status = “Include”, and Default on Export = “Exclude”. What this means is that in dev, my Project B changes will be enabled, but when the app is exported for deployment to SIT etc the build option’s status will be set to “Exclude”. In fact, my changes will be included in the export script, but they just won’t be rendered in the target environments.

When we created a new page, region, item, process, condition, or dynamic action for project B, we would mark it with our build option “Project B”. If an existing element was to be removed or replaced by Project B, we would mark it as “{NOT} Project B”.

Any code on the database side that was only for project B would be switched on with conditional compilation, e.g.:

$IF $$projectB $THEN
  PROCEDURE my_proc (new_param IN ...) IS...
$ELSE
  PROCEDURE my_proc IS...
$END

When the code is compiled, if the projectB flag has been set (e.g. with ALTER SESSION SET PLSQL_CCFLAGS='projectB:TRUE';), the new code will be compiled.

Build Options can be applied to:

  • Pages & Regions
  • Items & Buttons
  • Branches, Computations & Processes
  • Lists & List Entries
  • LOV Entries
  • Navigation Bar & Breadcrumb Entries
  • Shortcuts
  • Tabs & Parent Tabs

This works quite well for 90% of the changes required. Unfortunately it doesn’t handle the following scenarios:

1. Changed attributes for existing APEX components – e.g. some layout changes that would re-order the items in a form cannot be isolated to a build option.

2. Templates and Authorization Schemes cannot be marked with a build option.

On the database side, it is possible to detect at runtime if a build option has been enabled or not. In our case, a lot of our code was dependent on schema structural changes (e.g. new table columns) which would not compile in the target environments anyway – so conditional compilation was a better solution.

Apart from these caveats, the use of Build Options and Conditional Compilation have made the parallel development of these two projects feasible. Not perfect, mind you – but feasible. The best part? There’s a feature in APEX that allows you to view a list of all the components that have been marked with a Build Option – this is accessible from Shared Components -> Build Options -> Utilization (tab).

Enhancement Requests:

1. If Build Options could be improved to allow the scenarios listed above, I’d be glad. In a perfect world, I should be able to go into APEX, select “Project B”, and all my changes (adding/modifying/removing items, regions, pages, LOVs, auth schemes, etc) would be marked for Project B. I could switch to “Project A”, and my changes for Project B would be hidden. I think this would require the APEX engine to be able to have multiple definitions of each item, region or page, one for each build option. Merging changes between build options would need to be made possible, somehow – I don’t hold any illusions that this would be a simple feature for the APEX team to deliver.

2. Make the items/regions/pages listed in the Utilization tab clickable, so I can easily click through and change properties on them.

3. Another thing I’d like to see from the APEX team is builtin GUI support for exporting applications as a collection of individual scripts, each independently runnable – one for each page and shared component. I’m aware there is a Java tool for this purpose, but the individual scripts it generates cannot be run on their own. For example, if I export a page, I should be able to import that page into another copy of the same application (but with a different application ID) to replace the existing version of that page. I should be able to check in a change to an authorization scheme or an LOV or a template, and deploy just the script for that component to other applications, even in other workspaces. The export feature for all this should be available and supported using a PL/SQL API so that we can automate the whole thing and integrate it with our version control and deployment software.

4. What would be really cool, would be if the export scripts from APEX were structured in such a way that existing source code merge tools could merge different versions of the same APEX script and result in a usable APEX script. This already works quite well for our schema scripts (table scripts, views, packages, etc), so why not?

Further Reading: