How to define a Kendo grid Column filter between two dates?

Question

In our application we want the filter on a date column to prompt the user for a start date and an end date, with the filter returning rows where the field in question falls between (or on) those two dates.

Initial Approach

Our initial approach was to restrict date types to use gte and lte operators, and add the "extra : true" filterable option on the column. This came close, but presented the following problems: A) Each date input could use either the gte (Start) or lte (End) operator, providing undesired flexibility and the option for the user to create a filter that would never return results, and B) Presented a logical comparison (And / Or) that we don't want.

Better Approach

This question has an answer by Matthew Erwin that gets us very close: it allows us to completely re-style the filter entirely, so we can present simply a Start Date input and an End date input. However, what I can't get working is associating the right filter operation with the right input (gte for the Start date, lte for the End date). My custom filter is as follows:

    $scope.dateFilter = {
    extra: true,
    operators: {},
    ui: function (element) {
        var parent = element.parent();
        while (parent.children().length > 1)
            $(parent.children()[0]).remove();

        parent.prepend(
            "Start Date:<br/><span class=\"k-widget k-datepicker k-header\">" +
            "<span class=\"k-picker-wrap k-state-default\">" +
            "<input data-bind=\"value: filters[0].value\" class=\"k-input\" type=\"text\" data-role=\"datepicker\"" +
            " style=\"width: 100%\" role=\"textbox\" aria-haspopup=\"true\" aria-expanded=\"false\" aria-disabled=\"false\" " +
            " aria-readonly=\"false\" aria-label=\"Choose a date\">" +
            "<span unselectable=\"on\" class=\"k-select\" role=\"button\">" +
            "<span unselectable=\"on\" class=\"k-icon k-i-calendar\">select</span></span></span></span>" +

            "<br/>End Date:<br/>" +
            "<span class=\"k-widget k-datepicker k-header\"><span class=\"k-picker-wrap k-state-default\">" +
            "<input data-bind=\"value: filters[1].value\" class=\"k-input\" type=\"text\" data-role=\"datepicker\"" +
            " style=\"width: 100%\" role=\"textbox\" aria-haspopup=\"true\" aria-expanded=\"false\" " +
            " aria-disabled=\"false\" aria-readonly=\"false\" aria-label=\"Choose a date\">" +
            "<span unselectable=\"on\" class=\"k-select\" role=\"button\">" +
            "<span unselectable=\"on\" class=\"k-icon k-i-calendar\">select</span></span></span></span>"
        );
    }
};

With this approach, the Odata filter option is generated for each of the dates, however it uses the eq Equal To operator, so no values are ever returned. We aren't building filters specifically on the data source.

Is there a simple way I can associate each of those date inputs with a specific filter operator? Is there a better way to approach this subject? It seems like filtering dates based on a Start - End range would be commonly desired.

Other Details

We are using AngularJS, and WebAPI with Odata.

Answer 1

After working with Telerik, I came to an answer. The thread that I opened can be found here, but I'll also summarize in this answer.

The ultimate solution was to:

• Use the "Messages" option of the column "filterable" option to customize the filter display message.
• Use the "Extra" option of the column "filterable" option to get the extra Date selector in the filter menu.
• Configure the "Operators" option in the grid filterable option to set what operators can be used for dates (gte, lte) and what text is displayed for each (Begin Date, End Date).
• Use the filterMenuInit event to configure the filter controls.

End Result

Column Filterable

The following filterable options were used:

filterable: { "extra": "true", "messages": { "info": "Show items between dates:" }}

Extra gives us the second date selector, and the "info" message customizes the text displayed at the top of the filter menu.

Grid Filterable

I used the "operators" option in the grid-level "filterable" option to make date filters only provide the gte and lte operators, and to customize the text for those operators. This is what the operators configuration object wound up looking like:

"date": {
                "gte": "Begin Date",
                "lte": "End Date"
            }

Because we want this to apply for all dates, we put that in a factory and reuse it in each angular controller / view.

filterMenuInit Event

By providing a handler for the filterMenuInit event, you can access and configure the individual controls in the filter menu as it is created. The handler function that I created looks like this:

function (e) {
            if (e.sender.dataSource.options.schema.model.fields[e.field].type == "date") {
                var beginOperator = e.container.find("[data-role=dropdownlist]:eq(0)").data("kendoDropDownList");
                beginOperator.value("gte");
                beginOperator.trigger("change");
                beginOperator.readonly();

                var logicOperator = e.container.find("[data-role=dropdownlist]:eq(1)").data("kendoDropDownList");
                logicOperator.readonly();

                var endOperator = e.container.find("[data-role=dropdownlist]:eq(2)").data("kendoDropDownList");
                endOperator.value("lte");
                endOperator.trigger("change");
                beginOperator.readonly();
            }

Specifically, for any date field, this function sets the first and last dropdown operators to "gte" and "lte" respectfully (Those are the dropdowns for the first date operator and the second date operator), and sets all of the dropdowns to read-only so the user can't change them (the only other dropdown, which is at index 1, is the logical comparison - only And makes sense, so we don't let users change it.)

This function applies this configuration for any fields that are of "date" type. I did it this way so that I could create this function once, put it in an Angular factory, and then reuse it for any grid that I needed. If you don't want to apply this as a blanket configuration across all of your date columns, you can change the conditional to check for fields by name. Example:

if (e.field == "fieldName")

Hopefully this will be helpful to someone else. This doesn't give you ultimate customization of the UI in the filter menu, but it does let you simply set up a filter between two dates. I'm sure someone clever could merge this with my original strategy (replacing the markup for the filter menu entirely) to come up with something completely customized.

Answer 2

You can try the following method that gives two option for filtering: With two filtering fields and with the grid column.

<div>
    From: <input id="from" /> To: <input id="to" />
    <br />
    <br />
    <button id="filter" class="k-button">Filter</button>
</div>
<br />
<br />
<div id="grid"></div>
<script>
    var grid = $("#grid").kendoGrid({
        dataSource: {
            type: "json",
            transport: {
                read: "/Controller/Action"
            },
            pageSize: 10,
            schema: {
                model: {
                    fields: {
                        OrderId: { type: 'number' },
                        OrderItem: { type: 'string' },
                        OrderDate: { type: 'date' }
                    }
                }
            }
        },
        pageable: true,
        filterable: true,
        navigatable: true,
        selectable: true,
        columns:
        [
            { field: "OrderID", width: 100, title: "Order ID", filterable: false },
            { field: "OrderItem", width: 100, title: "Order Item", filterable: false },
            {
                field: "OrderDate", type: "date", width: 125, title: "Order Date",
                template: "#= kendo.toString(kendo.parseDate(EventTime, 'dd.MM.yyyy hh:mm tt'), 'dd.MM.yyyy hh:mm tt') #",
                filterable: {
                    ui: "datetimepicker"
                }
            },
        ]
    }).data("kendoGrid");

    $("#from, #to").kendoDatePicker({
    });

    $("#filter").on("click", function () {
        //var from = $("#from").data("kendoDatePicker").value();
        //var to = $("#to").data("kendoDatePicker").value();

        //If there is a problem regarding to the two lines above, you can also try this:
        var from = $("#from").val();
        var to = $("#to").val();

        var filter = [
            { field: "OrderDate", operator: "gte", value: from },
            { field: "OrderDate", operator: "lte", value: to }
        ];
        grid.dataSource.filter(filter);
    });
</script>

For more information Date Range Filtering in Kendo Grid Using WEB API and Entity Framework.