Foreign Key Column

8 Mar 202218 minutes to read

Foreign key column can be enabled by using DataSource, ForeignKeyField and ForeignKeyValue properties of Column.

In the following example, Employee Name is a foreign column which shows FirstName column from foreign data.

@Html.EJS().Grid("ForeignKey").DataSource((IEnumerable<object>)ViewBag.dataSource).Columns(col =>
{
  col.Field("OrderID").HeaderText("Order ID").IsPrimaryKey(true).Width("120").ValidationRules(new { required = true }).TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
  col.Field("EmployeeID").ForeignKeyField("EmployeeID").ForeignKeyValue("FirstName").DataSource((IEnumerable<object>)ViewBag.foreign).HeaderText("First Name").Width("150").ValidationRules(new { required = true }).Add();
  col.Field("Freight").HeaderText("Freight").Width("120").ValidationRules(new { required = true }).Format("C2").EditType("numericedit").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
  col.Field("ShipName").HeaderText("Ship Name").Width("150").Add();
  col.Field("ShipCity").HeaderText("Ship City").EditType("dropdownedit").Width("150").Add();

}).Render()
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace EJ2CoreSampleBrowser.Models
{
    public class EmployeeView
    {
        public EmployeeView()
        {

        }
        public EmployeeView(int EmployeeID, string FirstName, string LastName, string Title, DateTime BirthDate, DateTime HireDate, int ReportsTo, string Address, string PostalCode, string Phone, string City, string Country)
        {
            this.EmployeeID = EmployeeID;
            this.FirstName = FirstName;
            this.LastName = LastName;
            this.Title = Title;
            this.BirthDate = BirthDate;
            this.HireDate = HireDate;
            this.ReportsTo = ReportsTo;
            this.Address = Address;
            this.PostalCode = PostalCode;
            this.Phone = Phone;
            this.City = City;
            this.Country = Country;

        }
        public int EmployeeID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Title { get; set; }
        public DateTime BirthDate { get; set; }
        public DateTime HireDate { get; set; }

        public int ReportsTo { get; set; }

        public string Address { get; set; }
        public string PostalCode { get; set; }
        public string Phone { get; set; }
        public string City { get; set; }
        public string Country { get; set; }
        public static List<EmployeeView> GetAllRecords()
        {
            List<EmployeeView> Emp = new List<Models.EmployeeView>();
            Emp.Add(new EmployeeView(1, "Nancy", "Davolio", "Sales Representative", new DateTime(1948, 12, 08), new DateTime(1992, 05, 01), 2, "507 - 20th Ave. E.Apt. 2A ", " 98122", "(206) 555-9857 ", "Seattle ", "USA"));
            Emp.Add(new EmployeeView(2, "Andrew", "Fuller", "Vice President, Sales", new DateTime(1952, 02, 19), new DateTime(1992, 08, 14), 4, "908 W. Capital Way", "98401 ", "(206) 555-9482 ", "Kirkland ", "USA"));
            Emp.Add(new EmployeeView(3, "Janet", "Leverling", "Sales Representative", new DateTime(1963, 08, 30), new DateTime(1992, 04, 01), 3, " 4110 Old Redmond Rd.", "98052 ", "(206) 555-8122", "Redmond ", "USA "));
            Emp.Add(new EmployeeView(4, "Margaret", "Peacock", "Sales Representative", new DateTime(1937, 09, 19), new DateTime(1993, 05, 03), 6, "14 Garrett Hill ", "SW1 8JR ", "(71) 555-4848 ", "London ", "UK "));
            Emp.Add(new EmployeeView(5, "Steven", "Buchanan", "Sales Manager", new DateTime(1955, 03, 04), new DateTime(1993, 10, 17), 8, "Coventry HouseMiner Rd. ", "EC2 7JR ", " (206) 555-8122", "Tacoma ", " USA"));
            Emp.Add(new EmployeeView(6, "Michael", "Suyama", "Sales Representative", new DateTime(1963, 07, 02), new DateTime(1993, 10, 17), 2, " 7 Houndstooth Rd.", " WG2 7LT", "(71) 555-4444 ", "London ", "UK "));
            Emp.Add(new EmployeeView(7, "Robert", "King", "Sales Representative", new DateTime(1960, 05, 29), new DateTime(1994, 01, 02), 7, "Edgeham HollowWinchester Way ", "RG1 9SP ", "(71) 555-5598 ", "London ", " UK"));
            Emp.Add(new EmployeeView(8, "Laura", "Callahan", "Inside Sales Coordinator", new DateTime(1958, 01, 09), new DateTime(1994, 03, 05), 9, "722 Moss Bay Blvd. ", "98033 ", " (206) 555-3412", "Seattle ", "USA "));
            Emp.Add(new EmployeeView(9, "Anne", "Dodsworth", "Sales Representative", new DateTime(1966, 01, 27), new DateTime(1994, 11, 15), 5, "4726 - 11th Ave. N.E. ", "98105 ", "(71) 555-5598 ", " London", "UK "));
            return Emp;
        }
    }
}

For remote data, the sorting and grouping is done based on ForeignKeyField instead of ForeignKeyValue.
If ForeignKeyField is not defined, then the column uses Field.

Use edit template in foreignkey column

By default, foreign key column uses dropdown component for editing. You can render other than the dropdown by using the Edit property of Column. The following example demonstrates the way of using edit template in foreign column.

In the following example, The Employee Name is a foreign key column and while editing, AutoComplete component is rendered instead of DropDownList.

@Html.EJS().Grid("Grid").DataSource((IEnumerable<object>)ViewBag.dataSource).Columns(col =>
{
    col.Field("OrderID").HeaderText("Order ID").IsPrimaryKey(true).Width("120").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
    col.Field("EmployeeID").HeaderText("Employee Name").Width("150").ForeignKeyValue("FirstName").DataSource(ViewBag.ForeignData).Edit(new { create = "create", read = "read", destroy = "destroy", write = "write" }).Add();

    col.Field("Freight").HeaderText("Freight").Width("120").Format("C2").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
    col.Field("ShipName").HeaderText("Ship Name").Width("150").Add();
    col.Field("ShipCountry").HeaderText("Ship Country").Width("150").EditType("dropdownedit").Add();

}).Toolbar(new List<string>() { "Add", "Edit", "Delete", "Update", "Cancel" }).AllowPaging().EditSettings(edit => { edit.AllowAdding(true).AllowEditing(true).AllowDeleting(true).Mode(Syncfusion.EJ2.Grids.EditMode.Normal);}).Render()

<script>
    var autoComplete;
    var employeeData = @Html.Raw(Json.Encode(ViewBag.ForeignData));

    function create() { // to create input element
        return document.createElement('input');
    }

    function read () { // return edited value to update data source
       var value = new ej.data.DataManager(employeeData).executeLocal(new ej.data.Query().where('FirstName', 'equal', autoComplete.value));
       return value.length && value[0]['EmployeeID']; // to convert foreign key value to local value.
    }

    function destroy () { // to destroy the custom component.
       autoComplete.destroy();
    }

    function write (args) { // to show the value for custom component
       autoComplete = new ej.dropdowns.AutoComplete({
            dataSource: employeeData,
            fields: { value: args.column.foreignKeyValue },
            value: args.foreignKeyData[0][args.column.foreignKeyValue]
        });
       autoComplete.appendTo(args.element);
}
</script>
public IActionResult Index()
{
    ViewBag.DataSource = OrderDetails.GetAllRecords();
    ViewBag.ForeignData = EmployeeDetails.GetAllRecords();
    return View();
}

Customize filter UI in foreignkey column

You can create your own filtering UI by using Filter property of Column. The following example demonstrates the way to create a custom filtering UI in the foreign column.

In the following example, The Employee Name is a foreign key column. DropDownList is rendered using Filter UI.

@{
    Object filterTemplate = new Object();
    filterTemplate = (new { create = "create", read = "read", write = "write" });
}


@Html.EJS().Grid("Grid").DataSource((IEnumerable<object>)ViewBag.DataSource).Columns(col =>
{
    col.Field("OrderID").HeaderText("Order ID").Width("120").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
    col.Field("EmployeeID").HeaderText("Employee Name").Width("150").Filter(new { ui = filterTemplate }).ForeignKeyValue("FirstName").DataSource(ViewBag.ForeignData).Add();
    col.Field("OrderDate").HeaderText("Order Date").Width("130").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Format("yMd").Add();
    col.Field("Freight").HeaderText("Freight").Width("120").Format("C2").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
    col.Field("ShipCountry").HeaderText("Ship Country").Width("120").Add();

}).AllowFiltering().FilterSettings(filter => filter.Type(Syncfusion.EJ2.Grids.FilterType.Menu)).Render()

<script>
    var dropInstance;
    var fEmployeeData = @Html.Raw(Json.Encode(@ViewData["ForeignData"]));
    function create (args) {
        var flValInput = document.createElement('input');
        flValInput.classList.add('flm-input');
        args.target.appendChild(flValInput);
        dropInstance = new ej.dropdowns.DropDownList({
            dataSource: new ej.data.DataManager(fEmployeeData),
            fields: { text: 'FirstName', value: 'EmployeeID' },
            placeholder: 'Select a value',
            popupHeight: '200px'
        });
        dropInstance.appendTo(flValInput);
    }
    function write (args){
        dropInstance.text = args.filteredValue || '';
    }
    function read (args) {
        args.fltrObj.filterByColumn(args.column.field, args.operator, dropInstance.text);
    }
</script>
public IActionResult Index()
{
    ViewBag.DataSource = OrderDetails.GetAllRecords();
    ViewBag.ForeignData = EmployeeDetails.GetAllRecords();
    return View();
}

Use filter bar template in foreignkey column

You can use the filter bar template in foreign key column by defining the FilterBarTemplate property of Column. The following example demonstrates the way to use filter bar template in foreign column.

In the following example, The Employee Name is a foreign key column. This column header shows the custom filter bar template and you can select filter value by using the DropDown options.

@{
    Object filterTemplate = new Object();
    filterTemplate = (new { create = "create", read = "read", write = "write" });
}


@Html.EJS().Grid("Grid").DataSource((IEnumerable<object>)ViewBag.DataSource).Columns(col =>
{
    col.Field("OrderID").HeaderText("Order ID").Width("120").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
    col.Field("EmployeeID").HeaderText("Employee Name").Width("150").FilterBarTemplate(filterTemplate).ForeignKeyField("EmployeeID").ForeignKeyValue("FirstName").DataSource((IEnumerable<object>)ViewBag.ForeignData).Add();
    col.Field("OrderDate").HeaderText("Order Date").Width("130").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Format("yMd").Add();
    col.Field("Freight").HeaderText("Freight").Width("120").Format("C2").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
    col.Field("ShipCountry").HeaderText("Ship Country").Width("120").Add();

}).AllowFiltering().Render()

<script>

function create (args) {
    return document.createElement('input', { className: 'flm-input' });
}

function write (args) {
    var fEmployeeData = @Html.Raw(Json.Encode(ViewBag.ForeignData));
    fEmployeeData.splice(0, 0, { 'FirstName': 'All' }); // for clear filtering
    var dropInstance = new ej.dropdowns.DropDownList({
        dataSource: new ej.data.DataManager(fEmployeeData),
        fields: { text: 'FirstName' },
        placeholder: 'Select a value',
        popupHeight: '200px',
        index: 0,
        change: (args) => {
            var grid = document.getElementById("Grid").ej2_instances[0];
            if (args.value !== 'All') {
                grid.filterByColumn('EmployeeID', 'equal', args.value);
            }
            else {
                grid.clearFiltering("EmployeeID");
            }
        }
    });
    dropInstance.appendTo(args.element);
}
</script>
public IActionResult Index()
{
    ViewBag.DataSource = OrderDetails.GetAllRecords();
    ViewBag.ForeignData = EmployeeDetails.GetAllRecords();
    return View();
}

Perform aggregation in foreignkey column

Default aggregations are not supported in a foreign key column. You can achieve aggregation for the foreign key column by using the custom aggregates. The following example demonstrates the way to achieve aggregation in foreign key column.

In the following example, The Employee Name is a foreign key column and the aggregation for the foreign column was calculated in customAggregateFn.

@Html.EJS().Grid("Grid").DataSource((IEnumerable<object>)ViewBag.DataSource).Columns(col =>
{
    col.Field("OrderID").HeaderText("Order ID").Width("120").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
    col.Field("EmployeeID").HeaderText("Employee Name").Width("150").ForeignKeyValue("FirstName").DataSource(ViewBag.ForeignData).Add();
    col.Field("OrderDate").HeaderText("Order Date").Width("130").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Format("yMd").Add();
    col.Field("Freight").HeaderText("Freight").Width("120").Format("C2").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
    col.Field("ShipCountry").HeaderText("Ship Country").Width("120").Add();

}).Aggregates(agg => { agg.Columns(new List<Syncfusion.EJ2.Grids.GridAggregateColumn>() { new Syncfusion.EJ2.Grids.GridAggregateColumn() { Field = "EmployeeID", Type = "Custom", CustomAggregate = "customAggregateFn", FooterTemplate = "Count of Margaret: ${Custom}" } }).Add();}).Render()

<script>
    function customAggregateFn(data, column) {
        var grid = document.getElementById("Grid").ej2_instances[0];
        return data.result.filter(function (dObj) {
            return ej.base.getValue('FirstName', ej.grids.getForeignData(grid.getColumnByField(column.columnName), dObj)[0]) === 'Margaret';
        }).length;
    };
</script>
public IActionResult Index()
{
    ViewBag.DataSource = OrderDetails.GetAllRecords();
    ViewBag.ForeignData = EmployeeDetails.GetAllRecords();
    return View();
}