How to define a Kendo column filter between two dates?

Question

In our application, we want the filter in the date column to ask the user for a start date and an end date, while the filter returns rows in which the corresponding field is between (or included) these two dates.

Initial approach

Our initial approach was to limit the date types to use the gte and lte operators and add the "extra: true" filterable parameter in the column. This came close, but presented the following problems: A) Each date entry could use either the gte (Start) or lte (End) operator, providing undesirable flexibility and the ability for the user to create a filter that will never return results, and B) Logical comparison (And / Or), which we do not need.

Best approach

This question is answered by Matthew Erwin, which is very close to us: it allows you to completely filter the filter, so we can imagine just Start Entering the date and entering the end date. However, what I cannot get is to associate the correct filter operation with the correct 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 ki-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 ki-calendar\">select</span></span></span></span>" ); } }; 

With this approach, an Odata filter option is created for each of the dates, however, it uses the eq Equal To operator, so the values ​​are never returned. We do not create filters specifically for the data source.

Is there an easy way to associate each of these date entries with a specific filter statement? Is there a better way to approach this issue? It seems that filtering dates based on the Start-End range will usually be desirable.

Other information

We use 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 will also summarize in this answer.

The final solution was as follows:

• Use the "Messages" option of the "filter" column to customize the display of the filter display.
• Use the "Advanced" option of the "filter" column option to get an additional date selector in the filter menu.
• Set the "Operators" option in the grid filtering option to indicate which operators can be used for dates (gte, lte), and what text is displayed for each (start date, end date).
• Use the filterMenuInit event to configure filter controls.

Final result

Column filter

The following filtering options were used:

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

Additionally, it gives us a second date selector, and the message “info” sets up the text displayed at the top of the filter menu.

Filter Mesh

I used the "operator" option in the "filterable" parameter at the grid level to create date filters only for the gte and lte operators, as well as to adjust the text for these operators. This is what completes the statement configuration object:

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

Since we want this to apply to all dates, we put it in a factory and reuse it in every controller / view angular.

filterMenuInit Event

By providing a filterMenuInit event handler, you can retrieve and configure individual controls in the filter menu as you create it. The handler function 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(); } 

In particular, for any date field, this function sets the first and last drop-down operators to "gte" and "lte" with respect (these are drop-down lists for the date operator and date operator) and sets all the drop-down lists to be read-only, so the user does not can change them (the only other drop-down menu that is in index 1 is a logical comparison - only And it makes sense, so we do not allow users to change it.)

This function applies this configuration to any date type fields. I did it so that I could create this function once by placing it in an angular factory and then reusing it for any mesh I needed. If you do not want to apply this as a general configuration in all date columns, you can change the condition for checking fields by name. Example:

 if (e.field == "fieldName") 

Hope this will be helpful to someone else. This does not give you the final user interface settings in the filter menu, but it allows you to simply configure the filter between two dates. I'm sure someone smart could combine this with my original strategy (completely replacing the markup for the filter menu) to come up with something completely customized.

Answer 2

You can try the following method, which gives two filtering options: with two filtering fields and a 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, Filter date ranges in the Kendo grid using the WEB API and Entity Framework .