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
Show/Hide Multi-row Delete button for a Tabular Report

Comments

  1. Excellent, Jeff – I shall be borrowing that for sure. A minor suggestion: now that you have a class “edit_money” on each item you could remove the style=”text-align-right” and put it in CSS shared by the whole app.

  2. Good stuff Jeff – can’t wait to see the finished app

  3. Thank you for sharing this. I’m confused about a couple of things.
    Where does this go? And is it necessary?
    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’)
    )

    I don’t know think that I have a global.js but I put the $(document)….. snippet in the “Function and Global Variable Declaration” area of the page. I’m not getting any results.

    We’re on 5.1 now and it looks slightly different than your screenshots. Does the “edit_money” go in the Appearance->CSS Classes or Advanced->CSS Classes?

    I have a regular page item Text field inside a region, not in a grid.

    • Hi clinville2003,

      The APEX_ITEM.text construction is only necessary if you’re generating the item using PL/SQL, e.g. in a report. In that case, there is no “CSS Classes” attribute that you can assign; instead, you pass the class in the p_attributes parameter to the function.

      If you want to put the javascript in the page definition instead of a separate .js file, the “$(document).ready()” bit is unnecessary – instead, you could put the following code in the Execute when Page Loads attribute:


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

      For page items in Apex 5.0 and later, you put the class in Advanced -> CSS Classes.

      I hope this helps.

  4. Thank for this trick.
    To me works perfectly when you write the number. But if you calculate the number, the trick doesn’t work.
    Any way to work if the item is “display only”, for example?

    Regards,

    • Typically I would write the code that sets the item so that it formats the number how I want it; but in cases where an item may or may not be editable, and may or may not be set by my code, I would make it an editable text item and then set it to readonly (using the html attribute, not an APEX condition). That way the autoformat javascript can still act on changes to it.

  5. Thanks for sharing this.
    Another option for this is accounting.js library.
    https://github.com/openexchangerates/accounting.js

    Best Regards

Leave a Reply

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