Restricting available items in a foreign key drop-down column based on another column value

[Lee]

I have an ASP.NET MVC project and one of the project's pages has a grid that includes two columns called "Company" and "Segment". The Segment column is a foreign key column that is bound to a list of possible values to convert the underlying ID that's stored in the database into a user-friendly drop-down list of names to pick from.

The problem I'm having is that the contents of the Segment foreign key drop-down should be filtered according to the value of the Company column and not quite sure how to get this working. The rows are edited using the inline edit mode in case that makes any difference.

To try and make this a bit more concrete, this is what the data bound to the segment foreign key column could look like:

CompanyID    SegmentID  SegmentName

1                       1                   One

1                       2                   Two

2                      3                   Three

2                      4                   Four

 

So on editing a row, the Segment column drop-down currently shows all four values, but it should only show the two items that are associated with the related company ID.

I've looked at using a remote data source on the foreign key column to determine whether I can use the option of providing additional arguments via the `Data` option, but the data source is only queried once, when the grid is first loaded rather than on each cell edit.

I've also looked at the `beforeEdit` and `edit` events but `beforeEdit` is documented as being fired before cell editors are created and `edit` only gives me information about the row that's being edited.

The ability to define a custom editor looks more promising, but this is an MVC project and so the only option there appears to be a custom editor template and I don't know how I would extract values from other parts of the row to provide to any remote data source URL.

Thanks in advance for any help or suggestions.

 

[Yanislav]

Hello Lee,

Thank you for writing to us.

If I understand correctly the scenario, in the application a Foreign Key column is declared, and its editor is a DropDownList whose values need to be filtered based on information from the currently edited row. What I can recommend you is to hook up for the BeforeEdit event.

.Events(ev=>ev.BeforeEdit("onBeforeEdit"))

Through the events model property, you can access the data item which is being edited. Since the BeforeEdit event is fired before the read method of the DropDownList which is the custom editor of the Foreign Key column, you can save the needed information (CompanyID) in a global JS variable.

    var param;
    function onBeforeEdit(e) {
        param = e.model.ProductID;
    }

As you can see in the above snippet now the 'param' variable holds a value from the currently edited row, and it could be used within the handler declared in the .Data() configuration of the Read method of the data source.

@(
 Html.Kendo().DropDownListFor(m => m)
        .DataSource(x=> x.Read(r => r.Action("ReadC", "Home").Data("additionalData")))

    function additionalData(e) {
        return {
            productID:param
        }
    }

Thus you can send additional parameters with the Read request of the custom editor.

 

Regards,
Yanislav
Progress Telerik