Issues with Grid filterable columns

[Eric]

I am experiencing two issues. 

1. Every time I type a character in the filter box at the top of any of my columns, my Ajax Read method ("_CustomBinding") is called 2 times.  The first call populates the type ahead of the filter box, and the second one refreshes the grid.  I do not want the grid to refresh until the enter key is pressed in the filter box, not on each key press.  How do I disable the call to refresh the grid on every key press in the filter box?
2. From the Kendo examples, I cannot find a way to differentiate between the Ajax Read call for populating the grid and populating the type ahead data in the filter boxes at the top of the columns.  I need to be able to differentiate between these two uses for a couple of reasons.

1. My grid performs all of the paging on the server.  When I type into a column filter box, the call to the binding method includes the page number and page size.  This causes my database query to limit the results to only records for the current page, which is not what I want.  The auto fill should include values for all pages.  I see in your online example that you do not apply paging at the database level, which is why it includes results across pages. 
2. If I were successful at ignoring the paging when fetching the type ahead data, it would pull back way too many records.  I need to be able to differentiate between type ahead calls and ones for populating my grid data so that I can apply a distinct select for only the one column needed on my database query to properly fetch just the data needed for display in the type ahead, so I won’t have performance issues.

 

Please, advise me as to how I can overcome these issues.  Thank you.

I have a very complicated Kendo Grid.  Here is just part of it:

 

@(Html.Kendo().Grid<ProspectiveAdvantage.ViewModels.MemberListingModel>().Name("Members").TableHtmlAttributes(new { @class = "tblPatient" })

    .AutoBind(Model.AutoPopulateResults)

    .Columns(columns =>

    {

        columns.Bound(theMember => theMember.LastName).Width(30).Filterable(ftb => ftb.Cell(cell => cell.Operator("contains").ShowOperators(false).MinLength(3).SuggestionOperator(FilterType.Contains)));

        columns.Bound(theMember => theMember.FirstName).Width(30).Filterable(ftb => ftb.Cell(cell => cell.Operator("contains").ShowOperators(false).MinLength(3).SuggestionOperator(FilterType.Contains)));

        columns.Bound(theMember => theMember.DisplayClientMemberId).HeaderTemplate("Patient ID").Width(30).Filterable(ftb => ftb.Cell(cell => cell.Operator("contains").ShowOperators(false).MinLength(3).SuggestionOperator(FilterType.Contains)));

        columns.Bound(theMember => theMember.DisplayBirthDate).HeaderTemplate("DOB").Format("{0:MM/dd/yyyy}").Width(30).Filterable(ftb => ftb.Cell(cell => cell.Operator("contains").ShowOperators(false).MinLength(3).SuggestionOperator(FilterType.Contains)));

…

                .DataSource(dataBinding => dataBinding.Ajax()
                .Read(read => read.Action("_CustomBinding", "PCPandCA").Data("members_FetchSearchParameters"))
                .Events(events => events.Error("handleAjaxErrorFromGrid"))
                 .ServerOperation(true)
                 .PageSize(Model.PageSize).Model(model => { model.Id(e => e.MemberID); model.Field(f => f.FirstName); })
                 //default sort DisplaySignatureDue column by ascending
                 .Sort(sort => {
                     if (!AssessmentExpired) { sort.Add("DisplaySignatureDue").Ascending();}
                     if (AssessmentExpired) { sort.Add("DisplayExpiredDate").Ascending();}
                 })
    )
 
    .Pageable(pageable => pageable
        .PageSizes(true)
        .Refresh(true)
    )
    .Sortable()
    .NoRecords("No records found.")
    .Selectable()
    .Filterable(ftb => ftb.Mode(GridFilterMode.Row))
    .Events(events => events.Change("handleGridRowSelected").DataBinding("handleDataBinding").DataBound("handleDataBound").Filter("onFiltering"))
    .HtmlAttributes(new { style = "cursor:pointer;border: 0px;height: 380px;display: flex;overflow-y: scroll;" })
)

 

 

[Yanislav]

Hello Eric,

Let me address your questions below : 

1. From the shared configuration, I see that you are using the row filter configuration. The way this configuration is designed to work is to filter the Grid every time a new character is inserted. So, in this case, to avoid such behavior, you can use the following approaches: 

The first one is to use the default Filter mode, i.e. by setting the filtering configuration like this: Filterable(). In this mode a filter menu opens when clicking the row's filter icon. A request is sent only when the Enter button is pressed or when the Filter button is clicked in the filter menu. 

The other approach is to set a MinLength configuration for the filtered columns so it will send a request based on the length of the inserted text within the text box : 

columns.Bound(p => p.Freight).Filterable(f=>f.Cell(c=>c.MinLength(5)));

 With value 5 passed as a parameter, that would mean a request will be sent once at least 5 characters are entered in the filter input.

Alternatively, you can utilize the columns.filterable.cell.delay property : 

columns.Bound(p => p.Freight).Filterable(f=>f.Cell(c=>c.Delay(delay)));

 

2. About the second question, how to tell when a filter is applied when the request arrives on the server. You can check the filters count on the server, so if it equals 0 no filters are applied :

public ActionResult Orders_Read([DataSourceRequest]DataSourceRequest request)
		{
			if(request.Filters.Count == 0)
            {
				//Your logic for no filtering applied 
            }
...

Note that if other operations are applied like sorting are used together with filtering, the filters would be within the DataSourceRequest object too. To workaround this you can send a custom object which contains information if the request is for filtering as follows : 

1. Declare a boolean variable that will indicate if a filter is applied and set its value to true when the Filter event of the Grid fires:

 
var isFilterApplied = false;
function onFilter() {
      isFilterApplied = true;
}

2. Sending the value of this variable could be done by using the .Data() configuration within of the Read operation:

.Read(read => read.Action("Orders_Read", "Grid").Data("isFilter"))

3. And finally reset its value on requestEnd, this way the filters will be applied but the variable will indicate on the server if before the request a filtering is applied.

Attach a handler for the RequestEnd event in the DataSource Events configuration:

 .Events(ev=>ev.RequestEnd("onRequestEnd"))

    function onRequestEnd() {
        isFilterApplied = false;
    }

For more information on how to pass additional data with a request, I recommend you the following discussion on our forum : 
https://www.telerik.com/forums/pass-additional-parameters-to-read-ajax-datasource-method---mvc

Regards,
Yanislav
Progress Telerik

[Eric]

@yanislav, thank you for the information.  Everything you said is doing what you described, but now I have discovered something really disappointing that I am afraid is going to prevent me from being able to use the filter columns.  Maybe you have more insight.

 

As I type into the filter box, it triggers the server Read method.  I am using the trick you described to determine if the call was triggered by a filter box.  If it is for a filter, then I alter my database query to be “select distinct singleColumn”, instead of “select top @recordsPerPage column1, column2, ..”.  This allows me to only retrieve the data that I need, which is important since my database has millions of records.

 

However, the grid is doing something unexpected.  The grid is using the filter query to populate both the filter box type ahead and the grid, which means that the grid is populating with only the singleColumn.  I see that the grid is making a second Read call to the server without the filter flag, which returns a result set with all of the columns, but the grid is ignoring these results to only display the ones that came from a filter query.  When I hit the refresh button on my grid, it performs another non-filter query, which fixes everything, but it would be wasteful to force a grid refresh each time the user types into a filter box.

 

Is there a way I can get the control to not refresh the grid results with the filter results?  The filter results should only be used for the filter box type ahead.

[Yanislav]

Hello Eric,

If I understand correctly, the problem is related to the requests that are sent when the user enters а text in the filter field. In this case, since the Grid makes a second request when the built-in filter AutoComplete's change event fires , I would recommend two alternatives:

1. The first approach is to use the default 'menu filter-mode':

 .Filterable(ftb => ftb.Mode(GridFilterMode.Menu))

In this mode a single request is sent on clicking the "Filter" button in the filter menu.

2. The other way is to use a custom filter field, instead of using the built-in AutoComplete for the "Row" filter mode, which sends requests on typing and on change. A custom template could be initialized the following way : 

1. Specify a handler that will initialize the component which is going to be used as a filter field:

columns.Bound(p => p.ShipName).Filterable(ftb => ftb.Cell(cell => cell.Template("template"))).Width(225);

2. Within the handler refer the element and initialize a Kendo TextBox for example :

 function template(args) {
            args.element.kendoTextBox();
        }

You can use its change event to apply filtering to the Grid programmatically.

I hope this helps!

Regards,
Yanislav
Progress Telerik