I have an in-cell editable grid grid in which the values available in a grid foreign key column should be filtered by an input a user has previously selected. In our case a user picks a location from a kendo dropdown (outside of the grid) and that value filters what products should appear in the product foreign key column.

The general idea is that location 1 might have products A, B, and C. But Location 2 might only have products C and D. If the user picks location 1 then the multiselect bound to the foreign key column should only show A, B, and C, and if the user picks location two then they only see C and D when the dropdown is rendered.

I've seen examples where you change the editor template for Foreign Key to bind the dropdown to a remote datasource. In that case the read passes in whatever data the server needs for filtering (location id in our case) as data to the controller read call (https://www.telerik.com/forums/filtered-foreign-key-drop-down-list-based-on-another-key). The server then returns the filtered list of select list items which are bound to the dropdown. 

We'd really like to avoid the case where every time a user opens the dropdown we hit the server. Is there any way to do this filtering on the client side?

I'm envinsioning a case where we: 

• Send down all of the associations of locationId to productId and bind this to a javascript object
• Bind the column to a list of all possible products (so the dropdown template on the column object has all possible options)
• When the user clicks on the cell and the grid renders the dropdown we (in a javascript function) remove the values that aren't valid for the existing location 

Is there an event we could bind to, or a way we could customize the editor template to achieve our desired result, specifically to filter the items in the dropdown without binding to a remote datasource?

Thanks!

Hello Matt,

The ForeignKey column needs all values for the respective field that will be bound to the dropdown editor. For this reason, I would suggest using a custom editor. You would need to customize it further to match the desired functionality. 

I have customized the custom editor demo do show a possible approach. The available values in the DropDownList for selection of a Category would change based on the selection made in a DropDownList, outside of the Grid. In the example, above the Grid, a Kendo DropdownList is available to select the location, for which the data in the grid will be displayed. Upon selection of location the Grid's data is filtered:

function onLocationChange(e){
        	var location = e.sender.value();
          var grid = $("#grid").getKendoGrid();
          grid.dataSource.filter({field: "Location", operator: "contains", value: location})
        }

For the Grid, you could store the available Categories for each location in a variable with a single call to the server and load the respective categories based on the selection of location. Whenever editing is initiated check whether the field being edited is the Category field, get a reference to the DropDownList and update the dataSource with the available options for that Location.

var categories = ({
          Location1:
          [{"CategoryID": 1, "CategoryName": "Beverages"},
           {"CategoryID": 2, "CategoryName": "Condiments"},
           {"CategoryID": 3, "CategoryName": "Confections"}
          ],
          Location2:
          [{"CategoryID": 2, "CategoryName": "Condiments"},
           {"CategoryID": 3, "CategoryName": "Confections"}
          ]
        })

function onGridEdit(e){
          var location = $("#location").getKendoDropDownList().value();
          var ddl = e.container.find("[data-role='dropdownlist'][name='Category']").getKendoDropDownList();

          if(location == 1){
            ddl.dataSource.data(categories.Location1);
          } else {
            ddl.dataSource.data(categories.Location2);
          }
        }

A runnable dojo with the above suggestion implemented is available here, for you to review.

I hope the above helps you implement the desired functionality. Let me know if you have any questions.

Regards,
Aleksandar
Progress Telerik