Filter grid column by date between

1 Answer 36 Views
Grid
Lee
Top achievements
Rank 1
Iron
Iron
Iron
Lee asked on 11 Apr 2022, 09:06 PM | edited on 12 Apr 2022, 08:46 PM

I would like to create a custom filter for a kendo ui grid with the following requirements:

  1. The column is a date.
  2. There are other filterable columns that are not necessarily dates.
  3. The filter dropdown should not be unnecessarily complex. There should be a startDate, endDate, filter, and clear buttons. No other dropdowns (no "and", "greater than", "less than", etc).
  4. The filter should be in a dropdown menu from the column and not a separate row.
  5. The dataSource is a JSON object.
  6. The date in the JSON object is in this format: "2022-04-12T00:00:00".
  7. When the user clicks the clear button in the dropdown and then tries to filter again it should still work as expected.

I tried following this Dojo: https://dojo.telerik.com/EVEwuREK but it only got me part way there as I do not know how to override the filter button and include the filters from the other columns. In this example, there is no submit or clear button so if the user enters dates they can't bet back to the full list and if they filter by column that filter gets cleared when filtering by date. It also produces unexpected results depending on whether you filter by country or date first. 

I made a sample Dojo here which shows the bug with filtering: Dojo

Steps to Reproduce:

  1. Go to the Dojo and click run.
  2. Click the birthday filter.
  3. Choose 1/1/1980 - 1/1/1989 and click filter
  4. Observe that the correct entry is displayed.
  5. Click the filter on the birthday field again.
  6. Click Clear (observe that all 3 records are now shown as expected)
  7. Click the birthday filter again.
  8. Choose the same dates as before 1/1/1980 - 1/1/1989 and click filter.
  9. Notice this time 2 records are showing and neither fall inside that date range.

1 Answer, 1 is accepted

Sort by
0
Accepted
Lee
Top achievements
Rank 1
Iron
Iron
Iron
answered on 12 Apr 2022, 09:58 PM

Ok, I think I found the solution. Clicking clear seems to change the second operator value back to the first item in the list so I needed to add a filterMenuOpen that changes the second operator value back to "lte".

filterMenuOpen: function (e) {
    if (e.field == "dob") {
        let endDateOperator = e.container.find("select:last").data("kendoDropDownList");
        endDateOperator.value("lte");
        endDateOperator.trigger("change");
    }
}

Here is my total solution:

<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <title>Untitled</title>

  <link rel="stylesheet" href="https://kendo.cdn.telerik.com/2022.1.301/styles/kendo.common.min.css">
  <link rel="stylesheet" href="https://kendo.cdn.telerik.com/2022.1.301/styles/kendo.rtl.min.css">
  <link rel="stylesheet" href="https://kendo.cdn.telerik.com/2022.1.301/styles/kendo.default.min.css">
  <link rel="stylesheet" href="https://kendo.cdn.telerik.com/2022.1.301/styles/kendo.mobile.all.min.css">

  <script src="https://code.jquery.com/jquery-1.12.3.min.js"></script>
  <script src="https://kendo.cdn.telerik.com/2022.1.301/js/angular.min.js"></script>
  <script src="https://kendo.cdn.telerik.com/2022.1.301/js/jszip.min.js"></script>
  <script src="https://kendo.cdn.telerik.com/2022.1.301/js/kendo.all.min.js"></script></head>
<body>
  <div id="myGrid"></div>
  <script>
    let data = [
      {
        personId: 1,
      	name: "Lee",
      	dob: "1990-02-15T00:00:00"
      },
      {
        personId: 2,
        name: "John",
        dob: "1980-12-15T00:00:00"
      },
      {
        personId: 3,
        name: "Jenny",
        dob: "2001-08-31T00:00:00"
      }
     ];
    
    $("#myGrid").kendoGrid({
  dataSource: {
    data: data,
    sort: {
      field: "dob",
      dir: "desc"
    },
    model: {
            id: "personId",
            fields: {
              personId: {
                  type: "string",
              },
              dob: {
                  type: "date"
              }
            }
        }
    },
  },
  sortable: true,
  filterMenuInit: function (e) {
      if (e.field == "dob") {
        e.container.find("input").kendoDatePicker({});
        var startDateOperator = e.container.find("select:first").data("kendoDropDownList");
        e.container.find("select:first").closest(".k-widget.k-dropdown").before(`<div class="k-widget label">From</div>`);
        startDateOperator.value("gte");
        startDateOperator.trigger("change");
        let endDateOperator = e.container.find("select:last").data("kendoDropDownList");
        e.container.find("select:last").closest(".k-widget.k-dropdown").before(`<div class="k-widget label">To</div>`);
        endDateOperator.value("lte");
        endDateOperator.trigger("change");
        e.container.find(".k-widget.k-dropdown").hide();
      }
  },
                          filterMenuOpen: function (e) {
                        if (e.field == "dob") {
                            let endDateOperator = e.container.find("select:last").data("kendoDropDownList");
                            endDateOperator.value("lte");
                            endDateOperator.trigger("change");
                        }
                    },

  filterable: {
    operators: {
      date: {
          gte: "After",
          lte: "Before",
      }
    }
  },
  columns: [
    {
      field: "personId",
      title: "Person ID",
      menu: false
    },
    {
      field: "name",
      title: "Name",
      width: 100,
      menu: false,
      filterable: false,
    },
    {
      field: "dob",
      title: "Birthday",
      width: 130,
      filterable: {
          messages: {
              info: "Show Dates",
              title: "Show Dates Between"
          }
      },
    }
  ]
});
  </script>
</body>
</html>

Nikolay
Telerik team
commented on 14 Apr 2022, 09:54 AM

Hi Lee,

This approach seems valid to me. However, I think you can omit the filterMenuInit() logic and update the operator messages with the filterable.operators.date configuration:

filterable: {
          operators: {
            date: {
              gte: "From",
              lte: "To",
            }
          }
        },

Dojo Demo: https://dojo.telerik.com/oJOriyUc

Let me know if you have any questions.

Regards,

Nikolay

Lee
Top achievements
Rank 1
Iron
Iron
Iron
commented on 14 Apr 2022, 12:15 PM | edited

Unfortunately this solution doesnt satisfy condition 3 above. Omitting the filter menu init logic leaves all of the confusing dropdown boxes visible to the user. The filter init logic hides those and sets the appropriate values of after and before. This way they dont have to worry about accidentally choosing before and before which would blow up and dont see and/or.
Nikolay
Telerik team
commented on 19 Apr 2022, 10:43 AM

Hi Lee,

Sorry for missing point number 3.

Indeed, in this case, I agree your solution is suitable for the requirement you have. Thank you for sharing it with the community.

Regards,

Nikolay

Tags
Grid
Asked by
Lee
Top achievements
Rank 1
Iron
Iron
Iron
Answers by
Lee
Top achievements
Rank 1
Iron
Iron
Iron
Share this question
or