How to filter out data in a DataTable

I need help with a DataTable.  I need to filter out rows that meet a certain criteria.  I am using grouped tables like this:

$('table.groupedtable').DataTable({
            "aaSorting": [],
            "pageLength": 1000,
            "columnDefs": [
                { "visible": false, "targets": 1 }
            ],
            "drawCallback": function (settings) {
                var api = this.api();
                var rows = api.rows({ page: 'current' }).nodes();
                var last = null;
                api.column(1, { page: 'current' }).data().each(function (group, i) {
                    if (last !== group) {
                        $(rows).eq(i).before(
                            '<tr class="group"><td colspan="9">' + group + '</td></tr>'
                        );
                        last = group;
                    }
                })
            }
        });

Open in new window


I have searched and found this code:

var filteredData = table
                    .rows()
                    .indexes()
                    .filter(function (value, index) {
                        return table.row(value).data()[9] == "Complete";
                    });
                table.rows(filteredData)
                    .remove()
                    .draw();

Open in new window

I'm not sure where to place this within the grouped table function.  I tried adding it after the draw Callback Function and I get table not defined errors.

Thanks for any help!
Crystal RouseTask LeadAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

leakim971PluritechnicianCommented:
as you can see here, you just need to put it after your first code : https://datatables.net/reference/api/filter()
Kelvin McDanielSr. DeveloperCommented:
The second snippet has issues because there isn't a variable named table in the first snippet. Try the following update to your existing code -- the update is in bold below.

$('table.groupedtable').DataTable({
            "aaSorting": [],
            "pageLength": 1000,
            "columnDefs": [
                { "visible": false, "targets": 1 }
            ],
            "drawCallback": function (settings) {
                var api = this.api();
                var rows = api.rows({ page: 'current' }).nodes();
                var last = null;
                api.column(1, { page: 'current' }).data().each(function (group, i) {
                    if (last !== group) {
                        $(rows).eq(i).before(
                            '<tr class="group"><td colspan="9">' + group + '</td></tr>'
                        );
                        last = group;
                    }
                }); // <-- make sure you include this semicolon.
    var filteredData = api
                    .rows()
                    .indexes()
                    .filter(function (value, index) {
                        return table.row(value).data()[9] == "Complete";
                    });

    api.rows(filteredData)
                    .remove()
                    .draw();

            }
        });
Crystal RouseTask LeadAuthor Commented:
Thank you so much!  I was trying to figure out where to put the code.  I am still getting an error that table is not defined for:

return table.row(value).data()[9] == "Complete";
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Kelvin McDanielSr. DeveloperCommented:
Same thing there; change
table.rows

Open in new window

to  
api.rows

Open in new window

and it *should* get rid of the error.
Crystal RouseTask LeadAuthor Commented:
I've tried that and even added a breakpoint to watch it loop thru the records. MY IE webpage keeps crashing now with a long running script. I'm hoping I have misplaced a semi-colon, bracket or something and that its not the time it takes to loop thru all my records:

 $('table.filteredgroupedtable').DataTable({
            "aaSorting": [],
            "pageLength": 1000,
            "columnDefs": [
                { "visible": false, "targets": 1 }
            ],
            "drawCallback": function (settings) {
                var api = this.api();
                var rows = api.rows({ page: 'current' }).nodes();
                var last = null;
                api.column(1, { page: 'current' }).data().each(function (group, i) {
                    if (last !== group) {
                        $(rows).eq(i).before(
                            '<tr class="group"><td colspan="9">' + group + '</td></tr>'
                        );
                        last = group;
                    }
             });
                var filteredData = api
                    .rows()
                    .indexes()
                    .filter(function (value, index) {
                        return api.row(value).data()[8] == "Complete";
                    });

                api.rows(filteredData)
                    .remove()
                    .draw();
            }
        });
Kelvin McDanielSr. DeveloperCommented:
A crash like that is usually indicative of either misplaced scope or "doing too much across the board". I need to get to a computer with a proper code editor so that I can create a working example based on your provided code. I should have one later on today... unless another Expert bears me to it. ;)
Crystal RouseTask LeadAuthor Commented:
Thank you so much!  I really appreciate your help!
Kelvin McDanielSr. DeveloperCommented:
After taking a look at this in JSFiddle.net and reviewing some of the api documentation, I think I see the problem.  The second snippet tells the api to filter out the data and then redraw the table. However, this is inside the drawCallBack... which more than likely results in a never-ending regressive loop. So, it looks like you should be able to resolve this by moving the filtering action out of the drawCallBack event. Something like the following (which is based on the most recent code you posted above):

$('table.filteredgroupedtable').DataTable({
  "aaSorting": [],
  "pageLength": 1000,
  "columnDefs": [
    { "visible": false, "targets": 1 }
  ],
  "drawCallback": function (settings) {
    var api = this.api();
    var rows = api.rows({ page: 'current' }).nodes();
    var last = null;
    api.column(1, { page: 'current' }).data().each(function (group, i) {
      if (last !== group) {
        $(rows).eq(i).before(
          '<tr class="group"><td colspan="9">' + group + '</td></tr>'
        );
        last = group;
      }
    });
  }
});

$('table.filteredgroupedtable').on('preDraw', function(){
	var api = this.api();  
  	var filteredData = api
  	    .rows()
  	    .indexes()
  	    .filter(function (value, index) {
  	  	return api.row(value).data()[8] == "Complete";
  	    });

    api.rows(filteredData).remove(); // You shouldn't need the ".draw()" call here because this filters the bound data before the each draw() happens.
});

Open in new window


Please note that I don't have access to the data you're binding to this so it's difficult to test these suggestions effectively.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Crystal RouseTask LeadAuthor Commented:
Thanks so much for helping me.  I updated my code and now the page doesn't crash but doesn't filter out the data either.  I added a breakpoint and can see where column 9 is returning data that is "Complete".  The line:   var api = this.api(); shows this.api() as undefined.
Kelvin McDanielSr. DeveloperCommented:
No problem. :)

Is it at all possible that I can watch "over your shoulder" while you debug? That will short-circuit all of this. Please message me directly if that's possible.
Crystal RouseTask LeadAuthor Commented:
No, I'm sorry.  I can't do that - but I do appreciate your help!  I really wanted to solve this today but I'll ask one of our developers to help me in the morning.  You've helped me get a long way with this working "blind".
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
JavaScript

From novice to tech pro — start learning today.