This is a migrated thread and some comments may be shown as answers.

Filter and sort string columns as Date

5 Answers 2824 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Pino
Top achievements
Rank 1
Pino asked on 06 Oct 2020, 09:50 AM

 

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

 

 

 

5 Answers, 1 is accepted

Sort by
0
Pino
Top achievements
Rank 1
answered on 06 Oct 2020, 09:57 AM
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.
0
Pino
Top achievements
Rank 1
answered on 06 Oct 2020, 10:08 AM

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();
    }
0
Accepted
Tsvetomir
Telerik team
answered on 08 Oct 2020, 06:25 AM

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/.

0
Pino
Top achievements
Rank 1
answered on 08 Oct 2020, 09:27 AM

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.

0
Tsvetomir
Telerik team
answered on 08 Oct 2020, 05:04 PM

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).

Tags
Grid
Asked by
Pino
Top achievements
Rank 1
Answers by
Pino
Top achievements
Rank 1
Tsvetomir
Telerik team
Share this question
or