Filter and sort string columns as Date

[Pino]

 

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();
}

 

 

 

[Pino]

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.

[Pino]

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();
    }

[Tsvetomir]

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

[Pino]

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.

[Tsvetomir]

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