Filter and sort string columns as Date

6 posts, 1 answers
  1. Pino
    Pino avatar
    4 posts
    Member since:
    Oct 2020

    Posted 06 Oct 2020 Link to this post

     

    Hello,

    I have a grid and the data I receive is in string, even for my 2 date columns. I was able to change them in the preferred Date format, but I can't seem to get the filter and sorting working. 

    I tried to change the datatype of the columns to datetime, but that was unsuccesfullI

    I'm now looking for some help to create a custom filter (and sort) so that I can sort and filter my date columns the correct way.

    It's a pretty complicated solution, but here's some of the code were I load my grid:

    function loadGrid(userGridSettings) {
            var gridSettings = {
                selectable: "multiple row",
                sortable: true,
                filterable: true,
                pageable: {
                    refresh: true,
                    pageSizes: [10, 20, 50, 100],
                    buttonCount: 5
                },
                resizable: true,
                columnMenu: true,
                toolbar: kendo.template($("#searchgrid-toolbar").html()),
                editable: "popup",
                reorderable: true,
                columnHide: saveState,
                columnShow: saveState,
                columnResize: saveState,
                columnReorder: saveState,
                columns: [{
                    field: "IsSelected",
                    title: null,
                    sortable: false,
                    filterable: false,
                    width: 25,
                    menu: false,
                    reorderable: false
                }, {
                    field: "BatchName",
                    title: "Batchnaam",
                    filterable: { multi: true }
                }, {
                    field: "RequesterPartyRequired",
                    title: "Kabelsel.",
                    filterable: { multi: true }
                }, {
                    field: "StartDate",
                    title: "Plandatum start",
                    type: "date",
                    format: "{0: dd-MM-yyyy}",
                    filterable: {
                        ui: function (element) {
                            element.kendoDatePicker({
                                format: '{0: dd-MM-yyyy}'
                            })
                        },
                        multi: false
                    }
                }, {
                    field: "EndDate",
                    title: "Plandatum eind",
                    type: "date",
                    format: "{0: dd-MM-yyyy}",
                    filterable: {
                        ui: function (element) {
                            element.kendoDatePicker({
                                format: '{0: dd-MM-yyyy}'
                            })
                        },
                        multi: false
                    }
                }, {
                    field: "ID",
                    title: " ",
                    filterable: false,
                    sortable: false,
                    width: 25,
                    menu: false,
                    reorderable: false
                }]
            };
     
            var customFields = [{
                field: "IsSelected",
                template: "<input type='checkbox' class='checkbox' #= IsSelected !== undefined && IsSelected ? checked='checked' : '' # />",
                headerTemplate: "<input type='checkbox' id='checkAll' />"
            },
            {
                field: "StartDate",
                filterable: {
                    ui: function (element) {
                        element.kendoDatePicker({
                            format: '{0: d-M-yyyy}'
                        })
                    },
                    operators: {
                        string: {
                            eq: "Is gelijk aan",
                            neq: "Is niet gelijk aan"
                        }
                    },
                    multi: false
                },
                format: '{0: d-M-yyyy}'
            },
            {
                field: "EndDate",
                filterable: {
                    ui: function (element) {
                        element.kendoDatePicker({
                            format: '{0: d-M-yyyy}'
                        })
                    },
                    operators: {
                        string: {
                            eq: "Is gelijk aan",
                            neq: "Is niet gelijk aan"
                        }
                    },
                    multi: false,
                },
                format: '{0: d-M-yyyy}'
            },
             
            {
                field: "ID",
                template: "#=actionTemplate(data)#"
            }];
     
            if (userGridSettings !== undefined && userGridSettings !== "" && userGridSettings !== null && JSON.stringify(userGridSettings) !== "{}") {
                gridSettings.columns = userGridSettings;
            }
     
            $.each(customFields, function (customFieldIndex, customField) {
                $.each(gridSettings.columns, function (columnIndex, column) {
                    if (column.field == customField.field) {
                        if (customField.sortable) {
                            column.sortable = customField.sortable;
                        }
                        if (customField.template) {
                            column.template = customField.template;
                        }
                        if (customField.headerTemplate) {
                            column.headerTemplate = customField.headerTemplate;
                        }
                        if (customField.filterable) {
                            column.filterable = customField.filterable;
                        }
                    }
                });
            });
     
            searchGrid = $("#searchGrid").kendoGrid(gridSettings).data("kendoGrid");
     
            searchGrid.thead.find("[data-field=IsSelected]>.k-header-column-menu").remove();
            searchGrid.thead.find("[data-field=ID]>.k-header-column-menu").remove();
    }

     

     

     

  2. Pino
    Pino avatar
    4 posts
    Member since:
    Oct 2020

    Posted 06 Oct 2020 in reply to Pino Link to this post

    I found this here: https://www.telerik.com/forums/grid-date-filtering---with-string-dates

    But I'm unable to understand how to implement this custom filter.
  3. Pino
    Pino avatar
    4 posts
    Member since:
    Oct 2020

    Posted 06 Oct 2020 Link to this post

    Here's some code of how I load my data

    function loadData(manual) {
            if (manual === undefined) {
                manual = false;
            }
     
            if (!manual) {
                if (fieldValues.InfrastructureFacilityID === undefined) {
                    return;
                }
            }
     
            if (!validateFilters()) {
                return;
            }
     
            $("#checkAll").prop("checked", false);
            checkAll(false, false);
     
            $(".alert-more-items").hide();
     
            var request = createRequestModelAndSetStorage();
     
            var ds = new kendo.data.DataSource({
                transport: {
                    read: function read(options) {
                        //get data from webapi
                        enexis.services.connectioncase.findAssignmentCases(request, function (result) {
                            if (result.Success) {
                                $("#checkAll").prop("checked", false);
                                checkAll(false, true);
                                if (result.TotalItems > 500) {
                                    $(".alert-more-items .alert").html("Er zijn '" + result.TotalItems + "' items gevonden die voldoen aan uw zoekcriteria. De onderstaande dataset bevat maximaal 500 items, verfijn uw zoekcriteria en zoek opnieuw.");
                                    $(".alert-more-items").show();
                                }
                            } else {
                                enexis.helpers.forms.showError(result.ErrorMessages || ['Er is een onverwachte fout opgetreden.']);
                            }
     
                            options.success(result.Items || []);
                        }, function (result) {
                            $("#checkAll").prop("checked", false);
                            checkAll(false, true);
                            enexis.helpers.forms.showError(['Er is een onverwachte fout opgetreden.']);
                            options.error(result);
                        });
                    }
                },
                pageSize: 20
                //tried to set the type to Date, but then no data was shown in the columns
                //,
                //schema: {
                //    model: {
                //        fields: {
                //            StartDate: { type: "date" },
                //            EndDate: { type: "date" }
                //        }
     
                //    }
                //}
            });
     
            searchGrid.setDataSource(ds);
            searchGrid.thead.find("[data-field=IsSelected]>.k-header-column-menu").remove();
            searchGrid.thead.find("[data-field=ID]>.k-header-column-menu").remove();
        }
  4. Answer
    Tsvetomir
    Admin
    Tsvetomir avatar
    849 posts

    Posted 08 Oct 2020 Link to this post

    Hi Jeroen,

    The filter and sort operations happen at the data source. Therefore, it is important what is the type in the data source and not the display value for the fields. 

    In JavaScript, there is no DateTime type, it is called simply "date". Therefore, in order to let the grid know that it deals with a date data type, set the following schema.model option:

    dataSource: {
                        transport: {
                            read: MYURL
                        },
                        schema: {
                            model: {
                                fields: {
                                    OrderDate: { type: "date" },
                                }
                            }
                        },
                    },

    However, the filter and sort will be done by taking into account the time portion of the date. If you would like to ignore it, follow the example from the article below:

    https://docs.telerik.com/kendo-ui/knowledge-base/grid-group-by-date-only

     

    Kind regards,
    Tsvetomir
    Progress Telerik

    Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at https://learn.telerik.com/.

  5. Pino
    Pino avatar
    4 posts
    Member since:
    Oct 2020

    Posted 08 Oct 2020 in reply to Tsvetomir Link to this post

    Thanks, I tried to convert the columns to date, but no data was returned. That was because in the backend I returned the data as string (formatted it to the correct format in the backend, not necessary). So I now return dates from the backend and do the format in de grid itzelf. I also removed the time on the backend side so the filtering is correct.

     

    So just as I thought it was very simple, but I was thinking to complex.

  6. Tsvetomir
    Admin
    Tsvetomir avatar
    849 posts

    Posted 08 Oct 2020 Link to this post

    Hi Jeroen,

    I am glad to hear that the issue has now been resolved. As a side note, if the server returns data in a specific format, you could implement logic in JavaScript that would parse the data. For this purpose, handle the parse method of the schema:

    https://docs.telerik.com/kendo-ui/api/javascript/data/datasource/configuration/schema#schemaparse

     

    Regards,
    Tsvetomir
    Progress Telerik

    Five days of Blazor, Angular, React, and Xamarin experts live-coding on twitch.tv/CodeItLive, special prizes, and more, for FREE?! Register now for DevReach 2.0(20).

Back to Top