Autoformat Numbers in an Interactive Grid

A lot of the applications I build allow users to enter large numbers of monetary amounts, and the way these numbers are presented can have an impact on how easy it is for them to read and check those numbers.

All such amounts are formatted for display using a system-wide standard format (in our case, the Australian standard fm999g999g999g990d00), with any amounts less than $0.01 rounded to the nearest cent. After an amount is entered or modified, the user expects to see the value formatted correctly straight away; so I use javascript to take their entered value, convert it to a number, format it and set its value back in the field. Also, if the user has copied in any non-numeric characters (e.g. a $ symbol), these are simply removed silently.

An interactive grid (this one was a work in progress) with some editable monetary amounts.

In the past I had a global javascript file which I’d load with each application with the following basic functions to auto-format any monetary amount fields as the user tabs out of them, whether they appear in an ordinary form or a tabular form:

Number.prototype.formatMoney = function(decPlaces, thouSep, decSep) {
/* this function taken from http://stackoverflow.com/questions/9318674/javascript-number-currency-formatting */
  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) : "");
};

function parseNumeric(v) {
  //strip any non-numeric characters and return a non-null numeric value
  return parseFloat(v.replace(/[^\d.-]/g,''))||0;
}

$(document).ready(function() {
  //automatically format any item with the "edit_money" class
  $( document ).on('change', '.edit_money', function(){
    var i = "#"+$(this).attr("id"), v = $(i).val();
    if(v){ $(i).val( parseNumeric(v).formatMoney() ); }
  });
});

I would then simply add the class edit_money to any item in the application and it would automatically apply the formatting; for example, if the user types in 12345.6, it changes the value to 12,345.60.

TL;DR
If you want to skip to the punchline, scroll down past the next few paragraphs where I take you down a merry rabbit-hole that, as it turns out, was completely unnecessary (but still somewhat educational).

Now we’re on APEX 19.1 and starting to use Interactive Grids (IG) for a lot of new screens, but applying the edit_money class to the columns in the grid doesn’t work, because the edit item that is generated on-the-fly by the grid doesn’t [edit: I was wrong here] conform to the structure expected by my document-on-change function callback.

Instead, to solve this I decided to add a single dynamic action to the IG that sets the value to a JavaScript Expression which calls my parseNumeric and formatMoney functions.

Aside: when Google failed me
It took me a little bit of searching and experimentation to work out what the JavaScript Expression should be; I didn’t expect it to be complex, and knew it should refer to the item without specifying any particular column name or ID, because this DA could be triggered from multiple columns in the same grid. But I just didn’t know how to refer to the “current item”, and the attribute help was not as helpful as I’d have liked. My google-fu was failing me as well (although I picked up a few neat tricks that I’d like to try in future); I tried searching “oracle apex interactive grid javascript expression” but most of the results were for complicated scenarios that didn’t apply to what I thought was this simple case. I even tried reading the Oracle documentation but just couldn’t find what I was looking for.

I guessed the JavaScript Expression would have access to a this object that should give me access to the item’s value. I used a little trick to copy this into a global variable and used the Chrome debug console to examine this to see how to get the value of the cell being edited. Firstly, in the page Function and Global Variable Declaration I added var x;. Secondly, in the JavaScript Expression on the dynamic action I entered x=this. Running the page, I entered a value into the cell in the grid, opened the Chrome console, then typed “x”. Chrome immediately showed the structure of “x”:

That “triggeringElement” looks like it might be what I’m after, so I continued typing:

After finishing typing “.val()” it gave an error “val is not a function”. I’d seen other code around the place that converts triggeringElement to a jQuery object, so I tried that instead:

This spat out the number I’d entered. So initially I used $(this.triggeringElement).val(). Later I did some more digging and realised I didn’t need jQuery here, I can use the value attribute directly – this.triggeringElement.value. I suspect this is one of those basic things that they teach you on day one of an “Intro to APEX Interactive Grids 101” class but I must have been sick that day 🙂

My final DA has the following attributes:

  • Event = Change
  • Selection Type = Column(s)
  • Region = [the interactive grid region]
  • Column(s) = [list of all the editable monetary columns]
  • Action = Set Value
  • Set Type = JavaScript Expression
  • JavaScript Expression =
    this.triggeringElement.value?parseNumeric(this.triggeringElement.value).formatMoney():""
  • Suppress Change Event = Yes
  • Selection Type = Triggering Element
  • Fire on Initialization = No
Dynamic action attributes
DA True Action attributes

It’s not quite as simple as adding the class to all the items, but at least it’s just one dynamic action that I need to add to each interactive grid.

POSTSCRIPT
As pointed out by John, I went down this rabbit hole for one simple and annoying reason: I forgot that there are not one, but twoCSS Classes” attributes on each item, and I’d put my “edit_money” class in the wrong attribute.

This may have the appearance of being the right one (it’s the first one listed). This is not the CSS Classes you’re looking for.
This is the CSS Classes you’re looking for.
Hiding HTML when downloading an Interactive Report
Showing image thumbnails in Icon View

Comments

  1. A worthwhile journey but it shouldn’t be so difficult.
    First wouldn’t it be great if IG just did this automatically? I think it should. If you give a format it should be used on the client side as well.
    Your edit_money class should work with IG columns because they are just normal items. There are two CSS Classes attributes one under Appearance and one under Advanced. One applies to IG column and one applies to the column item. The PD help doesn’t make it easy to tell which is which. I just guess and check. You should add edit_money the one under advanced. I did this with your global event handler code and it worked.
    The info on setValue triggering element is helpful. I think because it is an item using $v or apex.item().getValue() should be preferred over element.value or $().val() – just in case.

Leave a Reply

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