Skip to content

Custom html for an Apex generated item? jQuery to the rescue

The apex application I’m working on has a search filter on a report page that looks like this:

transaction-search-checkboxes

The list of values is based on a user-defined “ref codes” table, which includes an option “Show By Default”. This option is currently set on the “Closed” and “Deleted” status and means that transactions with that status will not normally be listed in the report, unless the user explicitly selects either of those statuses, e.g.:

transaction-status-closed

If no checkboxes are selected, the report shows all transactions by default, except for Closed or Deleted transactions.

To indicate this behaviour, I added an asterisk (*) next to the label on those checkboxes. I also wanted some hover text so that a user who has forgotten what the asterisk means can get an idea, e.g.:

<td>
<input type="checkbox" id="P23_FTS_STATUS_7" name="p_v05" value="CLOSED">
<label for="P23_FTS_STATUS_7" title="* not shown by default">Closed*</label>
</td>

transaction-status-hover

However, the default apex Checkbox item doesn’t support putting extra attributes on the generated html labels – so I need to add the hover text by running some javascript after the page is loaded. jQuery to the rescue!

To add the hover text I simply add this to the Execute when Page Loads page attribute:

$("label[for*='P23_FTS_STATUS']:contains('*')")
  .attr("title","* not shown by default")

This searches for all label nodes where the “for” attribute contains my item name (“P23_FTS_STATUS”), where the text contains a “*”. It then adds the “title” attribute with my desired value.

Calculate Age in Javascript

I had a registration form in Apex which asks the applicant to enter their Date of Birth in a date item; I then needed to calculate how old they would be at the start of the event, which determines a number of rules, such as whether we need to obtain their parent’s permission.

In my first release I implemented this with a Dynamic Action which ran SQL something like this:

select round(months_between(start_date
                           ,to_date(:P1_DATE_OF_BIRTH,'DD-MON-YYYY'))
             / 12,1)
from events
where event_id = :P1_EVENT_ID;

This worked fine – it takes advantage of Oracle’s builtin support for date arithmetic. However, it was rather slow, because it needs to do a roundtrip to the database to run the query and return the result.

I wanted a pure javascript implementation to avoid the roundtrip, but my initial searches came up with a number of sub-par solutions involving extracting the year and month portions and applying simple arithmetic which did not take into account leap years.

Instead, I’ve gone with an easier solution taking advantage of the moments javascript package.

  1. Add the path to moment.min.js in the File URLs attribute of the page. You could get your own local copy or point to the relevant file from a cdn: http://cdnjs.com/libraries/moment.js/
  2. Add a function to the Function and Global Variable Declaration attribute of the page, which uses the moments object to convert the strings into date objects, and then call the diff method to get the number of years as a floating-point number, e.g.:
    function getAge() {
      var e = moment($v("P1_EVENT_DATE"),"YYYYMMDD")
         ,dob = moment($v("P1_DATE_OF_BIRTH"),"DD-MMM-YYYY");
      return e.diff(dob,'years',true).toFixed(1);
    }
  3. Add a Dynamic Action to the Date of Birth item which calls getAge() and sets the value of the Age display item.

The result is a much quicker response and less load on the database. This is an intentionally simple example, you could do it in different ways to suit your situation (e.g. if you have multiple date items you need to handle on the same page, you might pass them as parameters to the function).

The moments javascript package has an impressive list of features, including pretty-formatting a duration (e.g. a client-side version of the SINCE format e.g. “3 years ago”) documented here.

Why Kyte Tolerates Poor Questions

I have Tom Kyte’s “Ask Tom” in my feed reader, and every now and then it opens for questions and the flood pours in. Quite often there is an interesting question, but more often than not there are quite a lot of “poor” questions: some just poorly worded, some too-easily-googleable, others are could-have-answered-from-the-docs questions. It’s not dissimilar to StackOverflow, which doesn’t suffer so much because of the army of people who work together to either improve or close these types of questions. Sometimes I think “Why did you waste Tom’s time with that? You could have googled it, searched the docs, or opened SO and you’d probably find a good answer there without even having to ask it.”

However, my impression is wrong, I think, because my feed reader only shows me the initial question, and not the detailed follow-up by Tom and others. I was reminded today of how a poor question can still lead to enlightenment, by this excellent quote by Tom:

As things grow and change over time – engineers need to use different approaches. The planes I fly in have wings that go straight out from the plane. That is because I fly at about 500 mph. A fighter jet at mach 2 would be going 1,500 mph. The wings on a fighter jet are significantly different in their architecture – because if they weren’t it wouldn’t work. We have different types of bridges in order to cross different types of chasms. We have different building styles for different types of builds (stick and brick for a 2 story house, steel girders in a frame for 30 story buildings).

As you scale up, the solutions that worked for trivial amounts of data will not necessarily work for large amounts of data.

If you are in a place that says “make it work but do not change code” – expect to have a bad day.

(Source)

And, if you’re wondering how he keeps up with all this:

After 14 years on asktom and 20 years total (usenet news groups last century) answering tens of thousands of questions, I took a break from social media stuff.

(Source)

We could all do with a break from time to time, I think :)

Sample TAPI Apex Application

If you attended my presentation at AUSOUG Perth earlier this month, or if you’ve had a peek at the slides, you may be interested in a more concrete demonstration of the ideas presented. So if you’d like to install and play with a sample application that includes a TAPI generator, feel free to download this (EDIT: updated, see below).

Disclaimer: this is provided for information (and entertainment) purposes only.

Prerequisites:
Oracle Application Express 4.2.2 or later

Read the README file for installation instructions.

appimage

If you’re only interested in the schema-level TAPI and not the Apex application, the zip file includes the DDL script that you can run directly in a schema without requiring Apex.

EDIT (18/11/2014): updated sample code to do the right thing in WHEN OTHERS triggers.

EDIT (20/11/2014): updated sample code with a further example for a FK to a table, which doesn’t use a surrogate key. Also added an exception handler to the Apex application.

EDIT (2/12/2014): added Grid Edit for event types, as an example of how a tabular form might work with a TAPI; added deployment package; moved code to Bitbucket.

More updates and improvements will be added in the future – watch this space.

Help for your keyboard users

Apex’s Blue Responsive Theme 25 is a great theme for building a user-friendly website, and unlike many other themes which make the item labels clickable to get help, it renders little question-mark icons to the right of each item that has a help message defined.

theme-25-help-icon

One issue with this design, however, is that a keyboard-savvy user (such as myself) hates to switch between keyboard and mouse – so they Tab between each field as they fill in a form. With this theme, however, those little question-mark icons are in the tab order, so the user has to Tab twice between each field. If they’re not looking at the page they might forget; and if one item doesn’t happen to have a Help icon, they have to remember to only Tab once. All of this adds up to a poor user experience.

To fix this, we simply tell the browser to move the Help icons out of the tab order – and yet again, jQuery comes to the rescue as we can simply pick out all the elements with the class itemHelpButton and set their tabindex to “-1″:

$(".itemHelpButton").attr('tabindex',-1);

Put this code in the page attribute Execute when Page Loads – when the page loads, this will set the tabindex on all the help icons on the page, so now the user can tab through the page without interruption.

Follow

Get every new post delivered to your Inbox.

Join 271 other followers