asked on

How can i sort Jquery Datatable currency Column properly?

I am using Datatables Version 1.10.12 and jquery-1.11.1

My currency column doesn't sort properly,below is the code I am using to sort

$(document).ready( function () {
		$('#notSoCoolGrid').DataTable({
			"lengthMenu": [[10, 25, 50, -1], [10, 25, 50, "All"]],
			columnDefs: [{ type: 'currency', targets: 1 }],
			"aaSorting": [[ 2, 'asc' ]]
		} );
	} );

Open in new window

And I am getting as shown below for my currency column.Please note brackets( ) are considered negative number

($75.00) USD
($108.87) USD
($249.44) USD
($1,000.00) USD
£899.00 GBP
$905.00 AUD
£830.65 GBP
$825.00 USD
£59.67 GBP

Mikkel Sandberg

It looks like the "aaSorting" option is legacy as of DataTables v1.10 [source], so maybe there's a different option you can use. I haven't used the library myself, but hopefully this helps some.

Edit: looking through the documentation, maybe you can try the "order" option instead.

Frank Danny

ASKER

@Mikkel the aaSorting is working fine that is targeted to column[2]. I am having problem with currency column[1]

Mikkel Sandberg

I don't see a 'currency' type for column defs in the documentation. Is that a custom type?
On this page, I see the type 'num-fmt,' which looks like it covers currency. So then it might be:

columnDefs: [{ type: 'num-fmt', targets: 1 }],

Open in new window

Frank Danny

ASKER

@Mikkel , below is how currently the script is sorting currency..somehow I need to add Pund symbol and consider AUD and CAD,not sure the proper regex

$.fn.dataTable.ext.type.order['currency-pre'] = function ( data ) {
   
   [code] var expression = /((\(\$))|(\$\()/g;
    //Check if its in the proper format
    if(data.match(expression)){
        //It matched - strip out parentheses & any characters we dont want and append - at front     
        data = '-' + data.replace(/[\$\(\),]/g,'');
    }else{
      data = data.replace(/[\$\,]/g,'');
    }
    return parseInt( data, 10 );
};

Open in new window

[/code]

SOLUTION

Mikkel Sandberg

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

leakim971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Frank Danny

ASKER

Thank you@leakim971,i have been debugging one strange thing.The Jfiddle works fine under IE v11 but when I use code on my actual table .This is what I am getting

Unable to get property 'pop' of undefined or null reference, I am using IE v 11.
Below is F12 Tool debugger

SOLUTION

leakim971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Frank Danny

ASKER

Wow works like a charm..:). I had another currency CAD.So I added that and everything looks perfect.Thank you very much @Leakim971