Sorting columns with some empty cells

AlixC

Hi,
I use datatables to display a financial dataset.
In the columns 5 to 9 values can be 0, but column 10 has a sum of the previous columns and always has a value different from 0.
I have used the render sections of columnDefs to set cells with values of zero to return empty strings because 0 isn't a relevant information and that removes useless clutter from the table:

columnDefs: [
  {
    targets: [0, 2, 3],
    width: '80px'
  },
  {
    targets: [5, 6, 7, 8, 9],
    width: '100px',
    render: function (data) {
      return (data === 0) ? '' : $.fn.dataTable.render.number(' ', ',', 2).display(data);
    },
  },
  {
    targets: 10,
    width: '120px',
    render: $.fn.dataTable.render.number(' ', ',', 2)
  }
],

But that seems to brick the column sorting.
If all the cells are populated, with numbers, then sorting is fine because all cells have numerical values, but if some cells in the column have no value then the sorting appears to be alphanumerical and with the spaces as thousands separators a row with a value of '1 234' ends up before a row with a value of '234' in ascending order, for instance.

I tried to work around the issue by leaving the zeros, but setting these cells css to have the same color for background and text, but I use some css/js to highlight the row under the cursor and then the zeros become visible and it's starting to feel like a lot of extra work to try and work around that too.

columnDefs: [
  {
    targets: [0, 2, 3],
    width: '80px'
  },
  {
    targets: [5, 6, 7, 8, 9],
    width: '100px',
    render: function (data) {
      return (data === 0) ? data : $.fn.dataTable.render.number(' ', ',', 2).display(data);
    },
    createdCell: function (td, cellData, rowData, row, col) {
      if (cellData === 0) {
        $(td).css({ 'color': '#E2E2E2' });
      }
    }
  },
  {
    targets: 10,
    width: '120px',
    render: $.fn.dataTable.render.number(' ', ',', 2)
  }
],

Any ideas to handle that scenario?

AlixC

Ok, nevermind, I missed the columns.type option that solves the problem.