Exclude search keyword - do not find.

spacemancw

I have looked in the forum for this but couldn't find.

Very new to datatables. Starting off with a basic setup like https://datatables.net/examples/basic_init/filter_only.html ,
I want to exclude strings from a search. Is there an example of this anywhere?
So for example if I search for London, I'll find all rows with London in any column.
But if there was some sort checkbox that I could click, it would return all rows that DIDN'T have London in any of the columns.
It seems like a simple toggle, include string or exclude string.
Any help would be appreciated.

thanks

kthorngren

Sounds like you will probably need to create custom search plugin. Here is an example:
https://datatables.net/examples/plug-ins/range_filtering.html

Kevin

spacemancw

Thanks very much for the reply. I'm sure the solution is some version of that custom filtering but I can't work it out yet. I'm afraid I'm not familiar with JS yet.

kthorngren

Starting from the select input column search example I put together this example to help you get started:

http://live.datatables.net/xazexepe/1/edit

Please post any questions.

Kevin

spacemancw

Thanks very much for this. I took a look and it is possibly a work around for me. I also took another look at your first link and tried to mimic the MIN/MAX age example.

I added a single input field to my HTML:

<tr><td>Exclude: <input type="text" id="exc" name="exc"></td></tr>

I then modified the javascript :

$.fn.dataTable.ext.search.push(
    function( settings, data, dataIndex ) {
        var exc = $('#exc').val();
        var vfw = data[2]; // use data for the vfw column
        var ref = new RegExp(exc, 'g');

        // VFW
        if (vfw.match(ref))
        {
            return false;
        }

        return true;
    }
);

$(document).ready(function() {
    var table = $('#example').DataTable();

    // Event listener t o the two range filtering inputs to redraw on input
    $('#exc').keyup( function() {
        table.draw();
    } );
} );

I want to exclude based on a column I've named VFW.
When I load the page it shows NO rows. Nothing found.
If I type something in the Exclude input field, say "asa", it displays everything except those rows that match "asa".

This is good, but it's a bummer that zero rows are displayed when it loads.

So I tried giving the input field a value in the HTML :

<tr><td>Exclude: <input type="text" id="exc" name="exc" value="Enter string"></td></tr>

So now when it loads "Enter String" is in the text box, and all the rows load and I can search in the search box, then exclude using the exclude box. However if I delete all the text out of the Exclude box, all the rows go away again, until I type in there again.

kthorngren

This is good, but it's a bummer that zero rows are displayed when it loads.

That's why I push and pop the search plugin depending on the value of the checkbox. With this solution I would check the length of excand if 0 skip the filter. Something list this:

$.fn.dataTable.ext.search.push(
    function( settings, data, dataIndex ) {
        var exc = $('#exc').val();

        //process only if input value
        if (exc.length > 0) {
          var vfw = data[2]; // use data for the vfw column
          var ref = new RegExp(exc, 'g');
 
          // VFW
          if (vfw.match(ref))
          {
              return false;
          }
        }
 
        return true;
    }
);

Kevin

spacemancw

Perfect! thank you. This works. I'm good with having 2 fields, one to search and one to exclude. I created more variables for data[0],1,3 and 4 so that I could exclude based on those columns also and that works now.

Thanks again!

amazeworx

Hi Kevin. Your example is very close to what I need. Thank you.

http://live.datatables.net/xazexepe/1/edit

But I have a case where I need to use multiple select . Can you shed me a light how to do this with multiple select. It's similar to this example, but the result is excluded.

http://live.datatables.net/yewebawo/1/edit

Thanks in advance.

kthorngren

If the exclude checkbox is selected then function ExcludeSearch(term, column) is called. With the select2 code make sure the selected values are in an array then use something like includes() to see if the column is included in the array. For example change if (data[column] != term) return true; to if (term.includes(data[column])) return true;. Haven't tried it but it should be close.

Kevin

amazeworx

Thanks so much for your direction @kthorngren . if (term.includes(data[column])) return false; // Exclude values in term array works like a charm. You're the best!

nightsky_ting

hi kevin,
your example is very helpful .
however, I found out after I check on exclude checkbox and click on the dropbox to filter.
I click the header for sorting, the data will display again.
May I know how to solve this?

nightsky_ting

@amazeworx may I know your select2.js is it by https://select2.org/

hov

Hey, @kthorngren!
Is something like this possible with searchPanes? To exclude specific rows when I select a filter?

Thanks in advance!

sandy

Hi @hov ,

Yes it is possible, take a look at this example.

I'm using columns.searchPanes.options to declare a custom option for the pane. In this case SearchPanes will return everyone who works at edinburgh except for Tiger Nixon.

Hope this helps,
Sandy

hov

Thank you, @sandy for quick response and help!

I was thinking maybe something like this:
live.datatables.net/jibamefa/2/edit

So when I click the Exclude Search checkbox and then select a filter it excludes it from current rows.

Thanks in advance!

sandy

You can get that functionality by doing something like this.

Thanks,
Sandy

hov

@sandy, this was helpful, but I do not want to hardcode my options/filters in code. I would like to have a searchPane that excludes default options/filters from searchPane when checkbox Exclude search is clicked.

Thanks!

colin

But if there was some sort checkbox that I could click, it would return all rows that DIDN'T have London in any of the columns.

I'm not following. In your OP, you said you want to exclude "London" - how would you exclude "London" if it wasn't hard-coded? Are you expecting to have a class or some other identifier?

Colin

hov

Hey, @colin. I didn't specifically say what I would want to exclude. This is @spacemancw post. I just saw something similar to what I would like to implement and tried to expand on it. I realize now that I didn't really describe what I want to do clearly and for that I am sorry.

So, this is what I want: live.datatables.net/jibamefa/2/edit.

SearchPanes are there by default. I would like to implement this behavior:
1. I would click on Exclude search checkbox
2. I would click some filter in searchPanes (Developer/Edinburgh/London/Whatever)
3. I would like to get data without that filter (with that filter excluded).

For example:
If I click on checkbox and click Developer it would show me all rows where position is not Developer. If I click on checkbox and click Edinburgh it would show me all rows where Office is not Edinburgh, and so on.

Is that possible?
Sorry for this misunderstanding and thank you for your help.

sandy

Hi @hov,

Sorry that's not supported as a built in feature. Thank you for the suggestion though, we've taken a note of it.

Thanks,
Sandy

clintonm9

I have tried to figure out how to do the same thing OP is wanting, I think it would be a great addition. Going a step further would be nice to have a + or X in reach row to either exclude or include. But overall what an awesome extension!

apn_ft

i was looking for the same as the OP.
but to be honest.. i like the searchPane (great work), like in the example from @hov

So, this is what I want: live.datatables.net/jibamefa/2/edit.

exclude filter is simply achieved by selecting all (shift+click on last item) and unselect/select the unwanted (ctrl+click).

this searchPane is highly configurable and has a much better usabillity than a exclude checkbox (i even thought about two states of selection in the searchpane, like blue is include and red exclude.. but thats complicating nonsense, also without added value. select/unselect is more than sufficient).
at best it can be discussed about (the only thing i seem to miss) a searchPane menu item next to X for inverting the filter selection.

thinking of it.. do i need more than a exclude filter? a exclude search (eg instead of selecting Edinburgh, filter out all ..burgh cities: Edinburgh, Fraserburgh, Dryburgh, ..).
are there any practical uses for this? in my cases not really, excluding was usually used for frequently occurring field values, not parts of them.
but this could also be easily used here: type burgh in the searchfield of the searchPane menu, select all (shift + click last item), empty searchfield (not X, backspace), invert selection.

ok.. i propose for the searchpane menubar wish list

• a invert button next to X, to invert the selection (also usable to select all)
• a X inside the searchfield, to empty it w/o changing the selection.

sandy

Hi @apn_ft ,

Thanks for the suggestions - another vote added in this area. If more people indicate an interest in a feature like this then we will look at implementing it - but at the moment it isn't something that is requested often enough for us to turn into a fully supported feature.

Thanks,
Sandy

spacemancw

@kthorngren ...

almost 4 years later .. I return to this post ... I see a lot of activity. Anyway ... I have been using the exclude function on all my tables, that you helped me with.

$.fn.dataTable.ext.search.push(
    function( settings, data, dataIndex ) {
        var exc = $('#exc').val();
 
        //process only if input value
        if (exc.length > 0) {
          var vfw = data[2]; // use data for the vfw column
          var ref = new RegExp(exc, 'g');
  
          // VFW
          if (vfw.match(ref))
          {
              return false;
          }
        }
  
        return true;
    }
);

I have a search input field and an exclude input field. So I can search for something, and eliminate from those results with the exclude field. Great! But if I want to exclude more than one term ... it does'nt work that way. So if I search for all firewalls that are version 9.0, I search for "9.0" ... now I want to exclude "London Boston" ... I can't. Is there a way to alter this function to exclude A and B and C etc? I actually do have function that does that, but if I use it, it removes the pagination you get with datatables.

the input fields -

<input type="text" id="myInputS" onkeyup="debounce(myFunctionS(), 500)" placeholder="Search for names..">
<br>
<input type="text" id="myInputExcludeS" onkeyup="debounce(myFunctionS(), 500)" placeholder="Excludes names.."/>

that function is -

// Debounce for search
const debounce = (funct, time) => {
    let timeoutId;
    return function() {
        if(timeoutId) {
            clearTimeout(timeoutId)
        }
        const context = this
        const args = arguments
        timeoutId = setTimeout(() => {
            funct.apply(context, args)
        }, time);
    }
}

//Search and Exclude Input fields

function myFunctionS() {
  var input, filter, table, tr, td, i, txtValue;
  input = document.getElementById("myInputS");
  inputExclude = document.getElementById("myInputExcludeS");
  filter = input.value.toUpperCase();
  filterExclude = inputExclude.value.toUpperCase();
  table = document.getElementById("example");
  tr = table.getElementsByTagName("tr");
  for (i = 0; i < tr.length; i++) {
    td = tr[i].getElementsByTagName("td")[0];
    if (td) {
      txtValue = td.textContent || td.innerText;
      txtValue = txtValue.toUpperCase()
      let match = true
      let f = true // add this line
      filter.split(' ').forEach(q => {
        if(q && q.length > 0) {
          // and add these lines
          if(f) {
            match = false
            f = false
          }
          match = match || txtValue.indexOf(q) > -1  /// only change && instead ||
        }
     })
      filterExclude.split(' ').forEach(q => {
        if(q && q.length > 0)
          match = match && txtValue.indexOf(q) == -1
      })
      if (match) {
        tr[i].style.display = "";
      } else {
        tr[i].style.display = "none";
      }
    }
  }
}

if I use this ... the whole table loads on one long page.
any ideas will be very helpful.

thanks

colin

We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

spacemancw

Colin, been busy but finally getting around to this.

Here is the code with the datatables search and the exclude:
http://live.datatables.net/fivafozu/1/edit?html,css,js,console,output

with this, we get the default behavior. It paginates, 3 pages, 10 per page. I can search for entries that are NOT on the first page, like "WEX"
Search for WEX gives 2 results, add "CAL" to the search (WEX CAL), I get one result, the row that has BOTH those keywords on the same row. What I really want here is all rows that are WEX and all that are CAL. So I couldn't search for WEX and TAL, for instance. And I can only exclude one string.

Here is the code with the search and exclude I would like to use.
http://live.datatables.net/detaxaqa/1/edit?html,css,js,console,output

Again, it starts out with 3 pages. A search for WEX gives nothing. But if I show all 30 entries on one page, the search works. So obviously this is not working as I'd like.
But now that we can see all records on one page, and can search for WEX TAL NOR .. and keep adding strings. It finds all entries. Same with exclude, I can exclude multiple strings. So the search and exclude does what I want, but it currently doesn't work with pagination. It's fine for 30 rows, but I have over 1100 and growing.

thanks for looking at this.

kthorngren

Looks like you might want to change your regex method to this to ignore case:

var ref = new RegExp(exc, 'gi');

You will need to use WEX|CAL to perform a regex or search.

In the second example you are using myFunctionS() to search and it is using Javascript methods to access the row data. Only those shown on the page ore in the DOM so Javascript and jQuery methods have access to only those rows. Also not that the information element is not updated to show the filtered row information. This won't work well with Datatables.

Kevin

spacemancw

Kevin thanks for the reply. Exactly right, only those shown on the page can be searched. I have tried to make this work with datatables, I like the flexibility in the search, it allows lots of criteria to be included and excluded, but only works on non-paginated data. I guess that's that. Thanks again.