Multiple date range filters using jQuery DateRangePicker and jQuery Datatable

supkat

What I would like to do is create multiple custom filters of different types on a datatable.

I know there are plugins that do such things such as YADF and Column Filter. However, the limitation I have with using them is that they require the column definitions to be part of the datatable options initialization. I am using the MVC C# framework and I have over 50 list/Index views that generate a datatable. Other Detail views contain partial views of the datatables. I do not what to repeat the datatable javascript code on all of these views. I rather have it defined in one place in an external js file linked to the master layout page. The benefit of this being that if I have to make a change, all files will received the change.

With that said, as can be seen in the jsFiddle, I have successfully been able to create the input boxes per column. Some are text searches, others are date ranges. I have gotten the jQuery daterangepicker to work for those input boxes that have the class'date-filter'. If you click on Show/Hide columns and choose 'Date Created' or 'Date Updated' you will see the date picker.

I have created a custom filter that pushes the date criteria chosen. ($.fn.dataTableExt.afnFiltering.push(DateFilterFunction)). However, the filter does not work as expected. I want the filters to be cumulative. If you select a date range for one column (e.g. Date Updated) and then select for another column (e.g. DateCreated) the filter would seem to work. However, if you change the second filter, the other filter will change. Also, when clearing a particular filter on a date range column, all the existing date range column filters are removed. The behavior is erratic.

Can someone please look at the code and tell me what I am doing wrong.

JSFIDDLE: https://jsfiddle.net/supkat/a9pLk0ud/

Excerpt Javascript:

    var startDate;
    var endDate;



   var DateFilterFunction = (function (settings, data, iDataIndex) {

       var filterstart = startDate;
       var filterend = endDate; 
        var iStartDateCol = dataColumnIndex;
        var iEndDateCol = dataColumnIndex;

        var tabledatestart = data[iStartDateCol] !== "" ? moment(data[iStartDateCol], "DD-MMM-YYYY") : data[iStartDateCol];
        var tabledateend = data[iEndDateCol] !== "" ? moment(data[iEndDateCol], "DD-MMM-YYYY") : data[iEndDateCol];



        if (filterstart === "" && filterend === "") {
            return true;
        }

        else if ((moment(filterstart, "DD-MMM-YYYY").isSame(tabledatestart) || moment(filterstart, "DD-MMM-YYYY").isBefore(tabledatestart)) && filterend === "") {
            return true;
        }
        else if ((moment(filterstart, "DD-MMM-YYYY").isSame(tabledatestart) || moment(filterstart, "DD-MMM-YYYY").isAfter(tabledatestart)) && filterstart === "") {
            return true;
        }
        else if ((moment(filterstart, "DD-MMM-YYYY").isSame(tabledatestart) || moment(filterstart, "DD-MMM-YYYY").isBefore(tabledatestart)) && (moment(filterend, "DD-MMM-YYYY").isSame(tabledateend) || moment(filterend, "DD-MMM-YYYY").isAfter(tabledateend))) {
            return true;
        }

        return false;


    });




   $(".daterange", this).on('apply.daterangepicker', function (ev, picker) {
       ev.preventDefault();
       $(this).val(picker.startDate.format('DD-MMM-YYYY') + ' to ' + picker.endDate.format('DD-MMM-YYYY'));
       startDate = picker.startDate.format('DD-MMM-YYYY');
       endDate = picker.endDate.format('DD-MMM-YYYY');
       $.fn.dataTableExt.afnFiltering.push(DateFilterFunction);

       table.draw();

   });

   $(".daterange", this).on('cancel.daterangepicker', function (ev, picker) {
       ev.preventDefault();
       $(this).val('');
       startDate = '';
       endDate = '';
       $.fn.dataTableExt.afnFiltering.push(DateFilterFunction);

       table.draw();


   });

Tom (DataTables)

Datatables filtering is cumulative, ie each filter is applied on top of the other consecutively.

After having a look at the jsfiddle there's a couple of things.

1. You are adding the custom filtering function to the array every time the daterange changes rather than updating it. Because you have 2 filters there should only every be two filters added to the filtering array.

2. It looks like startDate and endDate are shared between the two differing filtering functions so you'd need a different startDate/endDate for each column.

Thanks

Tom

supkat

Thank you Tom for your response. I shall test the code again with your suggested changes.

Before your response though, I came up with the following solution that seems to have worked also:

https://jsfiddle.net/a9pLk0ud/2/