Sorting Currency

Medelm

Hi I have the following table:

[code]





Date
Total
Amount




[/code]

Column 4 is a date (dd/mm/yyyy) column 5/6 is a currency. I want to sort on these columns. I have the following code so far:

[code]
$(document).ready(function() {

$('#table').dataTable({

"sDom": 'lfrtip',
"bProcessing" : true,
"sPaginationType" : "full_numbers",
"iDisplayLength" : 10,
"aLengthMenu" : [ [ 10, 25, 100, -1 ],
[ 10, 25, 100, "All" ] ],
"aaSorting" : [ [ 4, "asc" ] ],
"bAutoWidth" : false,
"aoColumnDefs": [
{ "sType": 'currency', "aTargets": [4] },
{ "sType": 'date-eu', "aTargets": [3] }
]
});

// Change this list to the valid characters you want
var validChars = "$£€c" + "0123456789" + ".-,'";

// Init the regex just once for speed - it is "closure locked"
var
str = jQuery.fn.dataTableExt.oApi._fnEscapeRegex( validChars ),
re = new RegExp('[^'+str+']');


jQuery.fn.dataTableExt.aTypes.unshift(
function ( data )
{
if ( typeof data !== 'string' || re.test(data) ) {
return null;
}

return 'currency';
}
);

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

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

"currency-desc": function ( a, b ) {
return b - a;
}
} );

jQuery.extend( jQuery.fn.dataTableExt.oSort, {
"date-eu-pre": function ( date ) {
var date = date.replace(" ", "");
if(date.length > 0){
if (date.indexOf('.') > 0) {
/*date a, format dd.mn.(yyyy) ; (year is optional)*/
var eu_date = date.split('.');
} else {
/*date a, format dd/mn/(yyyy) ; (year is optional)*/
var eu_date = date.split('/');
}
/*year (optional)*/
if (eu_date[2]) {
var year = eu_date[2];
} else {
var year = 0;
}
/*month*/
var month = eu_date[1];
if (month.length == 1) {
month = 0+month;
}
/*day*/
var day = eu_date[0];
if (day.length == 1) {
day = 0+day;
}
}
return (year + month + day) * 1;
},
"date-eu-asc": function ( a, b ) {
return ((a < b) ? -1 : ((a > b) ? 1 : 0));
},
"date-eu-desc": function ( a, b ) {
return ((a < b) ? 1 : ((a > b) ? -1 : 0));
}
});

});


[/code]

The error I get in the chrome dev console is:

Uncaught TypeError: Property 'currency-asc' of object # is not a function

I have included the following:

[code]


[/code]

Any ideas?

allan

You are adding the sorting functions after DataTables is trying to use them :-). Just switch the code sequence around, so the functions are added before you initialise your table, and that should do it.

Allan

Medelm

Thanks very much Allan. I am now getting the following error:

Uncaught TypeError: Object # has no method '_fnEscapeRegex'

Which I am assuming relates to this part of the code.

[code]

var
str = jQuery.fn.dataTableExt.oApi._fnEscapeRegex( validChars ),
re = new RegExp('[^'+str+']');

[/code]

I am assuming its a function that should be included ?

Medelm

I can that function is definitely in the datatables js file that is added it just cant see it?

I have even moved the function to above the code and it gets through that section:

[code]
function _fnEscapeRegex ( sVal )
{
var acEscape = [ '/', '.', '*', '+', '?', '|', '(', ')', '[', ']', '{', '}', '\\', '$', '^', '-' ];
var reReplace = new RegExp( '(\\' + acEscape.join('|\\') + ')', 'g' );
return sVal.replace(reReplace, '\\$1');
}

// Change this list to the valid characters you want
var validChars = "$" + "0123456789" + ".-,";

// Init the regex just once for speed - it is "closure locked"
var str = _fnEscapeRegex( validChars ),
re = new RegExp('[^'+str+']');

[/code]

This now gives me another error:

Invalid regular expression: /[^$0123456789.-,]/: Range out of order in character class

I am really have a bad run with this! Any help will be appreciated. Thanks!

allan

> I am assuming its a function that should be included ?

Yes - if you go to http://datatables.net/ and open a console in your browser and type:

[code]
jQuery.fn.dataTableExt.oApi._fnEscapeRegex
[/code]

you will see your browser tells you it is a function.

> Invalid regular expression: /[^$0123456789.-,]/: Range out of order in character class

You haven't escaped the regular expression special characters. For example '.' and '-' are special characters along wit others (^ $ etc).

Allan

Medelm

Allan, thanks very much for your support.

In the end I couldn't get it to recognise that function so I just removed it and escaped the characters within the validchars string manually.

One of those things that is just not working for me! Thanks again.