Filter button does not search for my custom date picker on click.

imacoolguyful

I made a custom view where you can filter by department and or theme. Then you can choose start date and end date if you want. Then you click filter and you can download the filtered csv or excel. It is using a hidden datatable.

Institution code:

             <div class="form-group">
                  <label for="Institution">Institution</label>
                        <select class="form-control" id="Institution" name="Institution" data-column="0">
                            <option value="">All</option>
                            <!-- DEPARTMENTS -->
                        </select>
              </div>

Theme code:

               <div class="form-group">
                    <label for="dt_theme">Theme</label> 
                        <select class="form-control" id="dt_theme" name="dt_theme" data-column="2">
                            <option value="">All</option>
                            <!-- THEMES -->
                        </select>
                </div>

Date range filters:

<div class="form-group">
                    <label for="date">Date</label>
                    <input type="date" class="form-control" id="min" name="min">
                   <input type="date" class="form-control" id="max" name="max">
                </div>

I had a problem now where only theme and institution were being search for on filter click. This is because I only used "data-column=" for theme and institution. So this is the solution I came up with:

            var flag = false;
            var flag2 = false;
            $('#min', this).on('keyup change', function() {
                if (this.value != "" && $('#max').val() != "") {
                    flag = true;
                    table
                        .column(7)
                        .search(this.value + ":" + $('#max').val())
                        .draw();
                }
                if ($('#dt_theme').val() != "" && flag) {
                    table
                        .column(2)
                        .search($('#dt_theme').val())
                        .draw()
                }
                if ($('#Institution').val() != "" && flag) {
                    table
                        .column(0)
                        .search($('#Institution').val())
                        .draw()
                }
            });

            $('#max', this).on('keyup change', function() {
                if (this.value != "" && $('#min').val() != "") {
                    flag2 = true;
                    table
                        .column(7)
                        .search($('#min').val() + ":" + this.value)
                        .draw();
                }
                if ($('#dt_theme').val() != "" && flag2) {
                    table
                        .column(2)
                        .search($('#dt_theme').val())
                        .draw()
                }
                if ($('#Institution').val() != "" && flag2) {
                    table
                        .column(0)
                        .search($('#Institution').val())
                        .draw()
                }
            });

The problem with this solution is it filters for everything automatically after i set the min and max date range. If I click filter after it has already automatically filtered with date, theme, and institution. It defaults to filtering for the theme and institution and gets rid of the date range filter. I am wondering How I can make my filter button use the date range as well.

kthorngren

You will need to use a search plugin, instead of column().search(), for the date range search. You can see a running range search example here. You can find a date range plugin here.

If you need help implementing it please build a simple test case with example data to allow us to interactively help.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

imacoolguyful

@kthorngren Hey, the first link you sent goes to a 404 page, and the second link filters the table based on two dates in different columns. I am using two dates in the same column.

kthorngren

Fixed the first link.

The plugin uses these variables to define the columns:

        var iStartDateCol = 6;
        var iEndDateCol = 7;

If your start end end date are the same column then set them the same.

Also you will likely need to change the code used to parse the date values. Give it a try as is and debug the values of iFini and datofini to see if they work with your date format. If not make adjustments or maybe use the moment.js plugin.

Also I forgot about this thread that has a date range solution that works. Instead of defining the column in the plugin you define the date column using columns.type.

Kevin

imacoolguyful

$.fn.dataTableExt.afnFiltering.push(
                function(oSettings, aData, iDataIndex) {
                    var iFini = document.getElementById('min').value;
                    var iFfin = document.getElementById('max').value;
                    var dateCol = 7;


                    iFini = iFini.substring(0, 4) + iFini.substring(5, 7) + iFini.substring(8, 10);
                    iFfin = iFfin.substring(0, 4) + iFfin.substring(5, 7) + iFfin.substring(8, 10);
                    console.log(iFfin + " " + iFini);

                    var datofini = aData[dateCol].substring(0, 4) + aData[dateCol].substring(5, 7) + aData[dateCol].substring(8, 10);
                    var datoffin = aData[dateCol].substring(0, 4) + aData[dateCol].substring(5, 7) + aData[dateCol].substring(8, 10);
                   
                    console.log(datofini + " " + datoffin);

                    if (iFini === "" && iFfin === "") {
                        return true;
                    } else if (iFini <= datofini && iFfin === "") {
                        return true;
                    } else if (iFfin >= datoffin && iFini === "") {
                        return true;
                    } else if (iFini <= datofini && iFfin >= datoffin) {
                        return true;
                    }
                    return false;
                }
            );

I adjusted the code but it still does not do anything on filter press. My date range values are for example: 2020-12-31, i substring them so they show up as 20201231.
@kthorngren

kthorngren

The next steps will be to debug the if statements to see if they are working the way you want.

As I mentioned if you want help with this please build a simple test case case with a sample of your data so we can help.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

imacoolguyful

http://live.datatables.net/rujikepi/1/edit @kthorngren

kthorngren

This example works with your filter button:
http://live.datatables.net/tixumeju/1/edit

I changed from using the keyup event to the change event here:
http://live.datatables.net/gonuleni/1/edit

If you set the min value to 01/01/2011 and leave the max blank you should see all dates starting at 01/01/2011. Then change the max to 12/31/2011 and you should only see dates in the year 2011.

Kevin

imacoolguyful

It doesn't seem to work when I use the code from the first link. Is it because I have server sided processing or should that not affect it? @kthorngren

kthorngren

To combine all the inputs with the filter button you can do something like this:

        table
            .column(2)
            .search($('#dt_theme').val());

        table
            .column(0)
            .search($('#Institution').val());

        table.draw();  // Perform column searches and run search plugin.

Basically search column 2 and 0 with the input values but don't draw yet. Use draw() once at the end to apply all searches only once.

Kevin

kthorngren

I tried the first link again. Did you click the filter button?

Please provide the steps you are taking that is not working.

Kevin

imacoolguyful

http://live.datatables.net/vabakonu/1/edit this is what I did in my code. However, when I click filter. Nothing changes. Is it because I am using server side processing?
Works on the fiddle but not on my actual code. @kthorngren

kthorngren

However, when I click filter. Nothing changes. Is it because I am using server side processing?

The client side search plugin is not used with server side processing. All searching is performed by the server side script. Here are a couple options I can think of to handle the date range. It will depend on your server side script and how you can handle parsing and performing the date range search.

1. Combine the min and max values as one search string for the column search, for example:

        table
            .column(7)
            .search($('#min').val() + ":" + $('#max').val());

The server script will need to parse the search string for column 7 to build the search query.

1. Use the ajax.data option as a function to send the min and max date values as parameters to the server and use those parameters in the search query.

Option 2 seems like it would be easier but choosing which to use is based on your specific environment.

Kevin

imacoolguyful

http://live.datatables.net/wetusaza/1/edit That's what I was doing before but the problem is that I had it on keyup change so when I clicked the filter button it didn't apply. I tried putting it within the submit on click function but that didn't work.

 $('button:submit').on('click', function() {}

kthorngren

the submit on click function but that didn't work.

What exactly is not working?

Is the event not working or is the code within the event not working or is the server script not working?

You will need to debug where the failure is to determine the next steps. Can you post a link to your page so we can take a look?

Kevin