DataTables - Number and Date Formats

ericlemes

Hi there!

First I would like to thank you for DataTables, it's a great tool. I'm trying to use it in my .Net projects, creating a .Net wrapper for it and using as a main grid in my default UI architecture. I got some issues here and I need some help.

I need to work with different number and date time formats. I know that DataTables got a plugin interface that gives me the opportunity to create some custom renderers and sorters, but I have some specific issues that I cannot solve without changing the source code.

First one: I would like to tell my columns that it has a decimal separator, a thousand separator and the number of decimal places. I really want to do this in the column, so I don't need to create a custom renderer for "4 decimal places numeric renderer"... My application has a configuration for it.

Second: I need to retrieve these information when sorting the column. So I can convert it back to number and do the sorting.

I think that creating a "oCustomInfo" property in the grid column and retrieving it in sort and render calls solves the problem. Is there any way to do this?

I'm using DataTables 1.4.3.


Thanks in advance,

Eric

allan

Hi Eric,

I might be something here, but will the "Formatted numbers" sorting plug-in do what you are looking for?
http://datatables.net/plug-ins#sorting_formatted_numbers

This will deal with numbers such as 100'000.00 or 100,000.00 etc.

Regards,
Allan

ericlemes

Allan,

I saw this plugin. I'm looking for something like this, but with some more flexibility.

I need to render, based on the user culture, the number with "." as decimal separators or with "," as decimal separator. I really want to create only one render function instead of create one render function per culture.

The same problem happens when I need to configure the number of decimal places of a column. My application have some fields with configurable decimal places. I don't want to generate one javascript function for each variation of decimal places.

I can implement this in DataTables with a custom info property per column and giving this back in render functions. The same can occur in sorting problems, because I need to know back how I rendered the field so I can convert it back to number and sort it. The current call in custom sort functions don't let me get back information of the current sorted column.

I think this implementation can give lots of extra flexibility to users, giving them the ability to create complex render functions based on custom information associated to the column and converting it back for sorting purposes.


Thanks for your attention,

Eric

allan

Hi Eric,

Okay - I think I'm getting a bit of a handle on the problem. I assume your rendering function is on the server-side rather than using fnRender() in DataTables?

The reason you can't pass extra information into the sort functions is that this ability is not available through the Array.sort() method which DataTables uses. It might be possible to double wrap your functions, but the idea was that the only 'extra' information the developer could give to the function would be the function name (due to the Array.sort() limitation).

What you might consider doing is using a sorting function such as the one here: http://datatables.net/forums/comments.php?DiscussionID=223#Item_13

This will allow you to do something like this in your HTML:

[code]
100'000.00
[/code]

i.e. you have your rendered information available for the user to see, but you output the raw data into the hidden title attribute to allow DataTables to sort on that.

How does that sound?

Allan

ericlemes

No... the render function is on the client side, but it uses some extra information on the column to render this. I have this implemented, as follows.

My DataTable initialization script follows:

[code]
myDataTable = $("#theGrid").dataTable({
aoColumns: [{
sType: 'numeric',
fnRender: RenderDecimalNumber,
oCustomInfo{
"decimalPlaces":2,
"thousandSeparator":".",
"decimalSeparator":","}
}]
});
[/code]

My render function uses the NumberFormat JS from http://www.mredkj.com/javascript/numberFormat.html

[code]
function RenderDecimalNumber(oObj) {
var num = new NumberFormat();
num.setInputDecimal('.');
num.setNumber(oObj.aData[oObj.iDataColumn]);
num.setPlaces(this.oCustomInfo.decimalPlaces, true);
num.setCurrency(false);
num.setNegativeFormat(num.LEFT_DASH);
num.setSeparators(true, this.oCustomInfo.decimalSeparator, this.oCustomInfo.thousandSeparator);

return num.toFormatted();
}
[/code]

I needed to do some modifications on your source code, so I can retrieve oCustomInfo inside my render function. The modification is in the _fnAddColumn, and follows:

[code]
function _fnAddColumn(oSettings, oOptions) {
oSettings.aoColumns[oSettings.aoColumns.length++] = {
"sType": null,
"_bAutoType": true,
"bVisible": true,
"bSearchable": true,
"bSortable": true,
"sTitle": null,
"sWidth": null,
"sClass": null,
"fnRender": null,
"bUseRendered": true,
"iDataSort": oSettings.aoColumns.length - 1,
"oCustomInfo": null
};

/* User specified column options */
if (typeof oOptions != 'undefined' && oOptions !== null) {
var iLength = oSettings.aoColumns.length - 1;

if (typeof oOptions.sType != 'undefined') {
oSettings.aoColumns[iLength].sType = oOptions.sType;
oSettings.aoColumns[iLength]._bAutoType = false;
}

if (typeof oOptions.bVisible != 'undefined') {
oSettings.aoColumns[iLength].bVisible = oOptions.bVisible;
}

if (typeof oOptions.bSearchable != 'undefined') {
oSettings.aoColumns[iLength].bSearchable = oOptions.bSearchable;
}

if (typeof oOptions.bSortable != 'undefined') {
oSettings.aoColumns[iLength].bSortable = oOptions.bSortable;
}

if (typeof oOptions.sTitle != 'undefined') {
oSettings.aoColumns[iLength].sTitle = oOptions.sTitle;
}

if (typeof oOptions.sWidth != 'undefined') {
oSettings.aoColumns[iLength].sWidth = oOptions.sWidth;
}

if (typeof oOptions.sClass != 'undefined') {
oSettings.aoColumns[iLength].sClass = oOptions.sClass;
}

if (typeof oOptions.fnRender != 'undefined') {
oSettings.aoColumns[iLength].fnRender = oOptions.fnRender;
}

if (typeof oOptions.bUseRendered != 'undefined') {
oSettings.aoColumns[iLength].bUseRendered = oOptions.bUseRendered;
}

if (typeof oOptions.iDataSort != 'undefined') {
oSettings.aoColumns[iLength].iDataSort = oOptions.iDataSort;
}

if (typeof oOptions.oCustomInfo != 'undefined') {
oSettings.aoColumns[iLength].oCustomInfo = oOptions.oCustomInfo;
}
}

/* Add a column specific filter */
oSettings.aoPreSearchCols[oSettings.aoPreSearchCols.length++] = {
"sSearch": "",
"bEscapeRegex": true
};
}
[/code]

I think using the same approach on the sort functions will give some extra functionality to DataTables. The only gap is that I can retrieve column information (oCustomInfo) inside the sort functions. What do you think about this?


Cheers,

Eric

allan

Hi Eric,

It's becoming clearer which each post :-)

Two things from me here. Firstly when you are passing your custom object to the rendering function, why not just do something like this (note a small modification to RenderDecimalNumber() is required to use two arguments):

[code]
myDataTable = $("#theGrid").dataTable({
aoColumns: [{
sType: 'numeric',
fnRender: function (oDt) {
return RenderDecimalNumber( oDt, {
"decimalPlaces":2,
"thousandSeparator":".",
"decimalSeparator":","
} );
}
}]
});
[/code]

Secondly, regarding the sorting, you could set the bUseRendered ( http://datatables.net/usage#bUseRendered ) parameter to false for this column such that DataTables will use the original data for sorting, rather than the rendered data.

Regards,
Allan

ericlemes

Wow!

[code]
fnRender: function (oDt) {
05. return RenderDecimalNumber( oDt, {
06. "decimalPlaces":2,
07. "thousandSeparator":".",
08. "decimalSeparator":","
09. } );
[/code]

I'm not in this level of Javascript Kung-fu yet! ;-)

I like the bRendered too. It's something that I was looking for. The only problem here is that the user may have problems with filters.... Example:

1) I sent a number to DataTables (0.5150).
2) The column has bUseRendered = true;
3) The rendered content becomes 0,51.
4) When the user filter the data looking for 0,51, I think I'll have no results.

Is there any workaround for this?

Thanks again for your help.

Eric

allan

Hi Eric,

Good to hear that rendering function should do the trick for you - I love nested functions in Javascript!

Regarding the sorting - yes it is a bit of a pickle that... How about something like this:

[code]
fnRender: function (oDt) {
return ''+
RenderDecimalNumber( oDt, {
"decimalPlaces":2,
"thousandSeparator":".",
"decimalSeparator":","
} );
}
[/code]

Then you can use the sorting function given here on that column: http://datatables.net/forums/comments.php?DiscussionID=223#Item_13

This is the problem with bUseRendered - some data is useful for some cases, and the other data is useful for others! This should hopefully give you the best of both worlds.

Allan

ericlemes

Alan,

The sorting solution doesn't solve my problem. The numbers will be sorted as string.

I think nothing solves this 100%.


Thanks for your help, anyway.

Eric

allan

Hi Eric,

Doesn't the "x = parseFloat( x );" type lines in the sorting function take account of converting the numbers to a float and then sorting accordingly?

Allan (thinking that there must be a solution!)

ericlemes

Allan,

Will not work. Think in the same problem with dates. Internally we got a javascript date object, rendered as "Thu Jan 01 2009 01:01:01 GMT-0200", and the user sees "01/01/2009 01:01:01"... the data that the users searchs is different from the data that it sees.

Remembering that in Brazil, the date format is DD/MM/YYYY, in EUA it is MM/DD/YYYY... that will potentially confuses the user.

I think if we have two properties, bUseRenderedSort, bUseRenderedForSearch everything will be solved.


Thanks,

Eric

allan

Hi Eric,

This is why I am suggesting that you use hidden information for the sorting. The unrendered 'raw' data would be put into the span attribute and would be used for sorting upon by the custom sorting functions I have linked to. Furthermore the filtering would be done on what is visible to the end user. You would probably also need to use a custom filtering function for the 'title-numeric' type columns. This can be done by using the ofnSearch API. For example (this will strip HTML tags which is what you want):

[code]
$.fn.dataTableExt.ofnSearch['title-numeric'] = function ( sData ) {
return sData.replace(/\n/g," ").replace( /<.*?>/g, "" );
}
[/code]

I am very reluctant to introduce bUseRenderedForSearch because it would mean adding another array column for all tables, and introducing extra processing. Also there is the various APIs that DataTables presents and which can provide an answer to the problem.

Allan

houbeless

Hi Allan,
I used this function to number formating - it's look very good, but I have some bugs..

I have

[code] "aoColumns": [{
"sType": "numeric",
"fnRender": function (oObj) {
return RenderDecimalNumber( oObj, {
"decimalPlaces": 2,
"thousandSeparator": " ",
"decimalSeparator": "," });
}}],

function RenderDecimalNumber(oObj) {
var num = new NumberFormat();
num.setInputDecimal('.');
num.setNumber(oObj.aData[oObj.iDataColumn]);
num.setPlaces(this.oCustomInfo.decimalPlaces, true);
num.setCurrency(false);
num.setNegativeFormat(num.LEFT_DASH);
num.setSeparators(true, this.oCustomInfo.decimalSeparator, this.oCustomInfo.thousandSeparator);

return num.toFormatted();
} [/code]

And chrome develop. tools write me:
function RenderDecimalNumber(oObj) {
var num = new NumberFormat();
558: Uncaught ReferenceError: NumberFormat is not defined

Could you help me with this?
Thank you very much.
Houbeless

houbeless

Sorry, my fault, i didn't mount js numberformat source..