Implementing a "Contains" Filter for a Foreign Key Column in Kendo Grid

[Anas]

Hello,

I am currently working with a Kendo Grid in my ASP.NET MVC application, specifically with a foreign key column that retrieves its data from a separate data source. I have enabled filtering on this grid, and I would like to implement a "contains" filter for a foreign key column, specifically for the "Locations" column.

Here’s the relevant portion of my Kendo Grid configuration:
            @(
                Html.Kendo().Grid<FMS_Cloud_CoreMVC.ViewModels.EnergyAdmin>()
                            .Name("EAData")
                            .Filterable()
                            .Columns(
                            
                            columns =>
                            {
                                columns.ForeignKey(p => p.CompLocID, ds => ds.Read("GetLocations", "EnergyAdmin"), "CompLocID", "CompLocString")
                                .Title("Locations").Width(300)
                                .Filterable(ftb => ftb.Cell(cell =>
                                    cell.Operator("contains").SuggestionOperator(FilterType.Contains)));





                    columns.ForeignKey( p => p.Provider, ds => ds.Read( "GetEnergyProviders", "EnergyAdmin"), "DDLID", "ProviderName")
                    .Title("Provider").Width(150) ;
                               
                    columns.Bound(p => p.NMI).Width(150);
                    columns.Bound(p => p.WWDeviceId).Width(150);
                   

                    columns.Command(command => { command.Edit().Text(" "); command.Destroy().Text(" "); }).Width(200);
                })
                .ToolBar(toolbar => toolbar.Create())
                .Editable(editable => editable.Mode(GridEditMode.InLine))
                .Pageable()
                .Sortable()
                .Scrollable()
                .HtmlAttributes(new { style = "height:730px;" })
                .DataSource(dataSource => dataSource
                .Ajax()
                .PageSize(20)
                .Events(events => events.Error("error_handler"))
                .Model(model => model.Id(p => p.EnergyAdminId))
                .Create(update => update.Action("EditingInline_Create", "EnergyAdmin"))
                    .Read(read => read.Action("EditingInline_Read", "EnergyAdmin").Data("myCustomReadParam"))
                .Update(update => update.Action("EditingInline_Update", "EnergyAdmin"))
                .Destroy(update => update.Action("EditingInline_Destroy", "EnergyAdmin"))
                )
                )





While the "contains" filter works as expected with a simple text column, I would like to maintain the dropdown functionality for the foreign key column. Is there a way to achieve a "contains" filter on a foreign key column while still allowing users to select from the dropdown options?

I would greatly appreciate any guidance or examples you can provide.

Thank you in advance for your assistance!

[Eyup]

Hi Anas,

 

Thank you for writing to us.

You can achieve this requirement using the .Events(e=>e.FilterMenuInit()) event and custom javascript:
https://docs.telerik.com/kendo-ui/knowledge-base/grid-custom-filter-with-foreignkey-column

Here is a live sample for demonstration:
https://dojo.telerik.com/BYkfmcfH/3

Initially, the Category column will have numeric values coming from the Value Field of the column:

But with this implementation the ForeignKey column can be filtered using its Text Field:

Alternatively, you can also use Custom Editor from this sample (Category column) to achieve DropDownList column for the grid:
https://demos.telerik.com/aspnet-mvc/grid/editing-custom

The magic happens using 3 steps:

1. Model UIHint property:

        [UIHint("ClientCategory")]
        public CategoryViewModel Category 
        { 
            get; 
            set; 
        }

2. DefaultValue in the model similar to your code:

            model.Field(p => p.Category).DefaultValue(
                ViewData["defaultCategory"] as Kendo.Mvc.Examples.Models.CategoryViewModel);

3. The Editor Template:

@model Kendo.Mvc.Examples.Models.CategoryViewModel

@(Html.Kendo().DropDownListFor(m => m)
        .DataValueField("CategoryID")
        .DataTextField("CategoryName")
        .BindTo((System.Collections.IEnumerable)ViewData["categories"])
)

Do you find this information beneficial? Let me know what you think.

 

Regards,
Eyup
Progress Telerik

[Anas]

Thank you for your response. However, the solution provided does not implement the "contains" filter for the foreign key column in the grid list, which is my primary requirement. I want to maintain the dropdown functionality while allowing users to filter options based on a "contains" condition. Any further guidance on how to achieve this would be greatly appreciated.

Something like this.

[Eyup]

The ForeignKey column is using filtering type Enum:

The thing here is that the Grid usually does not contain the Text field of the ForeignKey column. If you want to see the "Contains" option, and the filtering to work for the Text field and not the integer value field of the column, you will need to add the text field to the Grid datasource and Model as well:

        columns.ForeignKey(p => p.CategoryName, ds=> ds.Read(r => r.Action("Categories", "Grid")), "CategoryID", "CategoryName")
            .Title("Category").Width(200);

I hope this makes sense and clarifies why the Contains option is not possible without changing the data structure of the Grid. Let me know what you think.