Can't do show/hide checkbox for custom rows using API.

fisheriks

Description of problem:
Hi for all. I'm new in this awesome plugin and maybe doing something wrong. But I read API documentation and try different ways to do my functional - but can't do it myself. So please check my code and help me with it.
I have table of support tickets data. The plugin create data table well. But I need additional functional checkbox to show hide custom rows.
Detailed: each row has column "Status". And I need checkbox "Show closed" near search form input, which must hide from table those rows which have status "Closed".
By default checkbox is selected.

So these are my steps...

I generate datatable as usual

$('.tickets-table').DataTable();

Add needed checkbox before search form input

$('#tickets-table_filter').prepend( "<label class='show-closed-filter ms-3' for='show-closed'>Show closed <input type='checkbox' id='show-closed' class='tickets-show-filter' data-status='closed' name='show-closed' value='closed' checked/></label>" );

Rows with status "Closed" which must be hide after checkbox click has class "closed" -

<tr data-status="closed" class="closed">
...
</tr>

So I'm trying to write click function on this checkbox this way...

var table = $('.tickets-table').DataTable();

$('.tickets-show-filter').click(function() {

    console.log('click filter');
    var checked = $(this).is(':checked');

    if (checked) {
        console.log('not checked');
        table.draw();
        
    } else {
        console.log('checked');
        table
            .rows( '.closed' )
            .remove()
            .draw();
    }

});

So I use .remove() to remove rows with class "closed" and it works well. And pagination also works fine.
But when I click on ckeckbox once more to show rows with status "closed" - they didn't show. And I now why, because they are completely removed from table data. So maybe ".remove" method is not correct to use for such functional, or there is some other function to get back all table rows data when checkbox is checked.
Please help me with it to find the right way to do this functional.

rf1234

I have a similar situation and I am posting my code below so that you can adapt it to your use case if you like it:

I have a data table that has three custom buttons. These buttons all search one column for different sub strings:
- All: Search for empty string (to make sure all rows show up)
- Test only: Search for "test version"
- Live only: Search for "live version"

Using the buttons option https://datatables.net/reference/option/buttons
This is quite easy to do:

buttons: [
    {   text: lang === "de" ? "Alles" : "All",
        action: function ( e, dt, node, config ) {
            dt.columns("#qaStatus").search("").draw();
        }
    },
    {   text: lang === "de" ? "Nur Test" : "Test only",
        action: function ( e, dt, node, config ) {
            dt.columns("#qaStatus").search("test version", false, false).draw();
        }
    },
    {   text: lang === "de" ? "Nur Live" : "Live only",
        action: function ( e, dt, node, config ) {
            dt.columns("#qaStatus").search("live version  ", false, false).draw();
        }
    }
]

I assigned the id "qaStatus" in my HTML.
https://datatables.net/reference/api/columns().search()

I am not using checkboxes in my buttons but that's a minor detail I guess.

This is what it looks like:
All (everything is shown and that is the default as well):

Test only:

Live only:

fisheriks

Hi. Thank you for a quick and great example.
I've also thought about the .search function but didn't try it to the end.

For now I made changes in my script according to your functional. It looks like this.

$('.tickets-show-filter').change(function() {
    if ($(this).is(':checked')) {
        console.log('not checked');
        table
            .search("")
            .draw(); 
    }
    else {
        console.log('checked');
        table
            .search("Reported")
            .draw();
    }
});

It works fine. But there are still few questions.

1. When I change checkbox and plugin filter the table I see search query in search form input. It is not so big problem but user don't need to see this after clicking checkbox. Maybe there is some ways to resolve this.

2. The main problem is with the numbers of the tickets statuses. For example if I have at least 3 statuses "Waiting for response", "Reported", "Closed" how I can write my search query on script to display all tickets except "Closed"? What text will be displayed in the search form input in this way?

Wait for your answers. Thanks. Regards.

rf1234

If you use "columns.search" instead of "search" as in my example above the global search field isn't filled. The user still will be able to do global search then.

kthorngren

The search() API and search input are tied together. One option is to use column().search(). This won't affect the search input.

Another option is to use a search plugin, like this:
http://live.datatables.net/wenaxuti/299/edit

The event handler gets the checkbox values and places them in an array. The plugin uses the array to compare with the values in the column. If the column value is not in the array the plugin returns false to hide the row. Otherwise if the array is empty or the value is in the array it returns true to display the row.

Kevin

rf1234

In addition to using "columns.search" instead of "search" you should consider using "columns.render" https://datatables.net/reference/option/columns.render

This way you can return different values depending on the type:
- If type is "filter" you can return a different string that includes the words "not closed" for example else you would return as is.
- This way you can search for "not closed" and solve your 2nd issue.

fisheriks

Hi, rf1234.
Thanks for the replies and examples.
Now I finished with "columns.search" and two Statuses with your variant.
It works well.

$('.tickets-show-filter').change(function() {
        if ($(this).is(':checked')) {
        console.log('not checked');
        table
            .columns(".status")
            .search("")
            .draw(); 
    }
    else {
        console.log('checked');
        table
            .columns(".status")
            .search("Reported")
            .draw();
    }
});

But with more than 2 statuses I still can't write correct script using "columns.render", as you said.
Maybe I misunderstand something in documentation. It will be very helpfull for me if you can create a short example only with using "columns.render" for 3 different values in one column.
Thank you very much.
I'm waiting for the reply. Regards.

fisheriks

Hi, Kevin.
Thank you too for the reply and example of search plugin.
I tried to edit it, but use two values of statuses in one input value doesn't work. Maybe I'm doing something wrong and problem is easy.
Can you show me in your example variant when one checkbox will filter two or more values, or explain this?
Thanks for any reply. Waiting for it.
Regards.

kthorngren

If you want to exclude all rows with Closed you can use a regex search with a not match. Like this:
http://live.datatables.net/gihufinu/1/edit

Kevin

rf1234

But with more than 2 statuses I still can't write correct script using "columns.render", as you said.
Maybe I misunderstand something in documentation. It will be very helpfull for me if you can create a short ex
ample only with using "columns.render" for 3 different values in one column.

Sorry, but I don't know how you would use columns.render. You are using data tables without a field list in Javascript. Hence I have no idea how to integrate columns.render witthout the field list. That is something you will need to figure out by yourself from the documentation. I can give you an example assuming you have a field list using columns.data and columns.render. You will need to adapt that.

var table = $('#yourDataTable').DataTable({
    ....
    columns: [
        {  data: "yourStatusField",
            render: function (data, type, row) {
                if ( type === "filter" ) {
                    if ( data != "Closed" ) {
                        return data + " not closed";
                    }
                }
                return data;
            }
        },
....

Based on that you can now search for "not closed". And find everything that is not closed. You might need to turn of smart search for that. But you can find that in my example above and in the docs.

This means you might need somehting like this

table.columns(".status").search("not closed", false, false).draw();

instead of

table.columns(".status").search("not closed").draw();

fisheriks

Thanks a lot for you both guys. In my last version I used search regex as on Kevin sample.

.search('^((?!Closed).)*$', true, false)

Full working script:

$('.tickets-show-filter').change(function() {
      if ($(this).is(':checked')) {
        console.log('not checked');
        table
          .columns(".status")
          .search("")
          .draw(); 
      }
      else {
        console.log('checked');
        table
          .columns(".status")
          .search('^((?!Closed).)*$', true, false)
          .draw();
      }
    });

It works great.

Sorry, but I don't know how you would use columns.render. You are using data tables without a field list in Javascript. Hence I have no idea how to integrate columns.render witthout the field list. That is something you will need to figure out by yourself from the documentation. I can give you an example assuming you have a field list using columns.data and columns.render. You will need to adapt that.

Especially thanks to rf1234 for your last example.
That's also important thing, because now I'll present my task only on frontend with ready html test table, but in future it will be get as data throught ajax. So it will be next time. For now those my questions are solved. If anything else I'll write you.

fisheriks

Thanks a lot for you both guys. In my last version I used search regex.

.search('^((?!Closed).)*$', true, false)

Full working script:

$('.tickets-show-filter').change(function() {
      if ($(this).is(':checked')) {
        console.log('not checked');
        table
          .columns(".status")
          .search("")
          .draw(); 
      }
      else {
        console.log('checked');
        table
          .columns(".status")
          .search('^((?!Closed).)*$', true, false)
          .draw();
      }
    });

It works great.

Sorry, but I don't know how you would use columns.render. You are using data tables without a field list in Javascript. Hence I have no idea how to integrate columns.render witthout the field list. That is something you will need to figure out by yourself from the documentation. I can give you an example assuming you have a field list using columns.data and columns.render. You will need to adapt that.

Especially thanks to rf1234 for your last example.
That's also important thing, because now I'll present my task only on frontend with ready html test table, but in future it will be get as data throught ajax. So it will be next time. For now those my questions are solved. If anything else I'll write you.

rf1234

Especially thanks to rf1234 for your last example.
That's also important thing, because now I'll present my task only on frontend with ready html test table, but in future it will be get as data throught ajax. So it will be next time. For now those my questions are solved. If anything else I'll write you.

You are welcome. I would consider using Editor with this right from the start. Saves you a lot of work and hassle on front and back end. And the license fee is very fair. I have been using Editor for a couple of years now and I wouldn't want to miss it.