CODEWITHSUNDEEP
javascriptjqueryhtmldatatables
DKean
DKean

Reputation: 2099

How to sort on a numeric column in jQuery DataTables

(Looked at a bunch of answers on this topic, none of which applies to this question.)

The DataTables have the feature of letting the user click on each column's Up/Down triangle icons to sort in Ascending or Descending order. I have loaded the data as follows

        oTable.fnAddData( ["Bogus data","1,541,512","12.5%","0","0","0"]);
        oTable.fnAddData( ["Bogus data","3,541,512","2.5%","0","0","0"]);
        oTable.fnAddData( ["Bogus data","541,512","1.5%","0","0","0"]);
        oTable.fnAddData( ["Bogus data","2,541,512","32.5%","0","0","0"]);
        oTable.fnAddData( ["Bogus data","741,512","3.5%","0","0","0"]);
        oTable.fnAddData( ["A Bogus data","41,512","1.5%","0","0","0"]);
        oTable.fnAddData( ["Bogus data","2,541,512","12.5%","0","0","0"]);
        oTable.fnAddData( ["Z Bogus data","1,541,512","12.5%","0","0","0"]);
        oTable.fnAddData( ["Bogus data","3,541,512","2.5%","0","0","0"]);
        oTable.fnAddData( ["La Bogus data","541,512","1.5%","0","0","0"]);
        oTable.fnAddData( ["The Bogus data","2,541,512","32.5%","0","0","0"]);
        oTable.fnAddData( ["Bogus data","741,512","3.5%","0","0","0"]);
        oTable.fnAddData( ["Bogus data","41,512","1.5%","0","0","0"]);
        oTable.fnAddData( ["Bogus data","2,541,512","12.5%","0","0","0"]);
        oTable.fnAddData( ["Bogus data","1,541,512","12.5%","0","0","0"]);
        oTable.fnAddData( ["Bogus data","3,541,512","2.5%","0","0","0"]);
        oTable.fnAddData( ["Bogus data","541,512","1.5%","0","0","0"]);
        oTable.fnAddData( ["Bogus data","2,541,512","32.5%","0","0","0"]);
        oTable.fnAddData( ["Bogus data","741,512","3.5%","0","0","0"]);
        oTable.fnAddData( ["Bogus data","41,512","1.5%","0","0","0"]);

In column number 2 the numeric values are processed alphabetically when I click on the Up/Down triangles. How can I adjust it so that the second column Up/Down arrows will trigger the proper way, treating the characters as numbers. I tried using the following initialization:

oTable = $('.utable').dataTable( {
"aoColumns": [{ sWidth: '60%' },{sWidth: '30%', "sType": "numeric"},{ sWidth: '10%' }],
"sDom": 'rt',
"sScrollY":"200px",
"bPaginate":false,
"bFilter":false,
"bInfo": false});

All this does is to lock the colum and the Up/Down icons will not work in the header of that column.

Upvotes: 3

Views: 23582

Answers (4)

Georges
Georges

Reputation: 1

Hello I made this using parseFloat and replace methods and using this example http://datatables.net/release-datatables/examples/basic_init/multi_col_sort.html

jQuery.fn.dataTableExt.oSort["string-nbr-asc"]  = function(x,y) {return ((parseFloat(x.replace(",","")) < parseFloat(y.replace(",",""))) ? -1 : ((parseFloat(x.replace(",","")) > parseFloat(y.replace(",",""))) ?  1 : 0));};

jQuery.fn.dataTableExt.oSort["string-nbr-desc"] = function(x,y) {return ((parseFloat(x.replace(",","")) < parseFloat(y.replace(",",""))) ?  1 : ((parseFloat(x.replace(",","")) > parseFloat(y.replace(",",""))) ? -1 : 0));};

If you have 10 columns and want to sort 7,8,9 wich are numbers like 7,081 1,925.49 use

"aoColumns":[null,null,null,null,null,null,{ "sType": "string-nbr" },{ "sType": "string-nbr" },{ "sType": "string-nbr" },null]

Upvotes: 0

Pete
Pete

Reputation: 2558

Your problem is that the numbers are not being recognized as such and even if they were, the commas in your numbers would probably the sorting function off (because it will probably not properly remove them).

One option you have is to implement your own sorting function which deals with your numbers properly. Here's an example that does what you need:

http://live.datatables.net/oborug/2/edit

PS -- Here's the relevant documentation: http://datatables.net/development/sorting

Upvotes: 4

Michal
Michal

Reputation: 13639

Technically column number two contains strings (1,000) - numbers with a comma and so does column 3 - numbers with percentages). Best thing for you to do is to pass the data to data tables as an integer (without commas and %) and write a custom formatter to add commas and percentages using mRender option (read about it at http://www.datatables.net/usage/columns).

If you add custom formatting to your data also do not forget to set the option to use underlying data as your sort source rather than the displayed data.

Upvotes: 2

Oussaki
Oussaki

Reputation: 1649

you have to define a function of sorting ( try to use sort by selection or sort by insertion ) try to stock your data variables in an array and do the caculations , else you can do the sort directly with your data

Upvotes: 0

Related Questions