Filtered foreign key drop down list based on another key

[Vince]

Lets say I have two objects:

• Person
• Car

A person can own many cars. The grid contains a list of people and each person has a "primary car" attribute. This is obviously a foreign key to the car object. However, the drop down list should only contain cars that belong to that person. 

How do I accomplish this without doing something inefficient like:

public ActionResult Index()
{
        ViewData["cars"] = w.Cars.GetAll();
}

 

I understand I can use an/a EditorTemplate/ClientTemplate. However, every example I've found online has the inefficient step above. If I have a controller method such as:

public JsonResult GetCars([DataSourceRequest] DataSourceRequest request, int personId)
{
    return Json(w.GetCarsThatBelongToPerson(personId).ToDataSourceResult(request);
}

 

Can I not just use this controller method in the template as opposed to dumping the entire list of cars?

Thanks for reading!

 

[Viktor Tachev]

Hello Vince,

You can specify custom editor for the column as illustrated in the example below. When defining the Read() method for the DropDownList editor you can pass the persionId via the Data() method. This way, in the Read ActionMethod you can return only the cars related to specific person.

• http://demos.telerik.com/aspnet-mvc/grid/editing-custom

Regards,
Viktor Tachev
Telerik by Progress

[Vince]

Hi Viktor,

 

I'm aware of that and have been using it. However, the example also has this:

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

 

If I don't send the entire list of cars, or in the above example categories, the drop down list appears blank for existing entries until the user expands the list.

[Vince]

Oops, to clarify (is there an edit button I'm missing?)

The selected FK is stored correctly and appears correctly selected on existing entries. However, the selected "car" does not show up until the grid enters edit mode. The only way to solve this is to dump the entire list of cars as ViewData.

[Viktor Tachev]

Hi Vince,

In order to pass only the relevant items to the DropDown component you should define the DataSource for it. You will bind it like illustrated in this example.

In order to pass additional data to the Read Action you should use the Data() method as described here.

The code for the DropDownList would look similar to the following:

@(Html.Kendo().DropDownListFor(m => m)
        .DataValueField("CategoryID")
        .DataTextField("CategoryName")
        .DataSource(d=>d
            .Read(read=>read.Action("GetCategories", "Controller").Data("additionalData"))
        )
)

The JavaScript function will look similar to the one below:

function additionalData(e) {
    var row = $("#grid").find(".k-edit-cell").parent();
    var grid = $("#grid").data("kendoGrid");
    var item = grid.dataItem(row);

    var id = item.ProductId;

    return {
        productId : id
    };
}

Note that you may need to adjust the code in order to match your configuration.


Regards,
Viktor Tachev
Telerik by Progress

[Vince]

I need to bump this as this problem is still here nearly 2 years later.

Viktor, I already did everything you've mentioned in this thread prior to reading it. What I'm specifically asking for is skipping the ViewData step where we dump all said data.

c.ForeignKey(i => i.carId, (System.Collections.IEnumerable)ViewData["cars"], "Id", "ModelName").EditorTemplateName("CarDropDown")

 

Refer to the above code snippet. I explicitly want to remove (System.Collections.IEnumerable)ViewData["cars"] and force a fetch for cars when  a user selects a person. Either I'm blind, or the documentation is not clear because I have no idea how to do this.

 

Again, I do not want to dump ViewData from the controller on page load. I want to dynamically fetch said cars that belong to a person upon person selection. All the code is there already, but I am clueless as how to force the specific line above to deal with this.

[Viktor Tachev]

Hello Vince,

The ForeignKey column needs all values for the respective field that will be bound to the dropdown editor. If you would like to read the items explicitly when the editor enters edit mode I suggest using a custom editor for the Grid. The example below illustrates how it can be configured:

• https://demos.telerik.com/aspnet-mvc/grid/editing-custom

In your scenario you should modify the configuration of the ComboBox/DropDown editor to use remote data like in this example:

• https://demos.telerik.com/aspnet-mvc/dropdownlist/remotedatasource

Using this approach there will be no need to send the data for the dropdown editor in ViewData.


Regards,
Viktor Tachev
Progress Telerik

[Vince]

Viktor, the solution you provided is identical to your previous replies unless I am mistaken.

I find it hard to believe that Telerik would not have foreseen an issue like this, as this is quite common and would easily hit performance limitations in large scale applications, so I'm assuming I must be doing something wrong or hitting this from the wrong angle.

Example:

Assume there is a list of people. When a user selects a person, a grid is passed said person's ID and everything on the grid is filtered; this includes the cars the person owns. This is the point at which the grid should grab values, including the drop downs in said grid.

The problem is that the grid, with said custom drop down, requires every car in the database (as view data) to display correctly prior to entering edit mode. So the solution you've provided does not solve this problem.

[Viktor Tachev]

Hello Vince,

If a custom editor is used it will not be necessary to pass all data for the DropDownList in ViewData. When the dropdown is configured to use remote binding it will call the Read ActionMethod when entering edit mode. Furthermore, you can limit the items that are returned from the server by sending additional arguments via the Data method.


Regards,
Viktor Tachev
Progress Telerik

[Vince]

Viktor, you specifically just said:

"When the dropdown is configured to use remote binding it will call the Read ActionMethod when entering edit mode."

Then how does it grab the values before entering edit mode? If the drop down only reads in edit, then the initial page loading will display blanks which goes back to the initial problem and the one I ran into initially.

[Viktor Tachev]

Hello Vince,

I modified the custom editor example so that the dropdown editor was configured to read the data. The Read Action of the DropDownList was called when the relevant cell was edited. There was no call to the Read action before that on my end. Check out the video below showing the behavior I am observing.

• https://www.screencast.com/t/Wcuj23mg

Regards,
Viktor Tachev
Progress Telerik

[Jairo]

I made this example as it is in the link but my problem is currently the kendo filter does not work for me, as I do for it to serve me

[Alex Hajigeorgieva]

Hi, Jairo,

Can you provide more information about the issue that you are facing? This thread is about a Foreign Key column.

To implement DropDownList take a look at this online demo:

https://demos.telerik.com/aspnet-mvc/dropdownlist/serverfiltering

In case we have misunderstood the requirement, please feel free to get back to us with some more details.

Regards,
Alex Hajigeorgieva
Progress Telerik

[Jairo]

    columns.Bound(p => p.IdBodegaProyecto).Hidden();
    columns.Bound(p => p.ProyectoM).ClientTemplate("#=ProyectoM.DescripcionProyecto#").Width(380);
    columns.Bound(p => p.BodegaM).ClientTemplate("#=BodegaM.DescripcionBodega#").Sortable(true).Title("Bodega").Width(280).Filterable(false);
    if (ViewBag.IsPermisos.IsAuditar)

 

I have these two lists in my grid, I made the grid inline
  my problem is that the column filter doesn't work

my dropdownlis 

@model MaterialesBAL.Model.BodegaBodegaModel

@(Html.Kendo().DropDownListFor(m => m)
                  .AutoBind(false)
                  .ValuePrimitive(true)
                  .OptionLabel("Selecione una Bodega...")
                  .DataTextField("DescripcionBodega")
                  .DataValueField("IdBodega")
                  .DataSource(Source =>
                  {
                      Source.Read(read => read.Action("GetBodega", "BodegaProyectoes"))
                                .ServerFiltering(true);
                  })
)
@Html.ValidationMessageFor(m => m)

everything works fine except the column filters where I use the drop down list

Is there a way that the filters work?

[Viktor Tachev]

Hi Jairo,

 

In order to implement filtering for a column that is bound to complex object it is recommended to use the approach described in the following article:

https://docs.telerik.com/kendo-ui/knowledge-base/grid-filter-column-with-dropdownlist

 

Regards,
Viktor Tachev
Progress Telerik

[Jairo]

Hello Viktor Tachev,
Thanks for responding but still no data
I am using the event

.Events (e => e.Filter ("Filter"))
.Events (e => e.FilterMenuInit ("FilterMenu"))
and my function I have it like this
    function Filter (e) {
             dataSource: this.dataSource;
            if (e.field == "ProjectM" && e.filter! == null) {
                var currentFilter = []
                var filtersCount = e.filter.filters [0] .length
                var value = e.filter.filters [0] .value
                if (e.sender.dataSource.filter ()! == undefined) {
                  currentFilter = e.sender.dataSource.filter (). filters // Retain the other filters
                }
                currentFilter.push ({field: "ProyectoM.DescripcionProyecto", operatot: "eq", value: value})
                e.preventDefault ()
                e.sender.dataSource.filter (currentFilter)
              }
    }
    function FilterMenu (e) {
        dataSource: this.dataSource;
                if (e.field === "ProjectM") {
                $ (e.container) .find ('[type = "reset"]'). click (function (event) {
                  var filters = e.sender.dataSource.filter (). filters
                  for (let i = 0; i <= filters.length; i ++) {
                    if (filters [i] .field == "ProyectoM.DescripcionProyecto") {
                      filters.splice (i, 1);
                      e.sender.dataSource.filter (filters)
                    }
                  }
                })
              }
    }
 I have the column like this
   columns.Bound (p => p.ProyectoM) .ClientTemplate ("# = ProyectoM.DescripcionProyecto #"). Width (380);

//.Filterable(ftb => ftb.Mode(GridFilterMode.Menu))
    .Filterable(filterable => filterable
        .Extra(false)
        .Operators(operators => operators
            .ForString(str => str.Clear()
                .StartsWith("Starts with")
                .IsEqualTo("Is equal to")
                .IsNotEqualTo("Is not equal to")
            ))
        ) 

I don't know what I'm doing wrong but; (it doesn't work

[Viktor Tachev]

Hello Jairo,

 

Thank you for the provided code. I examined it and it seems that the logic inside is checking for a property named ProjectM. However, the Grid column is bound to a field named ProyectoM.

Would you update the if statements in the Filter and FilterMenuInit handlers and let me know how the behavior changes?

 

Regards,
Viktor Tachev
Progress Telerik

[Jairo]

good afternoon, thanks for replying
  According to what you tell me, ProyectoM comes from the main grid model so I can call my drop-down list

  public  class BodegaProyectoModel
    {
        public int IdProyecto { get; set; }
        public int IdBodegaProyecto { get; set; }
        [UIHint("EditarProyectoBodega")]
        [Required]
        public ProyectoBodegaModel ProyectoM { get; set; }

        [UIHint("EditarBodegaBodega")]
        [Required]
        public  BodegaBodegaModel BodegaM { get; set; }
    }

if conditions in filter events I place it to reference the column and my dropdown list

If I have to remove ProyectoM, what should go in the if and how do I refer to my column?

Thank you
stay tuned

[Viktor Tachev]

Hi Jairo,

 

The updated if-statements would look similar to the highlighted rows below:

 

function Filter (e) {

	if (e.field == "ProyectoM" && e.filter! == null) {
		var currentFilter = []
		var filtersCount = e.filter.filters [0] .length
		var value = e.filter.filters [0] .value
		if (e.sender.dataSource.filter ()! == undefined) {
		  currentFilter = e.sender.dataSource.filter (). filters // Retain the other filters
		}
		currentFilter.push ({field: "ProyectoM.DescripcionProyecto", operatot: "eq", value: value})
		e.preventDefault ()
		e.sender.dataSource.filter (currentFilter)
	  }
}

function FilterMenu (e) { 
	if (e.field === "ProyectoM") {
		$ (e.container) .find ('[type = "reset"]'). click (function (event) {
		  var filters = e.sender.dataSource.filter (). filters
		  for (let i = 0; i <= filters.length; i ++) {
			if (filters [i] .field == "ProyectoM.DescripcionProyecto") {
			  filters.splice (i, 1);
			  e.sender.dataSource.filter (filters)
			}
		  }
		})
  }
}

 

Let me know how this modification works for you.

 

Regards,
Viktor Tachev
Progress Telerik