Datatable does not sort correctly

SOFMAN commented on Dec 27, 2022

I'm trying to sort the INGRESOS column but it doesn't sort correctly. In the capture $356,340.29 is greater than $92,385.72

Este es mi codigo

$columnas= [
  "NUMERO_MES" =>  [
    "label" => "NUMERO MES"
    "type" => "number"
  ]
  "MES" => [
    "label" => "MES"
    "type" => "string"
  ]
  "INGRESOS" => [
    "label" => "INGRESOS"
    "type" => "number"
    "prefix" => "$"
    "decimals" => 2
    "footer" => "sum"
    "footerText" => "@value"
  ]
]

 DataTables::create(array(
        "name" => "employeesTable",
        "dataSource" => $this->dataStore("proyeccionImpuestoRenta"),
        "themeBase" => "bs3",
        "showFooter" => true,
        "language" => array(
            "lengthMenu" => "Ver _MENU_ registros por página",
            "zeroRecords" => "No existen coincidencias",
            "info" => "Mostrando página _PAGE_ de _PAGES_ (_MAX_ meses)",
            "infoEmpty" => "No existen registros",
            "infoFiltered" => "(Buscando de _MAX_ registros)",
            "sSearch" => "Buscar",
            "paginate" => array(
                "sFirst" => "Primera", // This is the link to the first page
                "sPrevious" => "Anterior", // This is the link to the previous page
                "sNext" => "Siguiente", // This is the link to the next page
                "sLast" > "Última" // This is the link to the last page
            ),
        ),
     
        "options" => array(
            "searching" => true,
            "colReorder" => array("realtime" => false),
            "ordering" => true,
            "fixedHeader" => true,
            "select" => array(
                "style" => "multi"
            ),
            "paging" => false,
            "order" => array(
                array(2,'desc')
            )
        ),
        "columns" => $columnas
    ));

Sebastian Morales commented on Dec 28, 2022

Pls try "type" => "num-fmt" for your INGRESOS column like this:

  "INGRESOS" => [
    "label" => "INGRESOS"
    "type" => "num-fmt"

It should help with sorting that column by formatted numbers.

SOFMAN commented on Dec 28, 2022

I cannot use this type of data because the format of the amounts in the INCOME column is lost, I use the $ prefix and also decimal and thousands separators. When using num-fmt that format is lost so the indicated solution does not help me.

Something I noticed is that when serverSide is true it sorts fine but when serverSide is false the problem occurs

Sebastian Morales commented on Dec 29, 2022

I see your point. Pls try an alternative method to keep number format working while still sorting like "num-fmt" type:

DataTables::create(array(
    ...
    "columns" => array(
        ...
        "INGRESOS" => [
            ...
            "type" => "number"
        ...
    ),
    "options" => array(
        ...
        "columnDefs" => [
            [
                "targets" => [2], // assuming "INGRESOS" is the 3rd column, i.e index 2
                "type" => "num-fmt",
            ],
        ], 
    )
));

KoolReport's Forum

Datatable does not sort correctly #2919

Build Your Excellent Data Report