Foreign key column in Vue Grid component

16 Mar 202321 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.

app.vue
<template>
    <div id="app">
        <ejs-grid :dataSource='data' height='315'>
            <e-columns>
                <e-column field='OrderID' headerText='Order ID' textAlign='Right' width=100></e-column>
                <e-column field='EmployeeID' headerText='Employee Name' width=120 foreignKeyValue='FirstName' :dataSource='employeeData'></e-column>
                <e-column field='Freight' headerText='Freight' textAlign='Right' width=80></e-column>
                <e-column field='ShipCity' headerText='Ship City' width=130  ></e-column>
            </e-columns>
        </ejs-grid>
    </div>
</template>
<script>
import Vue from "vue";
import { GridPlugin, ForeignKey } from "@syncfusion/ej2-vue-grids";
import { data, employeeData } from './datasource.js';

Vue.use(GridPlugin);

export default {
  data() {
    return {
      data: data,
      employeeData: employeeData
    };
  },
  provide: {
      grid: [ForeignKey]
  }
}
</script>
<style>
 @import "../node_modules/@syncfusion/ej2-vue-grids/styles/material.css";
</style>

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, foreign key column uses dropdown component for editing.
You can render other than the dropdown by using the column.edit property.
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.

app.vue
<template>
    <div id="app">
        <ejs-grid ref='grid' :dataSource='data' :editSettings='editoption' :Toolbar='toolbar' height='270px' >
            <e-columns>
                <e-column field='OrderID' headerText='Order ID' textAlign='Right' width=100></e-column>
                <e-column field='EmployeeID' headerText='Employee Name' :dataSource='employeeData' foreignKeyValue='FirstName' :edit='edit' width=120></e-column>
                <e-column field='Freight' headerText='Freight' textAlign='Center' format='C2' width=80></e-column>
                 <e-column field='ShipCity' headerText='Ship City' width=130></e-column>
            </e-columns>
        </ejs-grid>
    </div>
</template>
<script>
import Vue from "vue";
import { createElement } from '@syncfusion/ej2-base';
import { GridPlugin, Edit, Toolbar,ForeignKey  } from "@syncfusion/ej2-vue-grids";
import { AutoComplete } from "@syncfusion/ej2-dropdowns";
import { DataManager,Query } from '@syncfusion/ej2-data';
import { data,fEmployeeData } from './datasource.js';

Vue.use(GridPlugin);
export default {
      data: () => {
        return {
          data: data,
          employeeData: fEmployeeData,
          toolbar: ['Add', 'Edit', 'Delete', 'Update', 'Cancel'],
          editoption: { allowEditing: true },
          edit: {
            create: () => { // to create input element
              return createElement('input');
            },
            read: () => { // return edited value to update data source
              let value = new DataManager(fEmployeeData).executeLocal(new Query().where('FirstName', 'equal', this.autoComplete.value));
              return value.length && value[0]['EmployeeID']; // to convert foreign key value to local value.
            },
            destroy: () => { // to destroy the custom component.
              this.autoComplete.destroy();
            },
            write: (args) => { // to show the value for date picker
              this.autoComplete = new AutoComplete({
                dataSource: fEmployeeData,
                fields: { value: args.column.foreignKeyValue },
                value: args.foreignKeyData[0][args.column.foreignKeyValue]
              });
              this.autoComplete.appendTo(args.element);
            }
          },
        };
      },
      provide: {
        grid: [Edit, ForeignKey]
      },
    }
</script>
<style>
  @import "../node_modules/@syncfusion/ej2-vue-grids/styles/material.css";
</style>

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.

app.vue
<template>
    <div id="app">
        <ejs-grid ref='grid' :dataSource='data' :allowFiltering='true' :filterSettings='filteroption' height='270px' >
            <e-columns>
                <e-column field='OrderID' headerText='Order ID' textAlign='Right' width=100></e-column>
               <e-column field='EmployeeID' headerText='Employee Name' :dataSource='employeeData' foreignKeyValue='FirstName' :filter='filter' width=120></e-column>
                <e-column field='Freight' headerText='Freight' textAlign='Center' format='C2' width=80></e-column>
                 <e-column field='ShipCity' headerText='Ship City' width=130></e-column>
            </e-columns>
        </ejs-grid>
    </div>
</template>
<script>
import Vue from "vue";
import { createElement } from '@syncfusion/ej2-base';
import { GridPlugin, Edit, Toolbar, ForeignKey, Filter  } from "@syncfusion/ej2-vue-grids";
import { DropDownList } from "@syncfusion/ej2-dropdowns";
import { DataManager } from '@syncfusion/ej2-data';
import { data,fEmployeeData } from './datasource.js';

let dropInstance;

Vue.use(GridPlugin);
export default {
      data: () => {
        return {
          data: data,
          employeeData: fEmployeeData,
          filteroption: { type: 'Menu' },
          filter: {
            ui: {
              create: (args) => {
                let flValInput = createElement('input', { className: 'flm-input' });
                args.target.appendChild(flValInput);
                dropInstance = new DropDownList({
                  dataSource: new DataManager(fEmployeeData),
                  fields: { text: 'FirstName', value: 'EmployeeID' },
                  placeholder: 'Select a value',
                  popupHeight: '200px'
                });
                dropInstance.appendTo(flValInput);
              },
              write: (args) => {
                dropInstance.text = args.filteredValue || '';
              },
              read: (args) => {
                args.fltrObj.filterByColumn(args.column.field, args.operator,dropInstance.text);
              }
            }
          },
        };
      },
      provide: {
        grid: [Filter, ForeignKey, Edit, Toolbar]
      }
    }
</script>
<style>
  @import "../node_modules/@syncfusion/ej2-vue-grids/styles/material.css";
</style>

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 custom the 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.

app.vue
<template>
    <div id="app">
        <ejs-grid ref='grid' :dataSource='data' :allowFiltering='true'  height='260px' >
            <e-columns>
                <e-column field='OrderID' headerText='Order ID' textAlign='Right' width=100></e-column>
               <e-column field='EmployeeID' headerText='Employee Name' :dataSource='employeeData' foreignKeyValue='FirstName' width=120></e-column>
                <e-column field='Freight' headerText='Freight' textAlign='Center' format='C2' width=80></e-column>
                 <e-column field='ShipCity' headerText='Ship City' width=130></e-column>
            </e-columns>
             <e-aggregates>
              <e-aggregate>
                <e-columns>
                    <e-column field="EmployeeID" type="Custom" :customAggregate='customAggregateFn' :footerTemplate='footerTemplate'></e-column>
                </e-columns>
             </e-aggregate>
          </e-aggregates>
        </ejs-grid>
    </div>
</template>
<script>
import Vue from "vue";
import { GridPlugin, Aggregate ,ForeignKey, Filter } from "@syncfusion/ej2-vue-grids";
import { getValue } from "@syncfusion/ej2-base";
import { getForeignData } from "@syncfusion/ej2-grids";
import { data,fEmployeeData } from './datasource.js';

Vue.use(GridPlugin);
export default {
      data: () => {
        return {
          data: data,
          employeeData: fEmployeeData,
          footerTemplate: function () {
            return {
              template: Vue.component('customTemplate', {
                template: `<span>Count of Margaret:  </span>`,
                data() { return { data: { data: {} } }; }
              })
            }
          },

        };
      },
      methods: {
        customAggregateFn: function (data, column) {
          return data.result.filter((dObj) => {
            return getValue('FirstName', getForeignData(this.$refs.grid.getColumnByField(column.field), dObj)[0]) === 'Margaret';
          }).length;
        }
      },
      provide: {
        grid: [Aggregate, ForeignKey, Filter],
      },
    }
</script>
<style>
  @import "../node_modules/@syncfusion/ej2-vue-grids/styles/material.css";
</style>

Enable multiple foreign key columns

Multiple foreign key columns with editing options are enabled for the Vue 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.

app.vue
<template>
    <div id="app">
        <ejs-grid :dataSource='orderDetails' :editSettings='editOptions' :toolbar='toolbarItems'  height='315'>
            <e-columns>
                <e-column field='OrderID' headerText='Order ID' textAlign='Right'  :validationRules='orderidrules' :isPrimaryKey='true' width=100></e-column>
                <e-column field='CustomerID' headerText='Customer Name' width=120 foreignKeyValue='ContactName' foreignKeyField="CustomerID" :dataSource='customerData' :validationRules='orderidrules'></e-column>
                <e-column field='Freight' headerText='Freight' textAlign='Right' editType='numericedit' width=80 format='C2'></e-column>
                <e-column field='EmployeeID' headerText='Ship City' width=120 foreignKeyValue='City' foreignKeyField="EmployeeID" :dataSource='employeeData' :validationRules='orderidrules'></e-column>
                <e-column field='ShipCountry' headerText='Ship Country' editType='dropdownedit' width=130></e-column>
            </e-columns>
        </ejs-grid>
    </div>
</template>
<script>
import Vue from "vue";
import { GridPlugin, Edit, Toolbar, ForeignKey } from "@syncfusion/ej2-vue-grids";
import { orderDetails, customerData, employeeData } from './datasource.js';

Vue.use(GridPlugin);

export default {
  data() {
    return {
      orderDetails: orderDetails,
      customerData: customerData,
      employeeData: employeeData,
      toolbarItems: ['Add', 'Edit', 'Delete', 'Update', 'Cancel'],
      editOptions: { allowEditing: true, allowAdding: true, allowDeleting: true },
      orderidrules: { required: true },
    };
  },
  provide: {
      grid: [Edit, Toolbar, ForeignKey]
  }
}
</script>
<style>
 @import "https://cdn.syncfusion.com/ej2/material.css";
</style>