how to sort in a bootstrap3 DataTable using derived value from column?

Question

I am using a DataTable in a bootstrap app and it is working great.

You can see the app running at (for example): https://opencalaccess.org/calaccess/filingdate/2022-11-09

But. I have:

    <script>
        $(document).ready(function () {
            $('#filings').DataTable(
                {
                    "columns": [
                        {"name": "Filing ID", "orderable": true},
                        {"name": "Filer ID", "orderable": true},
                        {"name": "Filer Name", "orderable": true},
                        {"name": "Filer Type", "orderable": true},
                        {"name": "Period", "orderable": true},
                        {"name": "Form", "orderable": true},
                        {"name": "Amounts", "orderable": false},
                        {"name": "Rpt Covers", "orderable": false}
                    ]
                }
            );
        });
    </script>

And, like I said, it works great. But I want to sort of the Amounts column also.

But the Amounts column is actually put together from several possible values. I would like to sort on the sum of those values, but I do not need to display the sum of those values.

So, say I have two rows:

<tr>
    <td>col1</td>
    <td>
      thing1: 100,
      thing2: 100,
      thing3: 200
      <div style="visibility: hidden;">sum: 400</div>
    </td>
</tr>
<tr>
    <td>col1</td>
    <td>
      thing1: 100,
      thing2: 1000
      <div style="visibility: hidden;">sum: 1100</div>
    </td>
</tr>

I would like to have the standard sort buttons on the column, but I want them to sort on the sum value in each row.

Possible?

I see things to try that might work but they all seem very awkward and ugly.

FYI, the versions of the libraries that I am using are:

https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js
https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css
https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css
https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js

Is this relevant? In DataTable, how to sort a column by partial value? Is jquery DataTable the same as the bootstrap DataTable?

What ended up working. The cell contents are:

This is a bit tricky to test. The cell now looks like:

 <td style = "white-space: nowrap;">
 <div style="visibility: hidden;">5898748</div>
 debt-amt_incur: $38,001.00<br/>
 debt-amt_paid: $4,824.00<br/>
 expn-amount: $271,681.00<br/>
 rcpt-amount: $234,479.00<br/>
 smry-amount_a: $3,662,227.00<br/>
 smry-amount_b: $1,362,306.00<br/>
 splt-elec_amount: $325,230.00<br/>
 </td>

and the final js is:

 {"name": "Amounts",
  "render": function(data, type, row, meta) {
     if (type === 'display' || type === 'filter') {
     return data;
   }
   else { // type is 'sort' in this case:
     return Number($.parseHTML(data)[0].innerHTML);
   }
  }
 },

One possible approach: use a [column render function](https://datatables.net/reference/option/columns.render), where you can define [orthogonal data](https://datatables.net/manual/data/orthogonal-data). In other words you can display one piece of data, but define a completely different piece of data (your sum) for sorting purposes. See the example using `render: function ( data, type, row ) { ... }`. There are various "orthogonal data" datatables questions on Stack Overflow, which can help. — andrewJames, Nov 29 '22 at 23:58
How exactly _you_ can do this depends on how your table data is sourced (e.g. it's already in the HTML table, or you use Ajax & JSON, or some other approach). Looks like for you it is already in the HTML table. — andrewJames, Nov 29 '22 at 23:59
"_Is jquery DataTable the same as the bootstrap DataTable?_" - See [this page](https://datatables.net/download/index) for the best way to ensure you combine compatible Bootstrap and DataTables libraries. — andrewJames, Nov 30 '22 at 00:04

score 1 · Accepted Answer · answered Nov 30 '22 at 13:54

Here is an example approach using a column render function and orthogonal data.

It uses similar table data to the data provided in the question.

$(document).ready(function() {
  $('#filings').DataTable({
    columns: [{
        name: "Col One",
      },
      {
        name: "Col Two",
        render: function(data, type, row, meta) {        
          if (type === 'display' || type === 'filter') {
            return data;        
          } 
          else { // type is 'sort' in this case:       
            let sum = Number($.parseHTML(data)[1].innerHTML.split(':')[1]);            
            return sum;
          }    
        }
      }
    ]
  });
});

<!doctype html>
<html>

<head>
  <meta charset="UTF-8">
  <title>Demo</title>

  <link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/3.3.7/css/bootstrap.css" />
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.13.1/css/dataTables.bootstrap.css" />
  <script type="text/javascript" src="https://code.jquery.com/jquery-3.6.0.js"></script>
  <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/3.3.7/js/bootstrap.js"></script>
  <script type="text/javascript" src="https://cdn.datatables.net/1.13.1/js/jquery.dataTables.js"></script>
  <script type="text/javascript" src="https://cdn.datatables.net/1.13.1/js/dataTables.bootstrap.js"></script>

</head>

<body>

  <div style="margin: 20px;">

    <table id="filings" class="display dataTable cell-border" style="width:100%">
      <thead>
        <tr>
          <th>Col One</th>
          <th>Col Two</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <td>col1b</td>
          <td>
            thing1: 100, thing2: 200, thing3: 200
            <div style="visibility: hidden;">sum: 500</div>
          </td>
        </tr>
        <tr>
          <td>col1a</td>
          <td>
            thing1: 100, thing2: 1000
            <div style="visibility: hidden;">sum: 1100</div>
          </td>
        </tr>
      </tbody>
    </table>

  </div>



</body>

</html>

The demo uses this:

let sum = Number($.parseHTML(data)[1].innerHTML.split(':')[1]);

to extract the sum value from each hidden <div>. It's just for demo purposes, to show you the approach. I am sure there are more robust approaches. And this only works for the very specific sample data provided in the question.

Reference: parseHTML

Much thanx. The suggestion worked well. Documented the solution at the bottom of the question. — Ray Kiddy, Dec 02 '22 at 23:59

how to sort in a bootstrap3 DataTable using derived value from column?

1 Answers1