HTML and Number Formats

nuttguy

Hey everybody! I am running into an issue where i can't seem to be able to format numbers that have HTML strings along with them. There was a similar thread that talked about HTML strings, links in particular, breaking sorting on DataTables and writing custom sorting in order to handle that. I used that thread so I can sort my data but I also want to be able to format the data. I was going off of another thread talking about how DataTables has the fnFormatNumber method and I tried to use that to sort the data. It works for all data that does not have HTML links in the table. I was wondering if anyone had any ideas on how I could both be able to sort the data, format the data, and all without breaking the links. Here are the threads that I talked about in this post:

http://datatables.net/forums/discussion/367/bug-sort-number-column-and-stype/p1

http://www.datatables.net/forums/discussion/2620/format-numbers/p1

And here is the relevant dataTables code for the table that I am working on.

[code]
$('#archive_table').dataTable({
"aoColumns": [
null,
null,
{ "sType": "num-html" },
null,
null,
null
],
"bFilter" : false,
"bInfo" : false,
"bLengthChange" : false,
"bPaginate" : false,
"bSort" : true,
"aaSorting": [[ 6, "desc" ]],
"aoColumnDefs": [
{ "bSearchable": false, "bVisible": false, "aTargets": [ 6 ] },
{ "aTargets": [ 3, 4, 5 ], "bUseRendered": false, "fnRender": function ( o ) {
return o.oSettings.fnFormatNumber( parseFloat( o.aData[ o.iDataColumn ] ) );
}
}
]
});
[/code]

If this was confusing I would be happy to try and clear it up and I would really appreciate an answer on this.

Thank You!

nuttguy

I'm a total idiot and realized that I forgot to post up a working example. Here is the link to the working example:

http://live.datatables.net/ezorix/5/edit#javascript,html,live

Let me know if I can answer any questions, any help that someone could pass my way would be fantastic!

nuttguy

After a lot of thinking I was finally able to come up with an answer to my own question. How I came up with it was I used what I learned from being able to sort data with HTML Strings and applied that in order to get rid of commas. Instead of adding the commas at the JavaScript level I added the commas at the SQL level. I did this because if you apply the fnFormatNumber to data with HTML Strings in front (example links) then it will return NaN (Not a Number). So do your formatting on the SQL level then use a custom sorting method to filter out commas and HTML Strings. Using this approach I was able to achieve what I was looking for. I am sure there is an easier solution but this is what I was able to come up with! If someone else has a better solution then I am very open to it! Here is the new DataTables code after the changes.

[code]
aTable = $('#archive_table').dataTable({
"aoColumns": [
null,
null,
{ "sType": "num-html" },
null,
null,
null
],
"bFilter" : false,
"bInfo" : false,
"bLengthChange" : false,
"bPaginate" : false,
"bSort" : true,
"aaSorting": [[ 6, "desc" ]],
"aoColumnDefs": [
{ "bSearchable": false, "bVisible": false, "aTargets": [ 6 ] },
{ "aTargets": [ 3, 4, 5 ], "bUseRendered": false, "fnRender": function ( o ) {
return o.oSettings.fnFormatNumber( parseFloat( o.aData[ o.iDataColumn ] ) );
}
}
]
});
[/code]

And here is a working example with the right sorting methods and what not. I hope that this helps somebody out there with their problem!

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

Note: The column that was really giving me problems was the MM CNT column. As you can probably see from the DataTables code above I am not using DataTables fnFormatNumber method in order to add commas to my numbers. The commas are added in at the SQL level so if you look at the HTML in the working example you will already see commas added in.

I know this was probably a little confusing please let me know if you have any questions I will do my best to answer them.

allan

Hi,

Sorry I wasn't able to reply you your earlier posts. Your example above looks fine, but it is worth saying that fnRender is deprecated and will be removed in v1.10 (it already has been in git).

> on how I could both be able to sort the data, format the data, and all without breaking the links

This is what I've been calling 'orthogonal data' and is a major part of DataTables now (indeed v1.10 is going to expand the options available). There is a blog post about it here: http://datatables.net/blog/Orthogonal_data - although it talks about mData which is a little more complicated than mRender which is more than good enough for what you are looking for here.

You would do something like this:

[code]
var formatter = $.fn.dataTables.default.fnFormatNumber;
...

// In the column initialisation:
mRender: function ( data, type, row ) {
if ( type === 'display' ) {
return formatter( data );
}
else if ( type === 'filter' ) {
return data +' '+ formatter( data );
}
return data;
}
[/code]

So it is using the number formatter from the DataTables defaults (which is the same as the function you used, but mRender doesn't get the settings object passed in), as well as different data being used for different types:

- display - the formatted number
- filtering - the formatted number and the unformatted number (so the user could type '34,432' or 34432' and both would match)
- sorting and type detection - the original unformatted number

This is available and can be done in v1.9.4+ :-)

Allan

nuttguy

Hi Allan,

Thanks very much for your post! This newer way (or not so new way it just looks like i'm behind) is really cool! I looked over your examples and they seem to make sense. I'll put it on my to-do list to upgrade the scripts I wrote in order to use mRender instead of fnRender. Really love DataTables by the way I think you do some really amazing work! Can't wait to see what else comes out of DataTables in the future.

Thanks Again,
NuttGuy