This is a migrated thread and some comments may be shown as answers.
Grid Foreign Key - Have a foreign key to the same table
9 Answers 134 Views
This is a migrated thread and some comments may be shown as answers.
Manuel
Top achievements
Rank 1
Manuel asked on 27 Aug 2015, 08:41 AM

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

 

9 Answers, 1 is accepted

Sort by
0
Manuel
Top achievements
Rank 1
answered on 27 Aug 2015, 08:46 AM
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).
0
Vladimir Iliev
Telerik team
answered on 31 Aug 2015, 09:13 AM
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
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
0
Manuel
Top achievements
Rank 1
answered on 31 Aug 2015, 11:49 AM

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

 

 

 

0
Manuel
Top achievements
Rank 1
answered on 31 Aug 2015, 12:54 PM
Also, I noticed that the filter is being applied to ID instead of the name; how can I change this behavior?
0
Vladimir Iliev
Telerik team
answered on 02 Sep 2015, 10:45 AM
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
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
0
Manuel
Top achievements
Rank 1
answered on 02 Sep 2015, 07:21 PM

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

0
Accepted
Vladimir Iliev
Telerik team
answered on 04 Sep 2015, 11:18 AM
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
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
0
Manuel
Top achievements
Rank 1
answered on 20 Oct 2015, 03:18 PM

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

0
Manuel
Top achievements
Rank 1
answered on 21 Oct 2015, 02:02 PM

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

Tags
Grid
Asked by
Manuel
Top achievements
Rank 1
Answers by
Manuel
Top achievements
Rank 1
Vladimir Iliev
Telerik team
Share this question
or