Ignore Zero When Rendering a Number in Money Format

Ninja Joe

To display currency, I am using:

columnDefs: [
{ targets: [ 1 ], render: $.fn.dataTable.render.number( ',', '.', 2, '$','' ) },
],

It works great! My numbers are displayed as $136,029.14.

However, when a number is 0, I want to show my users 0, not $0.00. How can I modify my code in columnDefs to do that? My first thought was a ternary statement but without knowing the value of the cell, I am unsure how to proceed.

kthorngren

Use columns.render. The number renderer, $.fn.dataTable.render.number, can take an additional API display(). Something like this should work:

columnDefs: [
  { targets: [ 1 ], 
    render: function (data, type, row) {
      return data === 0 ? 0 : $.fn.dataTable.render.number( ',', '.', 2, '$','' ).display( data );
  }
],

Didn't test this code so hopefully its close enough to make work

Kevin

Ninja Joe

Thank you, kthorngren. Your suggestion worked, WITH some modifications. Here's the final code I ended up using, hopefully it helps someone in the future:

$( '#certificates-datatable' ).DataTable( { scrollX: true, pageLength: 50, pagingType: 'full_numbers', 'order': [ [ 4, 'desc' ] ],
    columnDefs: [
        { targets: [ 1 ], render: function( data, type, row ) {
            return ( data === '0' || data === '0.00' ) ? '0' : $.fn.dataTable.render.number( ',', '.', 2, '$', '' ).display( data ) }
        },
    ]
} );

allan

I think this is a really good point actually. I think this is something that should be built into the number renderer. I'll add that for the next release.

Allan

soulbriski

How would this work if we wanted to swap currency for percentage after an update to the data?

Scenario: The table is a list of voucher codes originally drawn with column 5 displaying either % or £ values. I edit one row and change it from 50% to £68 (I'm only changing the value, not the symbol)

The table is originally drawn and column 5 is rendered thus:

function(data,type,row,meta){var spec;switch(row.user_credit_type){case '0':spec=new Intl.NumberFormat('en-GB', { style: 'currency', currency: 'GBP'}).format(data);break;case '1':spec=new Intl.NumberFormat('en-GB', { style: 'percent', minimumFractionDigits: 2}).format(data/100);break;}return spec;}

When I edit one of the rows via a popup modal, I update the table using:

table.cell(that, 'credit:name').data(myData.voucher_value)

which all works perfectly well but it now says 68%

How do I update the rendering to display it as £ instead of %

Many thanks

kthorngren

@soulbriski

How do I update the rendering to display it as £ instead of %

Without seeing a running example showing the problem its hard to say what the problem might be. First step is to do some debugging of the columns.render function when making the change to the data. Presumably when making the change the value of row.user_credit_type is 1 causing the style { style: 'percent', minimumFractionDigits: 2} to be applied.

If you need help debugging this please provide a link to your page or a test case replicating the issue.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

srgloureiro

@allan Are there any updates on this?

(novice in jquery myself)

I want to show:

1 => 1 (not 1,00)

1.5 => 1,5 (not 1,50)

allan

I've never thought of a nice way to implement an option for this in the number renderer API. Possibly the precision parameter could double as a "significant figures" option if prefixed with < or something like that? That's assuming I've understood your request corrected - you want a maximum of 2 decimal places to show, but reduce it to significant figures only if possible?

Allan