DataTables : using button - search in multiple columns using OR operator

Shivani Vyas

Hello, I am trying to make button called 'No Dates' on my page that can filter 3 columns from the table with result 'No Dates' text. I am displaying 'No Dates' text if there is null date. so using button I would like to filter table which has null dates in 3 columns - col 8, 9, 10 using OR condition. I am aware that dt.column(8,9,10).search('No Dates').draw(); will not work as it use AND operation.

I also tried using search plug-ins but its not working and also not giving any error. Here is what I tried.

// NO DATES BUTTON
            table.button().add(
                        null, {

                                text: 'No Dates',
                                action: function ( e, dt, node, config ) {

                                    $.fn.dataTable.ext.search.push(
                                            function( settings, searchData, index, rowData, counter)

                                            {

                                                var install = searchData[8];
                                                var pico = searchData[9];
                                                var sat = searchData[10];

                                                if (install === 'No Dates' || pico === 'No Dates' || sat === 'No Dates' ) 
                                                {
                                                    return true;
                                                }

                                                return false;
                                            });




                                }
                            }    
            );

I also tried following this link from web but no luck so far

https://stackoverflow.com/questions/62680861/datatables-search-in-multiple-columns-using-or-operator

I also have RESET Button which can remove all filters and display original table using below code. Not sure how to use
$.fn.dataTable.ext.search for resetting back to original form. Please advice what I am doin wrong here. Thank you.

// RESET BUTTON - To Display All Data 
        table.button().add(
                    null, {

                        text: 'Reset',
                        action: function ( e, dt, node, config ) {

                            table.columns().search( '' ).draw();  

                            // Tried below line from the web solution to reset when I tried using myfilter function
                            //$.fn.dataTable.ext.search.splice($.fn.dataTable.ext.search.indexOf(myfilter, 1));  


                        }
                    }
        );

kthorngren

Its hard to say with just the code snippets. Please build a simple example that represents the data you have so we can experiment with the code.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

Shivani Vyas

@kthorngren Hi Kevin, it is little complex to build example. But here is the url of my page. Let me know if you able to open it and see the code.

https://assettrack.cx/construction/systemsProgress1.php

kthorngren

I get the following error:

Request URL: https://assettrack.cx/construction/systemsProgress1.php
Request Method: GET
Status Code: 500 

We don't need a full example. Build a test case that has a sample of your data. Use Javascript loaded data or if you table is dom sourced grab a few rows. You can start with this template:
http://live.datatables.net/

Here is an example I created a long time ago that might give you some ideas.
http://live.datatables.net/mucevape/1/edit

Kevin

Shivani Vyas

@kthorngren I tried adding manually in this example. Not sure why buttons are not displayed.. but when I click on No Dates button.. it should filter column 2 and 3 with result 'No Dates' using OR condition. And when I click Reset button it should remove the filter.

live.datatables.net/nujomogi/1/edit

kthorngren

After you push or pop the search plugin you will need to use draw() to have Datatables perform the search and execute the plugin if its installed. I used the dt parameter of the buttons.buttons.action function to get the Datatables API instance.
http://live.datatables.net/rukuwaxa/1/edit

Kevin

Shivani Vyas

@kthorngren Thanks Kevin its working correctly in the example but its not working in my code as my data is coming from ajax and its based on some calculation. Here the code of those 3 columns and how it looks like.

columns: [
{ 
            data: "INSTALL_F",
            className: 'dt-nowrap', 
            render: function ( data, type, row) {


                //INSTALL_BL has NULL values
                var install_BL = "";
                if(row.INSTALL_BL != null){
                    install_BL = moment(row.INSTALL_BL).format('DD MMM YYYY');
                }
                else {
                    install_BL = "No Dates";
                }

                //INSTALL_F has NULL values
                var install_F = "";
                if(data != null){
                    install_F = moment(data).format('DD MMM YYYY');
                }
                else {
                    install_F = "No Dates";
                }

                // install_old
                var installDate = '';
                var oldInstallDate = '';

                if(row.install_old != null) {

                    oldInstallDate = moment(row.install_old).format('DD MMM YYYY');


                     // For Yellow Highlight for dates changed within 7 days
                    if((diffDays2 < 7) && (install_F != oldInstallDate) ) 
                    {
                        installDate = ' style=background-color:#fff281'; //yellow highlight 

                    } 
                }

                var installedProgress = "<div class='mt-0'>Planned Date: " + install_BL + "<br/>" +
                    "<div id='hoverText'" + installDate + ">" +
                    "<span title='Previous Date: " + oldInstallDate + "'>Forecast Date: " + install_F + "</span>" + 
                    "</div>" + 
                    "Remaining Assets: " + notInstalled + " (" + notInstallPC + "%)<br/>" +
                    "Installed Assets: " + installed + " (" + installPC + "%)<br/>" +
                    "</div>" +
                    "<div class='mb-0'>" +
                        "<div class='progress'>" + 
                            "<div class='progress-bar bg-success' role='progressbar' style='width: "+ installPC +"%;' aria-valuenow='"+ installed +"' aria-valuemin='0' aria-valuemax='"+ row.total +"'>"+ installPC +"%</div>" + 
                        "</div>" + 
                    "</div>";  }

        },
        { 
            data: "PICO_F",
            className: 'dt-nowrap', 
            render: function ( data, type, row) {


                //PICO_BL has NULL values
                var pico_BL = "";
                if(row.PICO_BL != null){
                    pico_BL = moment(row.PICO_BL).format('DD MMM YYYY');
                }
                else {
                    pico_BL = "No Dates";
                }

                //PICO_F has NULL values
                var pico_F = "";
                if(data != null){
                    pico_F = moment(data).format('DD MMM YYYY');
                }
                else {
                    pico_F = "No Dates";
                }

                // pico_old values
                var picoDate = '';
                var oldPicoDate = '';

                if(row.pico_old != null) {

                    oldPicoDate = moment(row.pico_old).format('DD MMM YYYY');

                     // For Yellow Highlight for dates changed within 7 days
                    if((diffDays2 < 7) && (pico_F != oldPicoDate) ) 
                    {
                        picoDate = ' style=background-color:#fff281'; //yellow highlight

                    } 
                }


                var picoProgress1 =
                        "<div>Planned Date: " + pico_BL + "<br/>" +
                            "<div id='hoverText'" + picoDate + ">" + 
                                "<span title='Previous Date: " + oldPicoDate + "'>Forecast Date: " + pico_F + "</span>" +
                            "</div>" +
                            "Required: " + picoReqd +
                            picoSubLine +
                            picoRemLine +
                            picoIPLine +
                            picoRetLine +
                            picoIRLine +
                            picoReportLine +
                        "</div>" +
                        "<div class='progress px-0'>" +
                            "<div class='progress-bar bg-success' role='progressbar' style='width: " + picoReportPC + "%;' aria-valuenow='" + picoReport + "' aria-valuemin='0' aria-valuemax='" + picoReqd + "'>" + picoReportPC + "%</div>" +
                            "<div class='progress-bar bg-success bg-opacity-50' role='progressbar' style='width: " + picoIRPC + "%;' aria-valuenow='" + picoIR_math + "' aria-valuemin='0' aria-valuemax='" + picoReqd + "'>" + picoIRPC +"%</div>" +
                            "<div class='progress-bar bg-danger' role='progressbar' style='width: " + picoRetPC + "%;' aria-valuenow='" + picoRet + "' aria-valuemin='0' aria-valuemax='" + picoReqd + "'>" + picoRetPC +"%</div>" +
                            "<div class='progress-bar bg-primary' role='progressbar' style='width: " + picoSubPC + "%;' aria-valuenow='" + picoSub_math + "' aria-valuemin='0' aria-valuemax='" + picoReqd + "'>" + picoSubPC +"%</div>" +
                            "<div class='progress-bar bg-info' role='progressbar' style='width: " + picoIPPC + "%;' aria-valuenow='" + picoIP_math + "' aria-valuemin='0' aria-valuemax='" + picoReqd + "'>" + picoIPPC + "%</div>" +
                        "</div>"; }


        },
        { 
            data: "SAT_F",
            className: 'dt-nowrap', 
            render: function ( data, type, row) {

                //SAT_BL has NULL values
                var sat_BL = "";
                if(row.SAT_BL != null){
                    sat_BL = moment(row.SAT_BL).format('DD MMM YYYY');
                }
                else {
                    sat_BL = "No Dates";
                }

                //SAT_F has NULL values
                var sat_F = "";
                if(data != null){
                    sat_F = moment(data).format('DD MMM YYYY');
                }
                else {
                    sat_F = "No Dates";
                }

                //sat_old values
                var oldSatDate = '';
                var satDate = '';

                if(row.sat_old != null) {

                    oldSatDate = moment(row.sat_old).format('DD MMM YYYY');

                    //For Yellow Highlight for dates changed within 7 days
                    if((diffDays2 < 7) && (sat_F != oldSatDate) ) 
                    {
                        satDate = ' style=background-color:#fff281'; //yellow highlight

                    } 
                }

                var satProgress =
                    "<div>Planned Date: "+ sat_BL + "<br/>" + 
                        "<div id='hoverText'" + satDate + ">" + 
                            "<span title='Previous Date: "+ oldSatDate +"'>Forecast Date: "+ sat_F +"</span>" + 
                        "</div>" + 
                    "</div>Required: "+ satReqd + "<br/>" + 
                    satRemLine +
                    satIPLine +
                    satFailedLine +
                    satSiteLine +
                    satReportLine +
                    "<div class='progress'>" + 
                        "<div class='progress-bar bg-success' role='progressbar' style='width: "+ satReportPC +"%;' aria-valuenow='"+ satReport +"' aria-valuemin='0' aria-valuemax='"+ satReqd +"'>"+ satReportPC +"%</div>" + 
                        "<div class='progress-bar bg-success bg-opacity-50' role='progressbar' style='width: "+ satSiteCompletePC +"%;' aria-valuenow='"+ satSiteComplete_math +"' aria-valuemin='0' aria-valuemax='"+ satSiteComplete_math +"'>"+ satSiteCompletePC +"%</div>" + 
                        "<div class='progress-bar bg-danger' role='progressbar' style='width: "+ satFailedPC +"%;' aria-valuenow='"+ satFailed +"' aria-valuemin='0' aria-valuemax='"+ satFailed +"'>"+ satFailedPC +"%</div>" + 
                        "<div class='progress-bar bg-primary' role='progressbar' style='width: "+ satIPPC +"%;' aria-valuenow='"+ satIP_math +"' aria-valuemin='0' aria-valuemax='"+ satIP_math +"'>"+ satIPPC +"%</div>" 
                    "</div>";


              }  
        }, 
],

I am sorry for the long code but this 3 column should filter where I displayed 'No Dates' if col1 - var install_BL or install_F or col2- pico_BL or pico_F or col3- sat_BL or sat_F has null dates.

kthorngren

I'm not quite sure how that is working because columns.render expects a return statement to be used for the data to display. However you can use Orthogonal data to set the values used for the search function. For example, return the "No Date" when appropriate or return the appropriate data you want to search for.

If you still need help then please update the test case to show the problem with your columns.render code or something close to it.

Kevin

Shivani Vyas

@kthorngren Sorry for confusion. Just because code is too long the comment is gone for approval. so please check this link , I have added my code in comments in javascript tab.

live.datatables.net/qikowope/1/edit

kthorngren

Please provide the steps to replicate the issue in the test case.

Kevin

Shivani Vyas

@kthorngren Thank you Kevin for your help. I used different approach for my button. Instead of searching using $.fn.dataTable.ext.search.push(), I created new hidden column in which using render function and based on OR condition I assigned number for those 3 columns where I want to apply search. And than based on this new hidden column I filter my table. so if my condition for searching in 3 columns fulfill, this new hidden col will return '1' else '0'. Its working perfectly fine. Here is the code if its helpful for others.

 // For No Dates Button - Created null column and apply OR condition in render fun.
// this is col 36 in my table
        {
            data: null,  
            className: 'dt-nowrap',
            visible: false,
            render: function (data, type, row) {


                    var install_BL = row.INSTALL_BL;
                    var install_F = row.INSTALL_F;
                    var pico_BL = row.PICO_BL;
                    var pico_F = row.PICO_F;
                    var sat_BL = row.SAT_BL;
                    var sat_F = row.SAT_F;

                    var picoReqd = parseInt(row.picoReqd);
                    var satReqd = parseInt(row.satReqd);

                    if( (install_BL == null || install_F == null) || (picoReqd > 0 && pico_BL == null || picoReqd > 0 && pico_F == null) || (satReqd > 0 && sat_BL == null || satReqd > 0 && sat_F == null)) 
                    {
                            return 1;
                    }

                    else
                    {
                        return 0;
                    }

                }

        },


// here is my button which will search for value '1' in col 36 which will filter table with the result I want.

// NO DATES BUTTON
        table.button().add(
                    null, {

                            text: 'No Dates',
                            action: function ( e, dt, node, config ) {


                                dt.column(36).search(1).draw();


                            }

                        }    
        );

kthorngren

Sounds like a good approach

Kevin