Search results

ForeignKey Column in Angular Grid component

18 May 2022 / 7 minutes to read

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

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

To use ForeignKeyColumn, you need to inject ForeignKeyService in the provider section of AppModule.

Source
Preview
app.component.ts
app.module.ts
main.ts
Copied to clipboard
import { Component, OnInit } from '@angular/core';
import { ForeignKeyService } from '@syncfusion/ej2-angular-grids';
import { data, employeeData } from './datasource';

@Component({
selector: 'app-root',
template: `<ejs-grid #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>`,
providers: [ForeignKeyService]
})
export class AppComponent implements OnInit {

public data: object[];
public employeeData: object[];

ngOnInit(): void {
    this.data = data;
    this.employeeData = employeeData;
}
}
Copied to clipboard
import { NgModule } from '@angular/core';
import { BrowserModule } from '@angular/platform-browser';
import { GridModule } from '@syncfusion/ej2-angular-grids';
import { AppComponent } from './app.component';

/**
 * Module
 */
@NgModule({
    imports: [
        BrowserModule,
        GridModule
    ],
    declarations: [AppComponent],
    bootstrap: [AppComponent]
})
export class AppModule { }
Copied to clipboard
import { platformBrowserDynamic } from '@angular/platform-browser-dynamic';
import { enableProdMode } from '@angular/core';
import { AppModule } from './app.module';

enableProdMode();
platformBrowserDynamic().bootstrapModule(AppModule);

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.

Source
Preview
app.component.ts
app.module.ts
main.ts
Copied to clipboard
import { Component, OnInit } from '@angular/core';
import { createElement } from '@syncfusion/ej2-base';
import { ForeignKeyService, EditService, IEditCell, EditSettingsModel, ToolbarService, Column } from '@syncfusion/ej2-angular-grids';
import { DataManager, Query } from '@syncfusion/ej2-data';
import { AutoComplete } from '@syncfusion/ej2-angular-dropdowns';
import { data, employeeData } from './datasource';


@Component({
selector: 'app-root',
template: `<ejs-grid #grid [dataSource]='data' [height]='270' [editSettings]='editoption' [toolbar]='toolbar'>
                <e-columns>
                    <e-column field='OrderID' headerText='Order ID' isPrimaryKey='true' textAlign='Right' width=100></e-column>
                    <e-column field='EmployeeID' headerText='Employee Name' width=120
                    foreignKeyValue='FirstName' [dataSource]='employeeData' [edit]='edit'></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>`,
providers: [ForeignKeyService, EditService, ToolbarService]
})
export class AppComponent implements OnInit {

public data: object[];
public employeeData: object[];
public editoption: EditSettingsModel = { allowEditing: true };
public autoComplete: AutoComplete;
toolbar = ['Add', 'Edit', 'Delete', 'Update', 'Cancel'];
public edit: IEditCell = {
    create: () => { // to create input element
        return createElement('input');
    },
    read: () => { // return edited value to update data source
        const EmployeeID = 'EmployeeID';
        const value = new DataManager(employeeData).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: { rowData: object, column: Column, foreignKeyData: object,
         element: HTMLTableCellElement }) => { // to show the value for date picker
        this.autoComplete = new AutoComplete({
            dataSource: employeeData,
            fields: { value: args.column.foreignKeyValue },
            value: args.foreignKeyData[0][args.column.foreignKeyValue]
        });
        this.autoComplete.appendTo(args.element);
    }
}

ngOnInit(): void {
    this.data = data;
    this.employeeData = employeeData;
}
}
Copied to clipboard
import { NgModule } from '@angular/core';
import { BrowserModule } from '@angular/platform-browser';
import { GridModule, ForeignKeyService } from '@syncfusion/ej2-angular-grids';
import { AppComponent } from './app.component';

/**
 * Module
 */
@NgModule({
    imports: [
        BrowserModule,
        GridModule
    ],
    declarations: [AppComponent],
    bootstrap: [AppComponent],
    providers: [ForeignKeyService]
})
export class AppModule { }
Copied to clipboard
import { platformBrowserDynamic } from '@angular/platform-browser-dynamic';
import { enableProdMode } from '@angular/core';
import { AppModule } from './app.module';

enableProdMode();
platformBrowserDynamic().bootstrapModule(AppModule);

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
app.component.ts
app.module.ts
main.ts
Copied to clipboard
import { Component, OnInit, ViewChild } from '@angular/core';
import { createElement } from '@syncfusion/ej2-base';
import { GridComponent, ForeignKeyService, FilterService, IFilter, FilterSettingsModel, Filter } from '@syncfusion/ej2-angular-grids';
import { DataManager } from '@syncfusion/ej2-data';
import { DropDownList } from '@syncfusion/ej2-angular-dropdowns';
import { data, fEmployeeData } from './datasource';

@Component({
selector: 'app-root',
template: `<ejs-grid #grid [dataSource]='data' [height]='315' [allowFiltering]='true'
    [filterSettings]='filteroption'>
                <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' [filter]='filter'></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>`,
providers: [ForeignKeyService, FilterService]
})
export class AppComponent implements OnInit {

public data: object[];
@ViewChild('grid') public grid: GridComponent;
public employeeData: object[];
public dropInstance: DropDownList;
public filteroption: FilterSettingsModel = { type: 'Menu'};
public filter: IFilter = {
    ui: {
        create: (args: { target: Element, column: object }) => {
            const flValInput: HTMLElement = createElement('input', { className: 'flm-input' });
            args.target.appendChild(flValInput);
            this.dropInstance = new DropDownList({
                dataSource: new DataManager(fEmployeeData),
                fields: { text: 'FirstName', value: 'EmployeeID' },
                placeholder: 'Select a value',
                popupHeight: '200px'
            });
            this.dropInstance.appendTo(flValInput);
        },
        write: (args: {
            column: object, target: Element, parent: any,
            filteredValue: number | string
        }) => {
            this.dropInstance.text = args.filteredValue as string || '';
        },
        read: (args: { target: Element, column: any, operator: string, fltrObj: Filter }) => {
            args.fltrObj.filterByColumn(args.column.field, args.operator, this.dropInstance.text);
        }
    }
};
ngOnInit(): void {
    this.data = data;
    this.employeeData = fEmployeeData;
}
}
Copied to clipboard
import { NgModule } from '@angular/core';
import { BrowserModule } from '@angular/platform-browser';
import { GridModule, ForeignKeyService } from '@syncfusion/ej2-angular-grids';
import { AppComponent } from './app.component';

/**
 * Module
 */
@NgModule({
    imports: [
        BrowserModule,
        GridModule
    ],
    declarations: [AppComponent],
    bootstrap: [AppComponent],
    providers: [ForeignKeyService]
})
export class AppModule { }
Copied to clipboard
import { platformBrowserDynamic } from '@angular/platform-browser-dynamic';
import { enableProdMode } from '@angular/core';
import { AppModule } from './app.module';

enableProdMode();
platformBrowserDynamic().bootstrapModule(AppModule);

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
app.component.ts
app.module.ts
main.ts
Copied to clipboard
import { Component, OnInit, ViewChild } from '@angular/core';
import { createElement } from '@syncfusion/ej2-base';
import { GridComponent, ForeignKeyService, FilterService, IFilterUI, Column } from '@syncfusion/ej2-angular-grids';
import { DataManager } from '@syncfusion/ej2-data';
import { DropDownList } from '@syncfusion/ej2-angular-dropdowns';
import { data, fEmployeeData } from './datasource';

@Component({
selector: 'app-root',
template: `<ejs-grid #grid [dataSource]='data' [height]='260' [allowFiltering]='true'>
                <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' [filterBarTemplate]='filter'></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>`,
providers: [ForeignKeyService, FilterService]
})
export class AppComponent implements OnInit {

public data: object[];
@ViewChild('grid') public grid: GridComponent;
public employeeData: object[];
public filter: IFilterUI = {
    create: (args: { element: Element, column: Column }) => {
        return createElement('input', { className: 'flm-input' });
    },
    write: (args: { element: Element, column: Column }) => {
        fEmployeeData.splice(0, 0, {FirstName: 'All'}); // for clear filtering
        const dropInstance: DropDownList = new DropDownList({
            dataSource: new DataManager(fEmployeeData),
            fields: { text: 'FirstName' },
            placeholder: 'Select a value',
            popupHeight: '200px',
            index: 0,
            change: (e) => {
                if (e.value !== 'All') {
                    this.grid.filterByColumn('EmployeeID', 'equal', e.value);
                } else {
                    this.grid.removeFilteredColsByField('EmployeeID');
                }
            }
        });
        dropInstance.appendTo(args.element as HTMLTableCellElement);
    }
};
ngOnInit(): void {
    this.data = data;
    this.employeeData = fEmployeeData;
}
}
Copied to clipboard
import { NgModule } from '@angular/core';
import { BrowserModule } from '@angular/platform-browser';
import { GridModule, ForeignKeyService } from '@syncfusion/ej2-angular-grids';
import { AppComponent } from './app.component';

/**
 * Module
 */
@NgModule({
    imports: [
        BrowserModule,
        GridModule
    ],
    declarations: [AppComponent],
    bootstrap: [AppComponent],
    providers: [ForeignKeyService]
})
export class AppModule { }
Copied to clipboard
import { platformBrowserDynamic } from '@angular/platform-browser-dynamic';
import { enableProdMode } from '@angular/core';
import { AppModule } from './app.module';

enableProdMode();
platformBrowserDynamic().bootstrapModule(AppModule);

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.

Source
Preview
app.component.ts
app.module.ts
main.ts
Copied to clipboard
import { Component, OnInit, ViewChild } from '@angular/core';
import { ForeignKeyService, AggregateService, getForeignData, CustomSummaryType,
 AggregateColumnModel, GridComponent } from '@syncfusion/ej2-angular-grids';
import { data, employeeData } from './datasource';
import { getValue } from '@syncfusion/ej2-base';

@Component({
selector: 'app-root',
template: `<ejs-grid #grid [dataSource]='data' [height]='280'>
                <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>
                <e-aggregates>
                    <e-aggregate>
                        <e-columns>
                            <e-column field="EmployeeID" type="Custom" [customAggregate]= 'customAggregateFn'>
                                <ng-template #footerTemplate let-data>
                                    Count of Margaret:  {{data.Custom}}
                                </ng-template>
                            </e-column>
                        </e-columns>
                    </e-aggregate>
                </e-aggregates>
            </ejs-grid>`,
providers: [ForeignKeyService, AggregateService]
})
export class AppComponent implements OnInit {

public data: object[];
@ViewChild('grid') public grid: GridComponent;
public employeeData: object[];

// Custom Aggregate function for foreign column
public customAggregateFn: CustomSummaryType = (data1: any, column: AggregateColumnModel) => {
    return data1.result.filter((dObj: object) => {
        return getValue('FirstName', getForeignData(this.grid.getColumnByField(column.field), dObj)[0]) === 'Margaret';
    }).length;
}

ngOnInit(): void {
    this.data = data;
    this.employeeData = employeeData;
}
}
Copied to clipboard
import { NgModule } from '@angular/core';
import { BrowserModule } from '@angular/platform-browser';
import { GridModule, ForeignKeyService } from '@syncfusion/ej2-angular-grids';
import { AppComponent } from './app.component';

/**
 * Module
 */
@NgModule({
    imports: [
        BrowserModule,
        GridModule
    ],
    declarations: [AppComponent],
    bootstrap: [AppComponent],
    providers: [ForeignKeyService]
})
export class AppModule { }
Copied to clipboard
import { platformBrowserDynamic } from '@angular/platform-browser-dynamic';
import { enableProdMode } from '@angular/core';
import { AppModule } from './app.module';

enableProdMode();
platformBrowserDynamic().bootstrapModule(AppModule);