Datatable function that filters below value in column

user_11058930 0
Viewed 917

I'm trying to create a button that when clicked, it filters my datatable where a specific column is less than 6. I'm trying to work off this example but struggling a little with my lack of programming/Javascript knowledge.

Any help appreciated

    function filterBelowSix() {
        var maxScore = 6;
        var totalScoreField = parseFloat( data[3] ); // use data for the total score column i think this the column index
        var table = $('#customer_df').DataTable();

        if (maxScore <= totalScoreField) {
            return true;
        } else { return false;}


       // how do i pass if else statement to table, so then i can order by specific columns after its been filtered? 
        table.order([8,'desc' ],[3,'asc']).draw();
     
    }

then the button would be something like this:

 <a class="quick-filter" href="#" onclick="filterBelowSix(); return false;">Filter below 6</a>
datatables jquery javascript
1 Answers

You can use the same filtering function that is mentioned in the linked example:

$.fn.dataTable.ext.search.push()

Combining this with a click event for the button, and the required sorting API call, gives us this:

$(document).ready(function() {

  var table = $('#example').DataTable();

  // register the click event for our button
  // I prefer doing this as opposed to using onclick in the button itself
  $( "#below_x" ).click(function() {
    filterBelowValue();
  });

  // the filter followed by a sort and table re-draw:
  function filterBelowValue() {
    var threshold = 60; // or whatever you want
    var colIdx = 3; // 4th column (first col has index of 0)

    $.fn.dataTable.ext.search.push(
      function( settings, data, dataIndex ) {
        return (data[colIdx] < threshold);
      }
    );

    table.column( colIdx ).order( 'asc' ).draw();
  }

} );

In my test case, the button and HTML table are as follows:

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

    <button id="below_x" type="button">Click Me!</button> 

    <br><br>

    <table id="example" class="display dataTable cell-border" style="width:100%">
        <thead>
            <tr>
                <th>Name</th>
                <th>Position</th>
                <th>Office in Country</th>
                <th>Age</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>Tiger Nixon</td>
                <td>System Architect</td>
                <td>Edinburgh</td>
                <td>61</td>
                <td>2011/04/25</td>
                <td>$320,800</td>
            </tr>
            <tr>
                <td>Ashton Cox</td>
                <td>Junior Technical Author</td>
                <td>San Francisco</td>
                <td>66</td>
                <td>2009/01/12</td>
                <td>$86,000</td>
            </tr>
            <tr>
                <td>Cedric Kelly</td>
                <td>Senior Javascript Developer</td>
                <td>Edinburgh</td>
                <td>22</td>
                <td>2012/03/29</td>
                <td>$433,060</td>
            </tr>
            <tr>
                <td>Donna Snider</td>
                <td>Customer Support</td>
                <td>New York</td>
                <td>27</td>
                <td>2011/01/25</td>
                <td>$112,000</td>
            </tr>
        </tbody>
    </table>

</div>

So, column index 3 is the "Age" column - and I filter all records where the age is less than 60 - and then I sort by that column (in ascending order).

Update

Additional question: How to reset the data to remove the filter?

This can be done using a similar technique - but with an important extra step.

The current filtering logic involves pushing a function onto the search API:

$.fn.dataTable.ext.search.push(...);

If we want to remove the filter, we will be performing a different function to achieve this. Therefore we have to remove the initial function from the filter API (we have to pop it off the array of functions we have created. Typically this will be an array of one.

The reset function on its own, without this extra step, would look like this:

The button:

<button id="reset" type="button">Reset!</button>

The event handler:

$( "#reset" ).click(function() {
  doReset();
});

And the doReset function:

function doReset() {
    
  $.fn.dataTable.ext.search.push(
    function( settings, data, dataIndex ) {
      return true; // show all rows!
    }
  );
  table.column( 0 ).order( 'asc' ).draw();
}

However, as noted above, this will push a second function onto the array of filter functions - and this second function will not work as expected because the first function (the one which filters the data) is still in place.

Therefore we have to clear the array of functions first:

while($.fn.dataTable.ext.search.length > 0) {
  $.fn.dataTable.ext.search.pop();
}

This fragment needs to be placed before both the filter function and the all-clear function:

  function filterBelowValue() {
    var threshold = 60; // or whatever you want
    var colIdx = 3; // 4th column (first col has index of 0)

    while($.fn.dataTable.ext.search.length > 0) {
      $.fn.dataTable.ext.search.pop();
    }

    $.fn.dataTable.ext.search.push(
      function( settings, data, dataIndex ) {
        return (data[colIdx] < threshold);
      }
    );
    table.column( colIdx ).order( 'asc' ).draw();

  }

  function doReset() {
    while($.fn.dataTable.ext.search.length > 0) {
      $.fn.dataTable.ext.search.pop();
    }
    
    $.fn.dataTable.ext.search.push(
      function( settings, data, dataIndex ) {
        return true; // show all rows!
      }
    );
    table.column( 0 ).order( 'asc' ).draw();
  }
user_125673650
Related