Filter dates by From/To date-range

[qlj]

Hi Oli,

I've been watching table-enhancement JS-libraries for a while now, having used a number of them for a while. What I've discovered is that very few of the open-source ones support column-header-filtering. This for me is an important factor, as I believe tables are more powerful and usable with column-filtering. I've recently discovered Tabulator and have found it to be easy to implement and a natural fit.

There is one column-header-filtering feature that I believe will add great power to Tabulator, namely date-range filtering. Currently, Tabulator approaches date-filtering in the same way as it does for all strings, where dates are filtered as though they were strings on the string fragment supplied in the filter-input. However, date-ranges are the more common way of narrowing down a date-dependent set of data.

I'm aware you could recommend that the developer provides a table-wide date range filter instead: I believe this would short-change the developer and user, as I've seen this ability provided in commercial libraries over ten years ago, such as Active Widgets; in addition, any open source table enhancement library that satisfactorily cracks this (and other similar filtering-behaviour) feature will likely become a market leader and enable developers to move away from commercial to open source libraries.

Do you have any plans to introduce column-header date-range (From/To) filters? If so, when will that be? Do you know of any current extensions or workarounds to provide the equivalent functionality?

Many thanks for your attention, and I look forward to your recommendations.

[tolgaulas]

Amen to that!

Do you have any plans to introduce column-header date-range (From/To) filters? If so, when will that be? Do you know of any current extensions or workarounds to provide the equivalent functionality?

[olifolkerd]

Hey @qlj and @tolgaulas

You will be happy to hear you can easily achieve this with a custom header filter and custom header filter function.

Below is a simple example to help you on your way:

//custom header filter
var dateFilterEditor = function(cell, onRendered, success, cancel, editorParams){

    var container = $("<span></span>")
    //create and style input
    var start = $("<input type='date' placeholder='Start'/>");
    var end = $("<input type='date' placeholder='End'/>");

    container.append(start).append(end);

    var inputs = $("input", container);

    inputs.css({
        "padding":"4px",
        "width":"50%",
        "box-sizing":"border-box",
    })
    .val(cell.getValue());

    function buildDateString(){
        return {
            start:start.val(),
            end:end.val(),
        };
    }

    //submit new value on blur
    inputs.on("change blur", function(e){
        success(buildDateString());
    });

    //submit new value on enter
    inputs.on("keydown", function(e){
        if(e.keyCode == 13){
            success(buildDateString());
        }

        if(e.keyCode == 27){
            cancel();
        }
    });

    return container;
}

//custom filter function
function dateFilterFunction(headerValue, rowValue, rowData, filterParams){
    //headerValue - the value of the header filter element
    //rowValue - the value of the column in this row
    //rowData - the data for the row being filtered
    //filterParams - params object passed to the headerFilterFuncParams property

    var format = filterParams.format || "DD/MM/YYYY";
    var start = moment(headerValue.start);
    var end = moment(headerValue.end);
    var value = moment(rowValue, format)
    if(rowValue){
        if(start.isValid()){
            if(end.isValid()){
                return value >= start && value <= end;
            }else{
                return value >= start;
            }
        }else{
            if(end.isValid()){
                return value <= end;
            }
        }
    }

    return false; //must return a boolean, true if it passes the filter.
}

//column definition for column you want to filter
{title:"Date Of Birth", field:"dob", align:"center", sorter:"date",  headerFilter:dateFilterEditor, headerFilterFunc:dateFilterFunction},

Let me know how you get on.

Cheers

Oli :)

[mikecologne]

Hey @qlj and @tolgaulas

You will be happy to hear you can easily achieve this with a custom header filter and custom header filter function.

Below is a simple example to help you on your way:

//custom header filter
var dateFilterEditor = function(cell, onRendered, success, cancel, editorParams){

  var container = $("<span></span>")
  //create and style input
  var start = $("<input type='date' placeholder='Start'/>");
  var end = $("<input type='date' placeholder='End'/>");

  container.append(start).append(end);

  var inputs = $("input", container);

  inputs.css({
      "padding":"4px",
      "width":"50%",
      "box-sizing":"border-box",
  })
  .val(cell.getValue());

  function buildDateString(){
      return {
          start:start.val(),
          end:end.val(),
      };
  }

  //submit new value on blur
  inputs.on("change blur", function(e){
      success(buildDateString());
  });

  //submit new value on enter
  inputs.on("keydown", function(e){
      if(e.keyCode == 13){
          success(buildDateString());
      }

      if(e.keyCode == 27){
          cancel();
      }
  });

  return container;
}

//custom filter function
function dateFilterFunction(headerValue, rowValue, rowData, filterParams){
    //headerValue - the value of the header filter element
    //rowValue - the value of the column in this row
    //rowData - the data for the row being filtered
    //filterParams - params object passed to the headerFilterFuncParams property

      var format = filterParams.format || "DD/MM/YYYY";
      var start = moment(headerValue.start);
      var end = moment(headerValue.end);
      var value = moment(rowValue, format)
      if(rowValue){
          if(start.isValid()){
              if(end.isValid()){
                  return value >= start && value <= end;
              }else{
                  return value >= start;
              }
          }else{
              if(end.isValid()){
                  return value <= end;
              }
          }
      }

    return false; //must return a boolean, true if it passes the filter.
}

//column definition for column you want to filter
{title:"Date Of Birth", field:"dob", align:"center", sorter:"date",  headerFilter:dateFilterEditor, headerFilterFunc:dateFilterFunction},

Let me know how you get on.

Cheers

Oli :)

Hi Oli,

i've tried to use this code, but i get an error: "Cannot add filter to MYDATEFIELD column, editor should return an instance of Node, the editor returned: w.fn.init [span]".

Could you tell me what i'm doing wrong?

Thx!

[olifolkerd]

Hey @mikecologne

That is because the example is for Tabulator 3.x which used jQuery, as version 4.x no longer uses jQuery, it cannot handle the jQuery object that is returned from the dateFilterEditor function.

The good news is this is an easy fix, you just need to replace the existing return line:

return container;

with:

return container[0];

This will return the DOM node rather than the jQuery wrapper.

I hope that helps,

Cheers

Oli :)

[mikecologne]

Hey @mikecologne

That is because the example is for Tabulator 3.x which used jQuery, as version 4.x no longer uses jQuery, it cannot handle the jQuery object that is returned from the dateFilterEditor function.

The good news is this is an easy fix, you just need to replace the existing return line:

return container;

with:

return container[0];

This will return the DOM node rather than the jQuery wrapper.

I hope that helps,

Cheers

Oli :)

Hi Oli,

That does the trick 👍 . Great piece of software btw! Thx a lot for helping me.

Kind regards, Mike

[Projo94]

Hello Oli,

I have added these two functions, and also added their filter definitions into element I want to filter. But nothing hasn't changed. Even filter fields for date hasn't been displayed. Also tried to debug code using alert, but it seems that functions aren't called. What could be the problem?

[jessp01]

Hi all,

The above examples certainly helped me on my way. Thought I'd share mine. This uses jquery-ui-timepicker-addon.js Great job on this project, Oli. Saves loads of time and effort:)

<!-- Bootstrap Date-Picker Plugin -->
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.4.1/js/bootstrap-datepicker.min.js"></script>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.4.1/css/bootstrap-datepicker3.css"/>
<script type="text/javascript" src="//code.jquery.com/jquery-1.11.1.min.js"></script>
<script type="text/javascript" src="//code.jquery.com/ui/1.11.0/jquery-ui.min.js"></script>
<script type="text/javascript" src="//trentrichardson.com/examples/timepicker/jquery-ui-timepicker-addon.js"></script>
<script type="text/javascript" src="//trentrichardson.com/examples/timepicker/i18n/jquery-ui-timepicker-addon-i18n.min.js"></script>
<script type="text/javascript" src="//trentrichardson.com/examples/timepicker/jquery-ui-sliderAccess.js"></script>
<script type="text/javascript" src="//momentjs.com/downloads/moment-with-locales.js"></script>

<link rel="stylesheet" media="all" type="text/css" href="//code.jquery.com/ui/1.11.0/themes/smoothness/jquery-ui.css" />
<link rel="stylesheet" media="all" type="text/css" href="//trentrichardson.com/examples/timepicker/jquery-ui-timepicker-addon.css" />
<script>
var dateEditor = function(cell, onRendered, success, cancel, editorParams){
    var cellValue = cell.getValue(),
    input = document.createElement("input");

    input.setAttribute("type", "text");

    input.style.padding = "4px";
    input.style.width = "100%";
    input.style.boxSizing = "border-box";

    input.value = typeof cellValue !== "undefined" ? cellValue : "";

    onRendered(function(){
        input.style.height = "100%";
        $(input).datetimepicker({
                timeFormat: 'H:mm:ss z',
                dateFormat:"mm/dd/yy",
                timezoneList: [
                        { value: 0, label: 'GMT' },
                        { value: +60, label: 'CET' },
                        { value: -300, label: 'EST' },
                       { value: +180, label: 'Israel'},
                ],
            onClose: onChange
        }); //turn input into datepicker
        input.focus();
    });

    function onChange(e){
        if(((cellValue === null || typeof cellValue === "undefined") && input.value !== "") || input.value != cellValue){
            success(input.value);
        }else{
            cancel();
        }
    }

    return input;
}
</script>

Then, at the column definition:

                {title:"Start time", field:"start_time", editor:dateEditor, align:"left",formatter:"datetime", formatterParams:{
                   inputFormat:"MM/DD/YYYY H:m:ss ZZ",
                   outputFormat:"H:mm:ss",
                   invalidPlaceholder:"(invalid date)",
                    sorterParams:"outputFormat",
                    },cellClick:function(e, cell){
                }}

In the hope that it helps somebody.