2 Replies Latest reply on Jan 14, 2018 3:32 PM by Doug

    Updating or inserting records with comma formatted numbers


      Lets say I have a table where the columns are varchar2 and number.  My users want to see number formatting on all numbers and currency formatting on all currency.  So I create a form to show all of the table columns.  If I change the value of one item (varchar2 or number) and attempt to Apply Changes, I will get an error because one or more of the numeric items contains non-numeric value, i.e., commas and $ signs.  In Oracle Forms, this was not a problem because the number formatting was recognized as formatting, but in APEX this distinction is apparently not made. 


      How can this be handled?


      I have created javascript which either formats or unformats numbers when the mouse cursor enters or leaves the region in which the "Apply Changes" button is located.  But this is a cumbersome process.  Please help.

        • 1. Re: Updating or inserting records with comma formatted numbers

          Hello Doug,


          If you're using the ARP (Automatic Row Processing) you can simply set the corresponding Format Mask attribute (under the Appearance section).

          E.g. for a currency, you could set "FML999G999G999G999G990D00". Then you could save either "$100", "100", "$100.00" or "100.00".


          You can test this easily using the Sample Database Application with the Products page.


          Hope this help


          • 2. Re: Updating or inserting records with comma formatted numbers

            Thanks Max.  I've been using the legacy builder which does not format numbers as numbers.  It essentially changes the number to a string, or else you would be able to commit.


            I note that the formatting works only on database items.  If you have an item with the value selected via a sql query, you have to do the formatting with a to_char in the query, e.g., "select to_char(money,'$999,999,999.99') from whereever.  Please let me know if there is a better way to format non-database numeric values.