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

10 posts, 1 answers
  1. Manuel
    Manuel avatar
    11 posts
    Member since:
    Jul 2015

    Posted 27 Aug 2015 Link to this post

    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

     

  2. Manuel
    Manuel avatar
    11 posts
    Member since:
    Jul 2015

    Posted 27 Aug 2015 in reply to Manuel Link to this post

    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).
  3. UI for ASP.NET MVC is VS 2017 Ready
  4. Vladimir Iliev
    Admin
    Vladimir Iliev avatar
    2172 posts

    Posted 31 Aug 2015 Link to this post

    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
  5. Manuel
    Manuel avatar
    11 posts
    Member since:
    Jul 2015

    Posted 31 Aug 2015 in reply to Vladimir Iliev Link to this post

    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

     

     

     

  6. Manuel
    Manuel avatar
    11 posts
    Member since:
    Jul 2015

    Posted 31 Aug 2015 in reply to Manuel Link to this post

    Also, I noticed that the filter is being applied to ID instead of the name; how can I change this behavior?
  7. Vladimir Iliev
    Admin
    Vladimir Iliev avatar
    2172 posts

    Posted 02 Sep 2015 Link to this post

    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
  8. Manuel
    Manuel avatar
    11 posts
    Member since:
    Jul 2015

    Posted 02 Sep 2015 in reply to Vladimir Iliev Link to this post

    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

  9. Answer
    Vladimir Iliev
    Admin
    Vladimir Iliev avatar
    2172 posts

    Posted 04 Sep 2015 Link to this post

    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
  10. Manuel
    Manuel avatar
    11 posts
    Member since:
    Jul 2015

    Posted 20 Oct 2015 in reply to Vladimir Iliev Link to this post

    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

  11. Manuel
    Manuel avatar
    11 posts
    Member since:
    Jul 2015

    Posted 21 Oct 2015 in reply to Vladimir Iliev Link to this post

    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

Back to Top
UI for ASP.NET MVC is VS 2017 Ready