DataTables - Number and Date Formats

DataTables - Number and Date Formats

ericlemesericlemes Posts: 6Questions: 0Answers: 0
edited July 2009 in General
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

Replies

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    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
  • ericlemesericlemes Posts: 6Questions: 0Answers: 0
    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
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    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
  • ericlemesericlemes Posts: 6Questions: 0Answers: 0
    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
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    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
  • ericlemesericlemes Posts: 6Questions: 0Answers: 0
    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
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    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
  • ericlemesericlemes Posts: 6Questions: 0Answers: 0
    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
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    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!)
  • ericlemesericlemes Posts: 6Questions: 0Answers: 0
    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
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    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
  • houbelesshoubeless Posts: 11Questions: 0Answers: 0
    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
  • houbelesshoubeless Posts: 11Questions: 0Answers: 0
    Sorry, my fault, i didn't mount js numberformat source..
This discussion has been closed.