Currency Sorting Help

PierceMcGeough

I am trying to get the a currency sorting working. The following gets sorted as is

4,100.00
450.00
7,500.00

instead of

450.00
4,100.00
7,500.00

I have tried looking at the following but got no joy. http://www.datatables.net/forums/discussion/2436/currency-sorting-example

My table breaks and does not sort by any columns when headers are clicked

This is my code

$(document).ready(function() {
    if (jQuery().dataTable) {
        var dataTableDefaults = {
            "fnDrawCallback" : function () {
            },
            "aLengthMenu": [
            [10, 15, 25, 50, 100, -1],
            [10, 15, 25, 50, 100, "All"]
            ],
            "iDisplayLength": 25,
            "oLanguage": {
                "sLengthMenu": "_MENU_ Records per page",
                "sInfo": "_START_ - _END_ of _TOTAL_",
                "sInfoEmpty": "0 - 0 of 0",
                "oPaginate": {
                    "sPrevious": "Prev",
                    "sNext": "Next"
                }
            },
            "aoColumnDefs": [{
                'bSortable': false,
                'aTargets': [-1]
            }],
        }

        $("#currency_sort").dataTable($.extend(true, {}, dataTableDefaults ));

    }
});


jQuery.fn.dataTableExt.aTypes.push(
    function ( sData ) {
        var sValidChars = "0123456789.-,";
        var Char;

        /* Check the numeric part */
        for ( i=1 ; i<sData.length ; i++ ) {
            Char = sData.charAt(i);
            if (sValidChars.indexOf(Char) == -1) {
                return null;
            }
        }

        /* Check prefixed by currency */
        if ( sData.charAt(0) == '$' || sData.charAt(0) == '£' ) {
            return 'currency';
        }
        return null;
    }
);

jQuery.fn.dataTableExt.oSort['currency-asc'] = function(a,b) {
    /* Remove any commas (assumes that if present all strings will have a fixed number of d.p) */
    var x = a == "-" ? 0 : a.replace( /,/g, "" );
    var y = b == "-" ? 0 : b.replace( /,/g, "" );

    /* Remove the currency sign */
    x = x.substring( 1 );
    y = y.substring( 1 );

    /* Parse and return */
    x = parseFloat( x );
    y = parseFloat( y );
    return x - y;
};

jQuery.fn.dataTableExt.oSort['currency-desc'] = function(a,b) {
    /* Remove any commas (assumes that if present all strings will have a fixed number of d.p) */
    var x = a == "-" ? 0 : a.replace( /,/g, "" );
    var y = b == "-" ? 0 : b.replace( /,/g, "" );

    /* Remove the currency sign */
    x = x.substring( 1 );
    y = y.substring( 1 );

    /* Parse and return */
    x = parseFloat( x );
    y = parseFloat( y );
    return y - x;
};

allan

Can you please link to a test case, as required in the forum rules.

DataTables 1.10 should automatically detect currency column types and sort them numerically. If that isn't happening then either there is string data in the column or something is going wrong. Either way we would need a test case.

Allan