Implementing Number Helpers and Filters

duggy_tables

Hi,

Apologies, I'm a bit of a noob and my js is baaaad. I have the following working code in an MVC5 web app. It allows me to filter the data set on hidden fields and works but with a couple of issues.

The dates appear as datetime and the numbers have no formatting. Also, the sorts apply to dates and numbers as if they were strings (despite the fact that in the database and model, they have the correct data types of date, int and decimal respectively).

$(document).ready(function() {
    $('#list').DataTable( {
        columnDefs: [
            { targets: [0, 1, 2, 3], visible: true},
            { targets: '_all', visible: false },
            {
                "orderSequence": ["asc"], "targets": [1]
            }
        ],
        initComplete: function () {
            this.api().columns("4").every( function () {
                var column = this;
                var select = $('<select><option value=""></option></select>')
                    .appendTo( $("#role_menu_placeholder").empty() )
                    .on( 'change', function () {
                        var val = $.fn.dataTable.util.escapeRegex(
                            $(this).val()
                        );
 
                        column
                            .search( val ? '^'+val+'$' : '', true, false )
                            .draw();
                    } );
 
                column.data().unique().sort().each( function ( d, j ) {
                    select.append( '<option value="'+d+'">'+d+'</option>' )
                } );
            } );
            
                        this.api().columns("5").every( function () {
                var column = this;
                var select = $('<select><option value=""></option></select>')
                    .appendTo( $("#status_menu_placeholder").empty() )
                    .on( 'change', function () {
                        var val = $.fn.dataTable.util.escapeRegex(
                            $(this).val()
                        );
 
                        column
                            .search( val ? '^'+val+'$' : '', true, false )
                            .draw();
                    } );
 
                column.data().unique().sort().each( function ( d, j ) {
                    select.append( '<option value="'+d+'">'+d+'</option>' )
                } );
            } );
        }
    } );
} );

I tried adding this in

            {
                targets: [3],
                render: list.render.number(null, null, 2, '$')
            },

As shown below. However, this just breaks the filter functionality and still doesn't format the column correctly.

My end goal here is to format column 0 as a date (e.g. 2012-12-31), instead of a string displaying a datetime, columns 1 and 2 as integers with commas (e.g. 10,200,123) and column 3 as a USD currency (e.g. $1,242,000).

If anyone can help me out here I'd be super grateful!

Thanks in advance

$(document).ready(function() {
    $('#list').DataTable( {
        columnDefs: [
            {
                targets: [3],
                render: list.render.number(null, null, 2, '$')
            },
            { targets: [0, 1, 2, 3], visible: true},
            { targets: '_all', visible: false },
            {
                "orderSequence": ["asc"], "targets": [1]
            }
        ],
        initComplete: function () {
            this.api().columns("4").every( function () {
                var column = this;
                var select = $('<select><option value=""></option></select>')
                    .appendTo( $("#role_menu_placeholder").empty() )
                    .on( 'change', function () {
                        var val = $.fn.dataTable.util.escapeRegex(
                            $(this).val()
                        );
 
                        column
                            .search( val ? '^'+val+'$' : '', true, false )
                            .draw();
                    } );
 
                column.data().unique().sort().each( function ( d, j ) {
                    select.append( '<option value="'+d+'">'+d+'</option>' )
                } );
            } );
            
                        this.api().columns("5").every( function () {
                var column = this;
                var select = $('<select><option value=""></option></select>')
                    .appendTo( $("#status_menu_placeholder").empty() )
                    .on( 'change', function () {
                        var val = $.fn.dataTable.util.escapeRegex(
                            $(this).val()
                        );
 
                        column
                            .search( val ? '^'+val+'$' : '', true, false )
                            .draw();
                    } );
 
                column.data().unique().sort().each( function ( d, j ) {
                    select.append( '<option value="'+d+'">'+d+'</option>' )
                } );
            } );
        }
    } );
} );

colin

We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin