[Solved] How to filter on a column that is not bound to the grid?

[Laksh]

I have a datasource with two columns of type Date  Lets say Date1 and Date2. This datasource is generated using SQL view.
 Date1 has date and time value while Date2 has only date value ( time is 00:00:00)
Only Date1 column is bound to the grid and Date1 column also has filter enabled. Filter shows only date calendar.(no time)
Date2 column is not bound to the grid
server side filtering is enabled.

Is it possible to filter on Date2 column?

For example
Grid shows "09/04/2014 10.30.00 AM". which is Date1. But if I select "equal to" operator and then select the date "09/04/2014" from the calendar, then I want the record to be appear regardless of the time.

I thought of returning Date2 column just for filtering purpose in datasource, but I don't know how to use the Date2 column in the filter. Because by default grid will use the bound column for filtering.

[Alexander Popov]

Hi Laksh,

Yes, this could be achieved by using the DataSource's filter method. For example: 

grid.dataSource.filter({field: "Date2", operator: "eq", value: someDateValue});

Regards,
Alexander Popov
Telerik

 

 

[Laksh]

I think I didn’t put my question properly

The grid is getting data from SQL view. I want to show date & time on the grid but filter it only by date. 

The grid is bound to Date1 column which has date & time and this column needs to be filterable by Date only. Because user would not remember the exact time so it doesn’t make sense to ask them to filter by time. However we want to show date & time on the grid that's why Date1 column is returning date & time. Since column has time I think the server side filter world not work. So to achieve this I thought of adding one more column in SQL view which only returns Date and want to use this column
for filtering. This is Date2 column. Note that Date2 will not be visible on the grid

var gridDS: kendo.data.DataSource = createGridDataSource("GetData", $grid);

    $grid.kendoGrid({
        dataSource: gridDS,
        autoBind: true,
        columnMenu: true,
        filterable: {
            extra: false,
            operators: {
                string: {
                    eq: "Is equal to",
                    neq: "Is not equal to"
                }
            }
        },
        columns: [
            {
                field: "ID",
                hidden: true,
                width: "74px"
            },
            {
                field: "Date1",         //Note Date1 is bound to the grid
                title: "Modified Date",
                format: "{0:MM/dd/yyyy hh:mm:ss}",
                filterable: { ui: modifiedDateFilter }  //But i want to filter it by Date2
            },
        ]
    });

    function modifiedDateFilter(element: any): void {
        element.kendoDatePicker();
    }

    function createGridDataSource(action: string, progressElem: any): kendo.data.DataSource {
        return new kendo.data.DataSource({
            type: "odata",
            transport: {
                read: {
                    url: svcUrl + "/" + action,
                    dataType: "json",
                    timeout: timeout
                },
            },
            schema: {
                data: function (data) {
                    return data["value"];
                },
                total: function (data) {
                    return data["odata.count"];
                },
                model: {
                    fields: {
                        ID: { type: "number" },
                        Date1: { type: "date" },        //This has date & time
                        Date2: { type: "date" },        //This has date
                    }
                }
            },
            serverFiltering: true,
            serverSorting: true
        });
    }
});

[Alexander Popov]

Hello again Laksh,

There are a couple simple approaches that I could use in this scenario:

• Modify the server-side code so that the time portion of the filter is stripped before calling the SQL View
• Replace the Grid's Date1 FilterMenu widget with a FilterMenu for the Date2 column. This could be achieved by getting the instance of the FilterMenu, destroying it and initializing a new one using different field, as shown in this example.

Regards,
Alexander Popov
Telerik

 

 

[Laksh]

I would like to use the first approach "Modify the server-side code so that the time portion of the filter is stripped before calling the SQL View" but i'm not able to understand on the server where would I get the filter value?

The grid's data source is calling Web API to get the data. and Web API simply return context.vwGetData.(We are using entity framework)
when I run SQL profiler I see the filters are applied on the view as expected in where condition. But i'm not sure how this is happening and how do I get access to the filter value before it applies on the SQL view.
Below is my existing code

public class MyController : ODataController
   {
       private MyDBContext db = new MyDBContext();

       [EnableQuery]
       public IQueryable<vwGetData> GetData()
       {
          // How do I get filter value before pass it down to SQL
           return db.vwGetData;
       }
   }

[Alexander Popov]

Hi Laksh,

Modifying the filter in an OData controller is not related to Kendo UI and is beyond the scope of our support services, however you can try getting the ODataQueryOptions argument, modifying the filter expression and applying it to the dataset. For example: 

public IQueryable<vwGetData> GetData(ODataQueryOptions QueryOptions)

Alternatively, you could use the client-side approach I previously suggested.

Regards,
Alexander Popov
Telerik