We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy.
Unfortunately, activation email could not send to your email. Please try again.

is it possible to sort on a foreign key column?

Thread ID:

Created:

Updated:

Platform:

Replies:

128147 Jan 4,2017 10:36 PM Sep 28,2017 08:39 AM ASP.NET MVC 11
loading
Tags: Grid
R Brian Lindahl
Asked On January 4, 2017 10:36 PM

i have numerous grids with foreign key columns. when these columns are sorted, the sort order is sorting on the ID column in the datasource, not on the displayed value. I can understand this behavior, as the sorting is done server-side (urladaptor). However, this isn't something the user should have to understand - all they know is that the values aren't sorted as they would expect.

is there a way to sort a foreign key column based on the displayed values?

Mani Sankar Durai [Syncfusion]
Replied On January 6, 2017 07:26 AM

Hi Brian, 

Thanks for contacting Syncfusion support. 

We have analyzed your query and we suggest you to use ForeignKeyAdaptor to sort a foreign key column based on the displayed values
 
We have also prepared a sample that can be downloaded from the below link. 

Please refer the below code example. 
[Index.cshtml] 
@(Html.EJ().Grid<MvcApplication.Models.OrdersView>("Edittemplate") 
        .Datasource((IEnumerable<object>)ViewData["Getdata"]) 
         .ClientSideEvents(e => e.Load("onLoad")) 
        .AllowPaging() 
        .AllowSorting() 
        .Columns(col => 
        { 
             
col.Field("FirstName").HeaderText("Name").Width(90).Add(); 
                     }) 
    ) 
 
    <script type="text/javascript"> 
      
        var employeeData =@Html.Raw(Json.Encode(@ViewData["Employee"])); 
       var obj = [{  dataSource: employeeData, foreignKeyField: "EmployeeID", foreignKeyValue: "FirstName" }]; 
         function onLoad(args) { 
             this.model.dataSource.adaptor = new ej.ForeignKeyAdaptor(obj,"remoteSaveAdaptor"); 
            } 
         
    </script> 
 
 

Refer the documentation link. 

Please let us know if you need further assistance. 

Regards, 
Manisankar  Durai. 


R Brian Lindahl
Replied On January 13, 2017 05:15 PM

I've been unable to get this to work. I started with a grid like this:

@(Html.EJ().Grid<BreadfarmBMS.Models.Order>("GridContainer")
        .Datasource(ds => ds.URL("/OrdersEdit/DataSource")
        .InsertURL("/OrdersEdit/Insert")
        .UpdateURL("/OrdersEdit/Update")
        .RemoveURL("/OrdersEdit/Remove")
      .Adaptor(AdaptorType.UrlAdaptor))
      .Query(queryString)

      .ClientSideEvents(c => {
        c.EndAdd("endadd");
        c.QueryCellInfo("queryCellInfo");
      })
...
  col.Field("CustomerID")
       .HeaderText("Customer")
       .ForeignKeyField("CustomerID")
      .ForeignKeyValue("Name")
       .DataSource((IEnumerable<object>)Model.Customers)
       .Add();

and changed it to:

@(Html.EJ().Grid<BreadfarmBMS.Models.Order>("GridContainer")
        .Datasource(ds => ds.URL("/OrdersEdit/DataSource")
        .InsertURL("/OrdersEdit/Insert")
        .UpdateURL("/OrdersEdit/Update")
        .RemoveURL("/OrdersEdit/Remove")
      .Adaptor(AdaptorType.UrlAdaptor))
      .Query(queryString)

      .ClientSideEvents(c => {
        c.Load("onLoad");
        c.EndAdd("endadd");
        c.QueryCellInfo("queryCellInfo");
      })
...
  col.Field("Name")
       .HeaderText("Customer")
       .Add();

<script type="text/javascript">
  function onLoad(args) {
    var custData = @Html.Raw(Json.Encode(Model.Customers));
    this.model.dataSource.adaptor = new ej.ForeignKeyAdaptor(
      [{ dataSource: custData, foreignKeyField: "CustomerID", foreignKeyValue: "Name" }]
      , "urlAdaptor");
  }
...
</script>

Did I get things right? All I get with this particular code is a timeout exception in the browser's debugger, and a page without any sign of a grid.

Mani Sankar Durai [Syncfusion]
Replied On January 16, 2017 06:31 AM

Hi Brian, 

We have analyzed your query and found that you are using UrlAdaptor to bind the value for the foreignKey column with foreignKeyAdaptor. Since foreignKeyAdaptor will work only with the local datasource. So we suggest you to use RemoteSaveAdaptor instead of using UrlAdaptor. Refer to the below sample, 
 
[Index.cshtml] 
@(Html.EJ().Grid<MvcApplication.Models.OrdersView>("Edittemplate") 
        .Datasource(ds => ds.Json((IEnumerable<object>)ViewData["Getdata"]).UpdateURL("/Home/Update").InsertURL("/Home/Insert").RemoveURL("/Home/Remove").Adaptor(AdaptorType.RemoteSaveAdaptor)) 
        .ClientSideEvents(e => e.Load("onLoad")) 
        .Columns(col => 
        { 
            ............................... 
            col.Field("FirstName").HeaderText("Name").Width(90).Add(); 
         }) 
    ) 
 
    <script type="text/javascript"> 
       var employeeData =@Html.Raw(Json.Encode(@ViewData["Employee"])); 
        var obj = [{  dataSource: employeeData, foreignKeyField: "EmployeeID", foreignKeyValue: "FirstName" }]; 
        function onLoad(args) { 
            this.model.dataSource.adaptor = new ej.ForeignKeyAdaptor(obj,"remoteSaveAdaptor"); 
            } 
   </script> 
 
 
 
The above code example illustrates how to render the foreignkey column using ej.ForeignKeyAdaptor. ForeignKeyAdaptor accepts two parameters. First parameter accepts the array of objects, which has dataSource, foreignKeyField and foreignKeyValue for the virtual column (“FirstName”) in Grid. Whereas the second parameter holds the type of secondary adaptor (either JsonAdaptor or remoteSaveAdaptor). 
 
We have also prepared a sample that can be downloaded from the below link. 
Also please refer the documentation link. 
 
Please let us know if you need further assistance. 
 
Regards, 
Manisankar Durai. 


Dave Lewis
Replied On September 20, 2017 04:07 PM

I would like to do the same thing as this.  However, I am using the ASP.NET Core Grid control.  How would it be possible to sort on a foreign key column based on displayed values (rather than the ID column of the datasource) in ASP.NET Core, instead of MVC?


Mani Sankar Durai [Syncfusion]
Replied On September 21, 2017 08:45 AM

Hi Dave, 

We have prepared a sample based on your requirement by sorting the foreign key columns based on displayed data instead of ID of the column dataSource. The sample has been prepared in Asp.Net Core and available under the below link. 
Refer the code example 
<ej-grid id="FlatGrid" allow-paging="true" load="onLoad" allow-sorting="true" > 
    <e-datamanager json="(IEnumerable<object>)ViewBag.data" adaptor="remoteSaveAdaptor" /> 
      <e-columns> 
... 
          <e-column field="FirstName" width="75"></e-column> 
      </e-columns> 
    
</ej-grid> 
 
<script type="text/javascript"> 
 
    var employeeData =@Html.Raw(Json.Serialize(ViewBag.employee));  
        var obj = [{  dataSource: employeeData, foreignKeyField: "EmployeeID", foreignKeyValue: "FirstName" }]; 
        function onLoad(args) { 
            this.model.dataSource.adaptor = new ej.ForeignKeyAdaptor(obj,"remoteSaveAdaptor"); 
            } 
</script> 

The above code example illustrates how to render the foreignkey column using ej.ForeignKeyAdaptor. ForeignKeyAdaptor accepts two parameters. First parameter accepts the array of objects, which has dataSource, foreignKeyField and foreignKeyValue for the virtual column (“FirstName”) in Grid. Whereas the second parameter holds the type of secondary adaptor (either JsonAdaptor or remoteSaveAdaptor) 

Please let us know if you need further assistance. 

Regards, 
Manisankar Durai. 


Dave Lewis
Replied On September 24, 2017 03:33 PM

I am not able to get this to work for me.  I have used the code as instructed, but it does not work in my grid.  The column just comes up empty without values.  As a note, my page has two separate grids on it.  Does having two grids on the same page cause an issue with this functionality?  I only need this foreign key sorting behavior on one of the grids.  As a secondary note, my grids are editable.  I noticed that the sample does not have editing capability.  Is this a problem, because I still need the IDs when I save records.  I just need the name or description for viewing and sorting only.


Mani Sankar Durai [Syncfusion]
Replied On September 25, 2017 08:04 AM

Hi Dave, 

We have checked your query and based on your requirement we have prepared a sample that can be downloaded from the below link. 
In this sample we rendered two grids and in one grid we have used foreign Key columns. We have used ForeignKeyAdaptor to sort the foreign key column based on text displayed but when we edit the row it will pass the id to the server side and records will be updated based on that.  
Also you have mentioned that you have not bound the values for foreign key column. So we suspect that you have not used ForeignKeyAdaptor in the load event of grid.  
Refer the code example 
<ej-grid id="FlatGrid" allow-paging="true" load="onLoad" allow-sorting="true" > 
    <e-datamanager json="(IEnumerable<object>)ViewBag.data" insert-url="/Home/NormalInsert" update-url="/Home/NormalUpdate" remove-url="/Home/NormalDelete"  adaptor="remoteSaveAdaptor" /> 
... 
    <e-columns> 
        ... 
          <e-column field="FirstName" width="75"></e-column>   //firstName is field of foreign key. 
          <e-column field="ShipCity" header-text="Ship City" width="75"></e-column> 
      </e-columns> 
    
</ej-grid> 
 
<br /> 
<ej-grid id="Grid" allow-paging="true" load="onLoad" allow-sorting="true"> 
    ...           //second grid 
 
</ej-grid> 
 
<script type="text/javascript"> 
 
    var employeeData =@Html.Raw(Json.Serialize(ViewBag.employee));  
        var obj = [{  dataSource: employeeData, foreignKeyField: "EmployeeID", foreignKeyValue: "FirstName" }]; 
        function onLoad(args) {   // bind using load event in grid. 
            this.model.dataSource.adaptor = new ej.ForeignKeyAdaptor(obj,"remoteSaveAdaptor");  
            } 
</script> 
 

If you still face the issue please get back to us with the following details. 
1.       Share the screenshot/ video of the issue that you have faced.  
2.       Share the exact issue details that you have faced. 
3.       Share the full grid and controller page. 
4.       Share the Syncfusion Essential studio version details. 
5.       If possible please reproduce the issue in the above attached sample 
The provided information will help us to analyze the issue and provide you the response as early as possible. 

Please let us know if you need further assistance. 

Regards, 
Manisankar Durai. 


Dave Lewis
Replied On September 26, 2017 09:49 AM

I am still not able to get this to work.  The foreign key column still comes up empty.  I have even tried removing both of my grids from the parent view and putting each of them into partial views, which didn't make a difference.

The following is the ActionResult in my controller that sets up my data sources for the two grids:.

public async Task<ActionResult> Admin()
        {
            var dt = DateTime.Now;
            ViewBag.DataSourceCats = await _context.Categories.Where(c => c.EndDate > dt).OrderBy(c => c.Description).ToListAsync();
            ViewBag.DataSourceItems = await _context.Items.Where(i => i.EndDate > dt).ToListAsync();

            return View();
        }

This is my view:

@{
    ViewBag.Title = "Checklist (Admin)";
    Layout = "~/Views/Shared/_Layout.cshtml";
}

<div id="divAdminType">
    <h4>
        <select id="adminType">
            <option value="Categories">Categories</option>
            <option value="Items" selected="selected">Items</option>
        </select>
    </h4>
</div>
<div id="divGrid" class="div-grid">
    <div id="divGridCat" class="is-hidden">
        <ej-grid id="gridCat" allow-paging="true" allow-sorting="true" allow-resizing="true" enable-alt-row="true" allow-scrolling="false">
            <e-datamanager json="(IEnumerable<Category>)ViewBag.DataSourceCats" insert-url="/Home/InsertCat" update-url="/Home/UpdateCat" 
                remove-url="/Home/DeleteCat" adaptor="remoteSaveAdaptor" />
            <e-page-settings page-size="20" />
            <e-edit-settings allow-adding="true" allow-editing="true" allow-deleting="true" edit-mode="Normal" show-delete-confirm-dialog="true" />
            <e-toolbar-settings show-toolbar="true" toolbar-items='@new List<string> {"add","edit","delete","update","cancel"}' />
            <e-columns>
                <e-column field="CategoryId" header-text="Category ID" width="100" is-primary-key="true" is-identity="true" />
                <e-column field="Description" header-text="Description" edit-type="StringEdit" validation-rules='@(new Dictionary<string,object> { {"required",true}, {"maxlength",50} })' />
            </e-columns>
            <e-sort-settings>
                <e-sorted-columns>
                    <e-sorted-column field="Description" direction="Ascending" />
                </e-sorted-columns>
            </e-sort-settings>
        </ej-grid>
    </div>
    <div id="divGridItem">
        <ej-grid id="gridItem" allow-paging="true" allow-sorting="true" allow-resizing="true" enable-alt-row="true" allow-scrolling="false" create="resizeGrid">
            <e-datamanager json="(IEnumerable<Item>)ViewBag.DataSourceItems" insert-url="/Home/InsertItem" update-url="/Home/UpdateItem" 
                remove-url="/Home/DeleteItem" adaptor="remoteSaveAdaptor" />
            <e-page-settings page-size="20" />
            <e-edit-settings allow-adding="true" allow-editing="true" allow-deleting="true" edit-mode="Normal" show-delete-confirm-dialog="true" />
            <e-toolbar-settings show-toolbar="true" toolbar-items='@new List<string> {"add","edit","delete","update","cancel"}' />
            <e-columns>
                <e-column field="ItemId" header-text="Item ID" width="80" is-primary-key="true" is-identity="true" />
                <e-column field="Description" header-text="Category" width="200" validation-rules='@(new Dictionary<string,object> { {"required",true} })' />
                <e-column field="OutreachText" header-text="Outreach Text" edit-type="StringEdit" validation-rules='@(new Dictionary<string,object> { {"required",true}, {"maxlength",255} })' />
                <e-column field="ClientText" header-text="Client Text" edit-type="StringEdit" validation-rules='@(new Dictionary<string,object> { {"maxlength",255} })' />
            </e-columns>
        </ej-grid>
    </div>
</div>

<script type="text/javascript" src="@Url.Content("~/lib/jquery/dist/jquery.js")"></script>
<script type="text/javascript">
    //$(document).ready(function () { showGrid("Items"); });

    $(window).resize(function () { resizeGrid(); });

    $("#adminType").on("change", function () { showGrid(this.value); })

    var catData = @Html.Raw(Json.Serialize(ViewBag.DataSourceCats));
    var obj = [{  dataSource: catData, foreignKeyField: "CategoryId", foreignKeyValue: "Description" }];
    function onLoad(args) {   // bind using load event in grid.
        this.model.dataSource.adaptor = new ej.ForeignKeyAdaptor(obj,"remoteSaveAdaptor");
}

    function showGrid(val) {
        if (val == "Categories") {
            $("#divGridCat").removeClass("is-hidden");
            $("#divGridItem").addClass("is-hidden");

            //document.getElementById("divGridCat").style.display = "block";
            //document.getElementById("divGridItem").style.display = "none";
        }
        else {
            //$("#divGridCat").addClass("is-hidden");
            //$("#divGridItem").removeClass("is-hidden");

            location.reload();

            //document.getElementById("divGridCat").style.display = "none";
            //document.getElementById("divGridItem").style.display = "block";
            //var gridObj = $("#gridItem").data("ejGrid");
            //gridObj.refreshContent();
        }
        resizeGrid();
    }

    function resizeGrid() {
        var elem = ($("#gridCat").is(":hidden") ? "#gridItem" : "#gridCat");
        var gridObj = $(elem).data("ejGrid");
        var scrollWidth = $("#divGrid").width();
        //var scrollHeight = (($("#divGrid").height()) - 120) - ($(".e-gridheader").outerHeight()) - ($(".e-pager").outerHeight());
        var scrollHeight = ($("#divGrid").height() - 140);
        gridObj.option({ allowScrolling: true, scrollSettings: { width: scrollWidth, height: scrollHeight } });
    }
</script>

As an fyi, I am using Syncfusion Essential Studio version 15.3.0.29.

Mani Sankar Durai [Syncfusion]
Replied On September 27, 2017 08:17 AM

Hi Dave, 

We have checked the code example and found that you have not set the load event for the grid but defined the function. This will not to trigger the load event and not to bind the data for the foreign key columns. So we suggest you to set the load event for the corresponding grid where you have to bind the foreign key column. 
Refer the code example 
div id="divGrid" class="div-grid"> 
    <div id="divGridCat" class="is-hidden"> 
        <ej-grid id="gridCat" allow-paging="true" allow-sorting="true" allow-resizing="true" enable-alt-row="true" load="onLoad" allow-scrolling="false"> 
... 
        </ej-grid> 
    </div> 
    <div id="divGridItem"> 
        <ej-grid id="gridItem" allow-paging="true" allow-sorting="true" allow-resizing="true" enable-alt-row="true" allow-scrolling="false" create="resizeGrid"> 
            ... 
        </ej-grid> 
    </div> 
</div> 
 
<script type="text/javascript"> 
... 
 
    var catData = @Html.Raw(Json.Serialize(ViewBag.DataSourceCats)); 
    var obj = [{  dataSource: catData, foreignKeyField: "CategoryId", foreignKeyValue: "Description" }]; 
    function onLoad(args) {   // bind using load event in grid. 
        this.model.dataSource.adaptor = new ej.ForeignKeyAdaptor(obj,"remoteSaveAdaptor"); 
    } 
 
...  

Refer the documentation link. 

Please let us know if you need further assistance. 

Regards, 
Mansankar Durai. 


Dave Lewis
Replied On September 27, 2017 09:19 AM

No, I am afraid that is not the problem.  I had the "load" event wired up initially, but I removed all of the code when it was not working and took out all the sorting.  I just forgot to put it back into place when I sent the sample of my code.  I apologize for that.  I have added the event back in as directed and it still does not work.  


Mani Sankar Durai [Syncfusion]
Replied On September 28, 2017 08:39 AM

Hi Dave, 

We have checked the code example provided by you and we suspect that you are binding the same table data for the grid and for the foreign key columns.  
Since for the grid you have bound ViewBag.DataSourceCats that contains Categories table data. Also for the foreign key column you have bind the same ViewBag.DataSourceCats. So in the Categories table we suspect that the Description field is not there. This is the reason data is not bound for foreign key column.  
Also please ensure that whether the foreign key column binding table contains the Description field. (i.e in dataSource: catData the field Description is available). We have to bind the foreign key columns like one of the field from the grid on one table should be the primary key for another table. 
We have also prepared a sample based on your provided code that can be available from the below link. 
Refer the code example 
<ej-grid id="FlatGrid" load="onLoad" allow-paging="true" allow-sorting="true" allow-resizing="true" enable-alt-row="true" allow-scrolling="false"> 
    <e-datamanager json="(IEnumerable<object>)ViewBag.data" insert-url="/Home/NormalInsert" update-url="/Home/NormalUpdate" remove-url="/Home/NormalDelete" adaptor="remoteSaveAdaptor" /> 
    ...    //ViewBag.Data is the datasource for the grid that contains EmployeeID as one of the field 
    <e-columns> 
... 
        <e-column field="FirstName" width="75" edit-type="String" validation-rules='@(new Dictionary<string,object> { {"required",true}, {"maxlength",50} })' /> 
//Here FirstName is the foreignKeyValue of another table   
    </e-columns> 
... 
</ej-grid> 
 
<br /> 
<ej-grid id="Grid" allow-paging="true" allow-sorting="true" allow-resizing="true" enable-alt-row="true" allow-scrolling="false" create="resizeGrid"> 
    ... 
 
</ej-grid> 
 
<script type="text/javascript"> 
    var employeeData =@Html.Raw(Json.Serialize(ViewBag.employee));  //ViewBag.employee is the another table data that contains EmployeeID as primary key. 
 
        var obj = [{  dataSource: employeeData, foreignKeyField: "EmployeeID", foreignKeyValue: "FirstName" }]; 
        function onLoad(args) { 
            this.model.dataSource.adaptor = new ej.ForeignKeyAdaptor(obj,"remoteSaveAdaptor"); 
            } 
</script> 
 
[HomeController.cs] 
public IActionResult Index() 
        { 
            if (order.Count() == 0) 
                BindDataSource(); 
            ViewBag.data = order;       //Order table data 
           if (foreignData.Count() == 0) 
                BindForeignData(); 
            ViewBag.employee = foreignData.Where(c => c.EmployeeID > 1).OrderBy(c => c.FirstName).ToList();    //employee table data 
            return View(); 
        } 


If you still face the issue please provide us the following details 
1.       Share the video of the issue.  
2.       Are you using foreign key column for the grid 1? 
3.       Does description field is the foreign key value and does it is available in catData on onLoad event? 
4.       If so please send the dataSource for the grid, for the foreign key column table. 
The provided information will help us to analyze the issue and provide you the response as early as possible. 

Please let us know if you need further assistance. 

Regards, 
Manisankar Durai 



CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.

;