DataGrid not sorting numerics

Nicholas Savill commented on Dec 21, 2020

Hi

I am struggling to get the DataGrid to sort numbers correctly. As you can see from the screenshot, sorting by amount_invoiced sorts as a string, not as a number. This 123.00 sorts before 2.00.

I have tried various options to work round the issue but none seem to work.

    DataTables::create([
      "dataSource"=>$report->dataStore("unpaidInvoices"),
      "columns"=>[
        "invoice_no" => [
          "label" => "Invoice No.",
          "type"=>"number",
        ],
        "invoice_dt" => [
          "label" => "Invoice Date",
          "data-order" => "raw_invoice_dt",
        ],
        "customer_name" => [
          "label" => "Customer",
        ],
        "amount_invoiced"=>[
          "label" => "Amount Invoiced",
          "type"=>"number",
          "decimals"=>2,
        ],
        "amount_paid"=>[
          "label" => "Amount Paid",
          "type"=>"number",
          "decimals"=>2,
        ],
        "amount_owing"=>[
          "label" => "Amount Owing",
          "type"=>"number",
          "decimals"=>2,
        ],
        "due_dt" => [
          "label" => "Payment Due",
          "data-order" => "raw_due_dt",
        ],
        "status" => [
          "label" => "Status",
        ],
        "raw_invoice_dt",
        "raw_due_dt",
      ],
      "options" => [
        "searching"=>true,
        "paging"=>true,
      ],
      "cssClass" => [
        "th" => function ($colName) {
          if (substr($colName,0,6)=="amount") {
            return "text-right";
          } elseif (substr($colName,0,3)=="raw") {
            return "d-none";
          }
        },
        "td" => function ($row,$colName) {
          if (substr($colName,0,6)=="amount") {
            return "text-right";
          } elseif (substr($colName,0,3)=="raw") {
            return "d-none";
          }
        },
      ]
    ]);

Any help would be appreciated.

Thanks Nick

Nicholas Savill commented on Dec 21, 2020

I have found a workaround, which is to create "hidden" columns (i.e. set to display:none) by taking the numeric value and adding a large number, so that the number of digits is the same.

For example, for the invoice_no field, I have calculated a new column raw_invoice_no as follows in the report definition: `

  ->pipe(new CalculatedColumn([
    "raw_invoice_no" => [
      "exp" => function($data){
        return $data['invoice_no']+1000000;
      }
    ],
  ]))

and changed the report definition by adding "data-order" => "raw_invoice_no" to the column definition and adding the raw_invoice_no column to the table (you will notice that the cssClass function hides this column from display in my original DataTables call).

However, there must be a better way than this, surely?

Nick

Sebastian Morales commented on Dec 21, 2020

Nicholas, it's excellent of you to find a great solution. Another one I can think of is using column meta definition like documented here:

https://www.datatables.net/plug-ins/sorting/

Just replace the "targets" value with your sorting column order. Let us know if you have any difficulty with this approach. Cheers,

Nicholas Savill commented on Dec 22, 2020

Hi Sebastian

Thank you for your reply. I'm not 100% clear how to install the plugin and use in with KoolReport (see also https://www.koolreport.com/forum/topics/1781).

If I understand this correctly, all I need to do is add <script type="text/javascript" src="dataTables.numericComma.js"></script> in the <head> section of my page, and then add a "columnDefs" attribute in my call to DataTables::create(). Is that correct? So nothing to do in the "plugins" property?

Best wishes Nick

Sebastian Morales commented on Dec 22, 2020

Nicholas, I've just checked and it seems that the latest DataTables has a "num-fmt" type that supports sorting by formatted number without any outside plug-in. Please add the following option to your DataTables:

    DataTables::create(array(
        ...
        "options" => array(
            "columnDefs" => array(
                ["type" => "num-fmt", "targets" => [2, 3, 4]] //assuming 2, 3, 4 are orders of the numeric columns you want to sort
            )
        ),
    ));

Let me know how this works. Cheers,

Nicholas Savill commented on Dec 22, 2020

That worked very well. In fact it was even simpler, as I could just add "type"=>"num-fmt" to the column definition. The type does not accept the decimals property, but I was able to format the number with formatValue and it still sorted very well.

    DataTables::create([
      "dataSource"=>$report->dataStore("unpaidInvoices"),
      "columns"=>[
        "invoice_no" => [
          "label" => "Invoice No.",
          "type"=>"num-fmt",
        ],
        "amount_invoiced"=>[
          "label" => "Amount Invoiced",
          "type"=>"num-fmt",
          "formatValue"=>function($value, $row, $cKey){
            return number_format($value,2);
          }
        ],
        ...

Thank you for your help!

KoolReport's Forum

DataGrid not sorting numerics #1780

Build Your Excellent Data Report