CODEWITHSUNDEEP
jquerysortingdatatables
Danny
Danny

Reputation: 45

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' ]]
        } );
    } );

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

Upvotes: 1

Views: 1764

Answers (2)

Danny
Danny

Reputation: 45

Below is what finally works out for me,i have to consider the exchange rate as well.

<link href="datatables/1.10.13/css/jquery.dataTable.min.css" type="text/css" rel="stylesheet" />
    <script src="js/jQuery/1.11.1/jquery.js"></script>
    <script src="datatables/1.10.13/js/jquery.dataTables.js"></script>
    <script>
        $(document).ready( function () {
            var currencies = { GBP: 1.2403, AUD: 0.7364, USD: 1 };
            $.fn.dataTable.ext.type.order['currencies-pre'] = function(d) {
                var sign = /\([^)]*\)/.test(d)?-1:1;
                var currency = currencies[d.match(/USD|GBP|AUD/).pop()]; // /!\ all MUST have currency
                var n = d.replace(/[^\d.]/g,"");
                console.log(sign * n * currency);
                return sign * n * currency;
            };
            $('#notSoCoolGrid').DataTable({
                "aoColumns": [{ sWidth: "10%", bSearchable: false, bSortable: false }, { sWidth: "90%", bSearchable: false, bSortable: true }],
                "lengthMenu": [[10, 25, 50, -1], [10, 25, 50, "All"]],
                "columnDefs": [{ type: 'currencies', targets: 1 }],
                "aaSorting": [[ 1, 'asc' ]]
            });
        });
    </script>

Upvotes: 0

Gyrocode.com
Gyrocode.com

Reputation: 58880

Use brackets-negative sorting plug-in. Your code can stay the same, you just need to include extra JS file //cdn.datatables.net/plug-ins/1.10.13/sorting/brackets-negative.js.

Upvotes: 2

Related Questions