0

In Visual FoxPro, a number can be formatted in a textbox or grid and still be seen by the program as just a number, even though it is shown with commas and period format.

Presently I'm inserting data into a DataTable Jquery as follows:

oTable.fnAddData( ["Bogus data","1,541,512.52","12.5%","0","0","0"]);

But I would like to be entering the data as follows and yet show it with the commas for clarity:

oTable.fnAddData( ["Bogus data",1541512.52,"12.5%","0","0","0"]);

The reason is that when you sort the rows on this column, the character string in the first example will produce a mess. the numbers, hopefully will produce a well ordered list.

If you have any other suggestions on how to fix the sorting of the character number column please suggest it...

TIA

Dennis

DKean
  • 2,017
  • 6
  • 19
  • 28

2 Answers2

1

A sort plugin for formatted numbers is detailed here, by the author of DataTables:

http://www.datatables.net/plug-ins/sorting

See "formatted numbers":

This plug-in will provide numeric sorting for numeric columns which have extra formatting, such as thousands seperators, currency symobols or any other non-numeric data.

jQuery.extend( jQuery.fn.dataTableExt.oSort, {
    "formatted-num-pre": function ( a ) {
        a = (a==="-") ? 0 : a.replace( /[^\d\-\.]/g, "" );
        return parseFloat( a );
    },

    "formatted-num-asc": function ( a, b ) {
        return a - b;
    },

    "formatted-num-desc": function ( a, b ) {
        return b - a;
    }
} );
Marc
  • 11,403
  • 2
  • 35
  • 45
  • Thank you for addressing my question in full Marc. I am in the middle of trying it out and reading the site you replied. I assume that numbers cannot be formatted then and will go on with this alternative instead. I do wish that there was a mask and format, but this will do. So, thank you for your kindness and help. This looks promising. – DKean Mar 06 '13 at 05:07
1

Here's a quick and dirty way to do it;

var number = 1541512.52;
var nicelyformattedNumber = number.toString().replace(/\B(?=(\d{3})+(?!\d))/g, ",");
oTable.fnAddData( ["Bogus data", nicelyformattedNumber, "12.5%","0","0","0"]);

Source

Community
  • 1
  • 1
icanc
  • 3,499
  • 3
  • 35
  • 45
  • Thank you for the quick answer, but this will produce a character value for the middle column in the table. The funny thing is that these numbers come to me fully formatted in character format. I would like to use numbers, as I said above. Nevertheless, thank you for the kindness of helping me. – DKean Mar 06 '13 at 05:04