Grid Foreign Key - Have a foreign key to the same table

[Manuel]

Hello! I have a model whose foreign key references itself. To provide some more context, I have a table in my database called "Categories", and this table may contain categories or sub-categories, depending on whether they have a reference to a "Non-sub-category" or not.

 

Having this into account, I am using a grid that contains a foreign key, as the following:

 

@(Html.Kendo().Grid<CategoriesViewModel>()
        .Name(​"CategoriesGrid")
        .Columns(columns =>
        {
            columns.Bound(c => c.Name).Width(50);
            columns.ForeignKey(c => c.ParentCategoryId,
                (System.Collections.IEnumerable)ViewData["Categories"], "CategoryId", "Name")
                .Filterable(filterable => filterable
                    .Extra(false)
                )
                .Width(50);
            columns.Command(command => command.Edit()).Width(50);
        })
        .ToolBar(toolbar => toolbar.Create())
        .Editable(e => e.Mode(GridEditMode.InLine))
        .DataSource(dataSource => dataSource
            .Ajax()
            .PageSize(5)
            .ServerOperation(false)
            .Model(model =>
                {
                    model.Id(a => a.CategoryId);
                    model.Field(a => a.ParentCategoryId).DefaultValue(null);
                })
            .Create(update => update.Action("CreateCategory", "Categories"))
            .Read(read => read.Action("GetCategories", "Categories"))
            .Update(update => update.Action("EditCategory", "Categories"))
        )

 The behaviour I desire is to add lines to this table and have the "ForeignKey" dropdown automatically updated. So for instance, if I add a category "Animal", when I am creating a new sub-category (e.g. a "Cat") I want to see already displayed "Animal" in my dropdown. I also want to be able to have an "Empty value" on the dropdown (like a placeholder), so I can indicate that the new category is a "Primary category" (i.e. a non-sub-category). I understand that the "ForeignKey" data is statically bound, and the only way I have right now to display the newly inserted data is to force a reload on the page.

 

Is not there any workarounds to update my "ForeignKey" dropdown on a grid change (Create or Edit)?

 Regards,

Manuel

 

[Manuel]

I forgot to mention: I really just want the dropdown to be refreshed. If I create a new record, I don't want to refresh the grid, since that would also update the position of the newly created record (i.e. some place else other than the first line).

[Vladimir Iliev]

Hello Manuel,

In current scenario I would suggest to try the following solution:

1) Include the text representation of the foreign key value into the view model. This can be done on the server side easily - please check the example below:

select new OrderViewModel
{
    OrderID = order.OrderID,
    OrderDate = order.OrderDate,
    //ForeignKey value:
    EmployeeID = order.EmployeeID,
    //custom field that holds the text representation
    EmployeeName = EmployeeRepository.One(employee => employee.EmployeeID == order.EmployeeID).Name,

On each create / update request you should also update the custom field above and return the created / updated record back to the client side in order this field to be updated in the DataSource as well.

2) Use client template to show the text from the custom field in the column:

columns.ForeignKey(p => p.EmployeeID, (System.Collections.IEnumerable)ViewData["employees"], "EmployeeID", "Name")
    .ClientTemplate("#=EmployeeName#")

3) Use custom editor for the column in which to dynamically request the data from the server. This can be done in the following way:

columns.Bound(p => p.EmployeeID)
    .ClientTemplate("#=EmployeeName#")
    .EditorTemplateName("EmployeeRemoteData");

Include the following editor inside "EmployeeRemoteData.cshtml" file under the "EditorTemplates" folder:

@model object

@(Html.Kendo().DropDownListFor(m => m)
    .DataValueField("EmployeeID")
    .DataTextField("Name")
    .DataSource(ds => ds.Read("Read_Employees", "Home"))
)

Regards,
Vladimir Iliev
Telerik

[Manuel]

Hello Vladimir,

Thank you for you answer. It all seems to be working fine! Just one more question: How can I pass data to my editor template? Since this is a self-referencing model, I don't want my newly created items to appear on the dropdown of their row. So I would like to pass its name/id in order to exclude it in my controller.

 Many thanks,

Manuel

 

 

 

[Manuel]

Also, I noticed that the filter is being applied to ID instead of the name; how can I change this behavior?

[Vladimir Iliev]

Hello Manuel,

You can pass current value to the server for filtering using the "Data" method of the DataSource "Read" operation - please check the example below:

@model object

<script>
    function additionalData() {
        var editor = $('#@ViewData.TemplateInfo.GetFullHtmlFieldName("")');
        var row = editor.closest("tr");
        var grid = row.closest("[data-role=grid]").getKendoGrid();
        var dataItem = grid.dataItem(row);

        return { EmployeeID: dataItem['@ViewData.TemplateInfo.GetFullHtmlFieldName("")'] };
    }
</script>

@(Html.Kendo().DropDownListFor(m => m)
     .DataTextField("Name")
     .DataValueField("EmployeeID")
     .DataSource(ds => ds.Read(read => read.Action("Read_Employees", "Home").Data("additionalData")))
)

Regards,
Vladimir Iliev
Telerik

[Manuel]

Thanks Vladimir, that worked for me. But I still couldn't fix the filter problem.

On a column that uses an editor template (remote dropdown) and a client template, the filter is being applied to ID instead of the name.How can I apply it to the name instead? I tried to follow the custom filter demo, using filterable.UI, but I could only return a list of Objects on my dropdown (could you please detail where this is a bind between name and Id, since I am using a ClientTemplate for the ID?)​

 Thanks,

Manuel

[Vladimir Iliev]

Hello Manuel,

This behavior is expected - in current case you should define custom filtering UI which to apply the filtering on the desired field. For more information you can check the Grid API client-side and this demo.

Regards,
Vladimir Iliev
Telerik

[Manuel]

Hello Vladimir,

 How about the groupable options? When I do a group by in my column, the IDs appear instead of the name. How can I show the name, since there is not a UI option for the groupable setting?

 Regards,

Manuel

[Manuel]

Hello Vladimir,

I ended up following an approach where I combine the ForeignKey with an editor template, which solved all my problems.
Thank you for your help,
Manuel