JavaScript (ES5)

Search results

ForeignKey Column in JavaScript (ES5) Grid control

08 May 2023 / 7 minutes to read

Foreign key column can be enabled by using column.dataSource, column.foreignKeyField and column.foreignKeyValue properties.

column.dataSource - Defines the foreign data.
column.foreignKeyField - Defines the mapping column name to the foreign data.
column.foreignKeyValue - Defines the display field from the foreign data.

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

Source

Preview

index.js

index.html

ej.grids.Grid.Inject(ej.grids.ForeignKey);
var grid = new ej.grids.Grid({
    dataSource: data,
    columns: [
                { field: 'OrderID', headerText: 'Order ID', textAlign: 'Right', width: 100 },
                { field: 'Freight', headerText: 'Freight', width: 100, textAlign: 'Right'},
                { field: 'ShipName', headerText: 'Ship Name', width: 180 },
                {
                    field: 'EmployeeID', headerText: 'Employee Name', width: 150, foreignKeyValue: 'FirstName', dataSource: employeeData
                }
            ],
    height: 315
});
grid.appendTo('#Grid');

<!DOCTYPE html><html lang="en"><head>
            
    <title>EJ2 Grid</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta name="description" content="Typescript Grid Control">
    <meta name="author" content="Syncfusion">
    <link href="index.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-base/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-grids/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-buttons/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-popups/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-navigations/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-dropdowns/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-lists/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-inputs/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-calendars/styles/material.css" rel="stylesheet">
    
    
    
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-splitbuttons/styles/material.css" rel="stylesheet">
    
    
    
    
<script src="https://cdn.syncfusion.com/ej2/21.2.3/dist/ej2.min.js" type="text/javascript"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
</head>
<body>
    
    <div id="container">
        <div id="Grid"></div>        
    </div>

<script>
var ele = document.getElementById('container');
if(ele) {
    ele.style.visibility = "visible";
 }   
        </script>
<script src="index.js" type="text/javascript"></script>
</body></html>

For remote data, the sorting and grouping is done based on column.foreignKeyField instead of column.foreignKeyValue.

If column.foreignKeyField is not defined, then the column uses column.field.

Use edit template in foreignkey column

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

In the following code example, the Employee Name is a foreign key column. When editing, the AutoComplete component is rendered instead of drop-down list.

Source

Preview

index.js

index.html

var autoCompleteObj;
ej.grids.Grid.Inject(ej.grids.ForeignKey, ej.grids.Edit);
var grid = new ej.grids.Grid({
    dataSource: data.slice(0,10),
    editSettings: { allowEditing: true },
    columns: [
        { field: 'OrderID', headerText: 'Order ID', textAlign: 'Right', width: 100 },
        {
            field: 'EmployeeID', headerText: 'OrderDate', width: 150, foreignKeyValue: 'BirthDate', dataSource: employeeData, format: 'yMd',
            edit: {
                    create: function() { // to create input element
                        return ej.base.createElement('input');
                    },
                    read: function() { // return edited value to update data source
                        var value = new ej.data.DataManager(employeeData).executeLocal(new ej.data.Query().where(ej.grids.getDatePredicate({field: 'BirthDate', operator: 'equal',value: autoCompleteObj.value})));
                        return value.length && value[0]['EmployeeID']; // to convert foreign key value to local value.
                    },
                    destroy: function() { // to destroy the custom component.
                        autoCompleteObj.destroy();
                    },
                    write: (args) => { // to show the value for date picker
                        autoCompleteObj = new ej.dropdowns.AutoComplete({
                            dataSource: employeeData,
                            fields: { value: args.column.foreignKeyValue },
                            value: args.foreignKeyData[0][args.column.foreignKeyValue]
                        });
                        autoCompleteObj.appendTo(args.element);
                    }
                }
        },
        { field: 'Freight', headerText: 'Freight', width: 100, textAlign: 'Right'},
        { field: 'ShipName', headerText: 'Ship Name', width: 180 }
    ],
    height: 315
});
grid.appendTo('#Grid');

<!DOCTYPE html><html lang="en"><head>
            
    <title>EJ2 Grid</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta name="description" content="Typescript Grid Control">
    <meta name="author" content="Syncfusion">
    <link href="index.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-base/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-grids/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-buttons/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-popups/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-navigations/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-dropdowns/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-lists/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-inputs/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-calendars/styles/material.css" rel="stylesheet">
    
    
    
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-splitbuttons/styles/material.css" rel="stylesheet">
    
    
    
    
<script src="https://cdn.syncfusion.com/ej2/21.2.3/dist/ej2.min.js" type="text/javascript"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
</head>
<body>
    
    <div id="container">
        <div id="Grid"></div>        
    </div>

<script>
var ele = document.getElementById('container');
if(ele) {
    ele.style.visibility = "visible";
 }   
        </script>
<script src="index.js" type="text/javascript"></script>
</body></html>

Customize filter UI in foreignkey column

You can create your own filtering UI by using column.filter property. 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.

Source

Preview

index.js

index.html

ej.grids.Grid.Inject(ej.grids.ForeignKey, ej.grids.Filter);
var dropInstance;
var grid = new ej.grids.Grid({
    dataSource: data.slice(0,10),
    allowFiltering: true,
    filterSettings: {type: 'Menu'},
    columns: [
        { field: 'OrderID', headerText: 'Order ID', textAlign: 'Right', width: 100 },
        {
            field: 'EmployeeID', headerText: 'Employee Name', width: 150, foreignKeyValue: 'FirstName', dataSource: fEmployeeData,
            filter: {
                    ui: {
                        create: function(args) {
                            let flValInput: HTMLElement = ej.base.createElement('input', { className: '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);
                        },
                        write: function(arg) {
                            dropInstance.text = args.filteredValue || '';
                        },
                        read: function(args) {
                            args.fltrObj.filterByColumn(args.column.field, args.operator, dropInstance.text);
                        }
                    }
                }
        },
        { field: 'Freight', headerText: 'Freight', width: 100, textAlign: 'Right'},
        { field: 'ShipName', headerText: 'Ship Name', width: 180 }
    ],
    height: 315
});
grid.appendTo('#Grid');

<!DOCTYPE html><html lang="en"><head>
            
    <title>EJ2 Grid</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta name="description" content="Typescript Grid Control">
    <meta name="author" content="Syncfusion">
    <link href="index.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-base/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-grids/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-buttons/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-popups/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-navigations/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-dropdowns/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-lists/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-inputs/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-calendars/styles/material.css" rel="stylesheet">
    
    
    
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-splitbuttons/styles/material.css" rel="stylesheet">
    
    
    
    
<script src="https://cdn.syncfusion.com/ej2/21.2.3/dist/ej2.min.js" type="text/javascript"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
</head>
<body>
    
    <div id="container">
        <div id="Grid"></div>        
    </div>

<script>
var ele = document.getElementById('container');
if(ele) {
    ele.style.visibility = "visible";
 }   
        </script>
<script src="index.js" type="text/javascript"></script>
</body></html>

Use filter bar template in foreignkey column

You can use the filter bar template in foreign key column by defining the column.filterBarTemplate property. 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.

Source

Preview

index.js

index.html

ej.grids.Grid.Inject(ej.grids.ForeignKey, ej.grids.Filter);
var grid = new ej.grids.Grid({
    dataSource: data.slice(0,10),
    allowFiltering: true,
    columns: [
        { field: 'OrderID', headerText: 'Order ID', textAlign: 'Right', width: 100 },
        {
            field: 'EmployeeID', headerText: 'Employee Name', width: 150, foreignKeyValue: 'FirstName', dataSource: fEmployeeData,
            filterBarTemplate: {
                create: function(args) {
                    return ej.base.createElement('input', { className: 'flm-input' });
                },
                write: function(args) {
                    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: function(args) {
                            if (args.value !== 'All') {
                                grid.filterByColumn('EmployeeID', 'equal', args.value);
                            }
                            else {
                                grid.removeFilteredColsByField('EmployeeID');
                            }
                        }
                    });
                    dropInstance.appendTo(args.element);
                }
            }
        },
        { field: 'Freight', headerText: 'Freight', width: 100, textAlign: 'Right'},
        { field: 'ShipName', headerText: 'Ship Name', width: 180 }
    ],
    height: 260
});
grid.appendTo('#Grid');

<!DOCTYPE html><html lang="en"><head>
            
    <title>EJ2 Grid</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta name="description" content="Typescript Grid Control">
    <meta name="author" content="Syncfusion">
    <link href="index.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-base/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-grids/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-buttons/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-popups/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-navigations/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-dropdowns/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-lists/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-inputs/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-calendars/styles/material.css" rel="stylesheet">
    
    
    
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-splitbuttons/styles/material.css" rel="stylesheet">
    
    
    
    
<script src="https://cdn.syncfusion.com/ej2/21.2.3/dist/ej2.min.js" type="text/javascript"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
</head>
<body>
    
    <div id="container">
        <div id="Grid"></div>        
    </div>

<script>
var ele = document.getElementById('container');
if(ele) {
    ele.style.visibility = "visible";
 }   
        </script>
<script src="index.js" type="text/javascript"></script>
</body></html>

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.

Source

Preview

index.js

index.html

var datePickerObj;
ej.grids.Grid.Inject(ej.grids.ForeignKey, ej.grids.Aggregate);
var grid = new ej.grids.Grid({
            dataSource: data.slice(0,10),
            columns: [
                { field: 'OrderID', headerText: 'Order ID', textAlign: 'Right', width: 100 },
                {
                    field: 'EmployeeID', headerText: 'Employee Name', width: 150, foreignKeyValue: 'FirstName', dataSource: employeeData,
                },
                { field: 'Freight', headerText: 'Freight', width: 100, textAlign: 'Right'},
                { field: 'ShipName', headerText: 'Ship Name', width: 180 }
            ],
            height: 280,
            aggregates: [
                {
                    columns: [
                        {
                            type: 'Custom',
                            customAggregate: customAggregateFn,
                            field: 'EmployeeID',
                            footerTemplate: 'Margaret count: ${Custom}'
                        }
                    ]
                }
            ]
        });
grid.appendTo('#Grid');

<!DOCTYPE html><html lang="en"><head>
            
    <title>EJ2 Grid</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta name="description" content="Typescript Grid Control">
    <meta name="author" content="Syncfusion">
    <link href="index.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-base/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-grids/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-buttons/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-popups/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-navigations/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-dropdowns/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-lists/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-inputs/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-calendars/styles/material.css" rel="stylesheet">
    
    
    
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-splitbuttons/styles/material.css" rel="stylesheet">
    
    
    
    
<script src="https://cdn.syncfusion.com/ej2/21.2.3/dist/ej2.min.js" type="text/javascript"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
</head>
<body>
    
    <div id="container">
        <div id="Grid"></div>        
    </div>

<script>
var ele = document.getElementById('container');
if(ele) {
    ele.style.visibility = "visible";
 }   
        </script>
<script src="index.js" type="text/javascript"></script>
</body></html>

Enable multiple foreign key columns

Multiple foreign key columns with editing options are enabled for the JavaScript(ES5) Grid component.

In the following example, Customer Name and Ship City are foreign key columns that display the ContactName and City columns from foreign data.

Source

Preview

index.js

index.html

ej.grids.Grid.Inject(ej.grids.Page, ej.grids.Edit, ej.grids.Toolbar, ej.grids.ForeignKey);
var grid = new ej.grids.Grid({
    dataSource: orderDetails,
    allowPaging: true,
    toolbar: ['Add', 'Edit', 'Delete', 'Update', 'Cancel'],
    editSettings: { allowEditing: true, allowAdding: true, allowDeleting: true },
        columns: [
            { field: 'OrderID', headerText: 'Order ID', textAlign: 'Right',  isPrimaryKey: true, width: 100 },
            {
                field: 'CustomerID', foreignKeyField: 'CustomerID', foreignKeyValue: 'ContactName', dataSource: customerData,
                width: 150, headerText: 'Customer Name', validationRules: { required: true }
            },
            { field: 'Freight', headerText: 'Freight', editType: 'numericedit', width: 100, textAlign: 'Right', format: 'C2'},
            {
                field: 'EmployeeID', foreignKeyField: 'EmployeeID', foreignKeyValue: 'City', dataSource: employeeData,
                width: 150, headerText: 'Ship City', validationRules: { required: true }
            },

            { field: 'ShipName', headerText: 'Ship Name', editType: 'dropdownedit', width: 180 }
        ],
        height: 315
    });
grid.appendTo('#Grid');

<!DOCTYPE html><html lang="en"><head>
            
    <title>EJ2 Grid</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta name="description" content="Typescript Grid Control">
    <meta name="author" content="Syncfusion">
    <link href="index.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-base/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-grids/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-buttons/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-popups/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-navigations/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-dropdowns/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-lists/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-inputs/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-calendars/styles/material.css" rel="stylesheet">
    
    
    
    <link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-splitbuttons/styles/material.css" rel="stylesheet">
    
    
    
    
<script src="https://cdn.syncfusion.com/ej2/21.2.3/dist/ej2.min.js" type="text/javascript"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
</head>
<body>
    
    <div id="container">
        <div id="Grid"></div>        
    </div>

<script>
var ele = document.getElementById('container');
if(ele) {
    ele.style.visibility = "visible";
 }   
        </script>
<script src="index.js" type="text/javascript"></script>
</body></html>

Contents

Use edit template in foreignkey column
Customize filter UI in foreignkey column
Use filter bar template in foreignkey column
Perform aggregation in foreignkey column
Enable multiple foreign key columns

Contents

Use edit template in foreignkey column
Customize filter UI in foreignkey column
Use filter bar template in foreignkey column
Perform aggregation in foreignkey column
Enable multiple foreign key columns

Please provide additional information

Thank you for your feedback and comments.We will rectify this as soon as possible!