JavaScript/jQuery DataGrid - columns.lookup
Specifies properties of a lookup column.
A lookup column restricts the set of values that can be chosen when a user edits or filters the column. In a lookup column, each cell is a drop-down menu. You can use a lookup column when you need to substitute displayed values with required values. For example, consider that you have two arrays of objects: drivers
and buses
.
const drivers = [ { driverID: 1, firstName: "John", lastName: "Smith", busID: 2 }, { driverID: 2, firstName: "Lizzy", lastName: "Cook", busID: 1 }, { driverID: 3, firstName: "Brian", lastName: "Hawkins", busID: 3 } ]; const buses = [ { busID: 1, plates: "123456" }, { busID: 2, plates: "AB-1234" }, { busID: 3, plates: "CD-9876" } ];
All drivers
have the busID
field, which refers to a bus. If drivers
is the main dataSource, the Bus ID column displays bus IDs, which provides little information to a user. It will be more useful to display bus license plates instead of IDs. For this, the buses
array must be set as a lookup dataSource for the Bus ID column. Then, the names of data fields must be assigned to the valueExpr and displayExpr properties. Values from the valueExpr data field will be replaced with values from the displayExpr data field.
jQuery
$(function() { $("#dataGridContainer").dxDataGrid({ dataSource: drivers, // ... columns: [{ dataField: "busID", lookup: { dataSource: buses, valueExpr: "busID", displayExpr: "plates" } }] }); });
Angular
<dx-data-grid [dataSource]="drivers"> <dxi-column dataField="busID"> <dxo-lookup [dataSource]="buses" valueExpr="busID" displayExpr="plates"> </dxo-lookup> </dxi-column> </dx-data-grid>
import { DxDataGridModule } from "devextreme-angular"; // ... export class AppComponent { drivers = [ { driverID: 1, firstName: "John", lastName: "Smith", busID: 2 }, { driverID: 2, firstName: "Lizzy", lastName: "Cook", busID: 1 }, { driverID: 3, firstName: "Brian", lastName: "Hawkins", busID: 3 } ]; buses = [ { busID: 1, plates: "123456" }, { busID: 2, plates: "AB-1234" }, { busID: 3, plates: "CD-9876" } ]; } @NgModule({ imports: [ // ... DxDataGridModule ], // ... })
Vue
<template> <DxDataGrid ... :data-source="drivers"> <DxColumn data-field="busID"> <DxLookup :data-source="buses" value-expr="busID" display-expr="plates" /> </DxColumn> </DxDataGrid> </template> <script> import 'devextreme/dist/css/dx.light.css'; import DxDataGrid, { DxColumn, DxLookup } from 'devextreme-vue/data-grid'; const drivers = [ { driverID: 1, firstName: "John", lastName: "Smith", busID: 2 }, { driverID: 2, firstName: "Lizzy", lastName: "Cook", busID: 1 }, { driverID: 3, firstName: "Brian", lastName: "Hawkins", busID: 3 } ]; const buses = [ { busID: 1, plates: "123456" }, { busID: 2, plates: "AB-1234" }, { busID: 3, plates: "CD-9876" } ]; export default { components: { DxDataGrid, DxColumn, DxLookup }, data() { return { drivers, buses } } } </script>
React
import React from 'react'; import 'devextreme/dist/css/dx.light.css'; import DataGrid, { Column, Lookup } from 'devextreme-react/data-grid'; const drivers = [ { driverID: 1, firstName: "John", lastName: "Smith", busID: 2 }, { driverID: 2, firstName: "Lizzy", lastName: "Cook", busID: 1 }, { driverID: 3, firstName: "Brian", lastName: "Hawkins", busID: 3 } ]; const buses = [ { busID: 1, plates: "123456" }, { busID: 2, plates: "AB-1234" }, { busID: 3, plates: "CD-9876" } ]; export default function App() { return ( <DataGrid ... dataSource={drivers}> <Column dataField="busID"> <Lookup dataSource={buses} valueExpr="busID" displayExpr="plates" /> </Column> </DataGrid> ); }
With this code, the Bus ID column contains license plates instead of IDs. Moreover, the user can choose a plate number from the drop-down menu when editing cells or applying a filter to this column.
See Also
allowClearing
Specifies whether to display the Clear button in lookup column cells while they are being edited.
Set this property to true only if your data source accepts null values.
To specify this property based on a condition, set the showClearButton property instead. This is a property of the SelectBox UI component which is used as an editor for lookup column cells. allowClearing is an alias for this property in the DataGrid. The following code shows how to set showClearButton in the onEditorPreparing event handler:
jQuery
$(function() { $("#dataGridContainer").dxDataGrid({ // ... onEditorPreparing: function (e) { if (/* a condition to set the property's value */) { e.editorOptions.showClearButton = true; } } }); });
Angular
import { Component } from '@angular/core'; @Component({ selector: 'app-root', templateUrl: './app.component.html', styleUrls: ['./app.component.css'] }) export class AppComponent { onEditorPreparing(e) { if (/* a condition to set the property's value */) { e.editorOptions.showClearButton = true; } } }
import { BrowserModule } from '@angular/platform-browser'; import { NgModule } from '@angular/core'; import { AppComponent } from './app.component'; import { DxDataGridModule } from 'devextreme-angular'; @NgModule({ declarations: [ AppComponent ], imports: [ BrowserModule, DxDataGridModule ], bootstrap: [AppComponent] }) export class AppModule { }
<dx-data-grid ... (onEditorPreparing)="onEditorPreparing($event)"> <!-- ... --> </dx-data-grid>
Vue
<template> <DxDataGrid ... :on-editor-preparing="onEditorPreparing"> <!-- ... --> </DxDataGrid> </template> <script> import 'devextreme/dist/css/dx.light.css'; import DxDataGrid from 'devextreme-vue/data-grid'; export default { components: { DxDataGrid }, // ... methods: { onEditorPreparing(e) { if (/* a condition to set the property's value */) { e.editorOptions.showClearButton = true; } } } } </script>
React
import React from 'react'; import 'devextreme/dist/css/dx.light.css'; import DataGrid from 'devextreme-react/data-grid'; class App extends React.Component { onEditorPreparing(e) { if (/* a condition to set the property's value */) { e.editorOptions.showClearButton = true; } } render() { return ( <DataGrid ... onEditorPreparing={this.onEditorPreparing}> {/* ... */} </DataGrid> ); } } export default App;
dataSource
Specifies the data source for the lookup column.
An array of objects or primitives, a store instance, or a DataSource configuration.
This property accepts one of the following:
Array of objects or primitives
A JavaScript array that contains plain objects or primitives.DataSource configuration object
A DataSource configuration object. You can find code for this example in the following help topic: Bind a Lookup Column to a Custom Data Source. For more information about the DataSource and DevExtreme Data Layer, refer to the Data Layer article.IMPORTANTDataSource instances are not supported.Store instance
An ArrayStore, LocalStore, ODataStore, or CustomStore instance.Function
A function that returns one of the above.NOTEWhen the component is initialized, it calls the dataSource function with an empty options object. To avoid execution errors, check that options properties are defined before accessing them. The following code snippet demonstrates how to check if options.data is defined before using the property in a filter expression:
jQuery
index.js$("#dataGridContainer").dxDataGrid({ columns: [{ lookup: { dataSource(options) { return { store: dataStore, filter: options.data ? ['StateID', '=', options.data.StateID] : null, }; }, } }] })
Angular
app.component.htmlapp.component.ts<dx-data-grid ... > <dxi-column ... > <dxo-lookup [dataSource]="lookupDataSource" ... ></dxo-lookup> </dxi-column> </dx-data-grid>
export class AppComponent { lookupDataSource = (options) => { return { store: this.dataStore, filter: options.data ? ['StateID', '=', options.data.StateID] : null, } } }
Vue
App.vue<script setup> import { DxDataGrid, DxColumn, DxLookup } from 'devextreme-vue/data-grid'; const lookupDataSource = (options) => { return { store: dataStore, filter: options.data ? ['StateID', '=', options.data.StateID] : null, } } </script> <template> <DxDataGrid ... > <DxColumn ... > <DxLookup :dataSource="lookupDataSource" ... /> </DxColumn> </DxDataGrid> </template>
React
App.jsimport { DataGrid } from 'devextreme-react'; import { Column, Lookup } from 'devextreme-react/data-grid'; function App() { function lookupDataSource(options) { return { store: dataStore, filter: options.data ? ['StateID', '=', options.data.StateID] : null, } } return ( <> <DataGrid ... > <Column ... > <Lookup dataSource={lookupDataSource} ... /> </Column> </DataGrid> </> ) }
If the lookup data source contains objects, specify the valueExpr and displayExpr properties in addition to the dataSource.
displayExpr
Specifies the data source field whose values must be displayed.
A row's data.
The displayed value.
This property accepts a string - the name of the data field that provides displayed values, or a function that returns the displayed value.
Values in a lookup column are sorted by the valueExpr field. Implement the column's calculateSortValue function to sort by the displayExpr field instead:
jQuery
index.js$(function() { $("#dataGridContainer").dxDataGrid({ columns: [{ // ... lookup: { // ... }, calculateSortValue: function (data) { const value = this.calculateCellValue(data); return this.lookup.calculateCellValue(value); } }] }); });
Angular
app.component.htmlapp.component.tsapp.module.ts<dx-data-grid ... > <dxi-column ... [calculateSortValue]="calculateSortValue"> <dxo-lookup ... ></dxo-lookup> </dxi-column> </dx-data-grid>
import { Component } from '@angular/core'; @Component({ selector: 'app-root', templateUrl: './app.component.html', styleUrls: ['./app.component.css'] }) export class AppComponent { calculateSortValue (data) { const column = this as any; const value = column.calculateCellValue(data); return column.lookup.calculateCellValue(value); } }
import { BrowserModule } from '@angular/platform-browser'; import { NgModule } from '@angular/core'; import { AppComponent } from './app.component'; import { DxDataGridModule } from 'devextreme-angular'; @NgModule({ declarations: [ AppComponent ], imports: [ BrowserModule, DxDataGridModule ], providers: [ ], bootstrap: [AppComponent] }) export class AppModule { }
Vue
App.vue<template> <DxDataGrid ... > <DxColumn ... :calculate-sort-value="calculateSortValue"> <DxLookup ... /> </DxColumn> </DxDataGrid> </template> <script setup> import 'devextreme/dist/css/dx.light.css'; import DxDataGrid, { DxColumn, DxLookup } from 'devextreme-vue/data-grid'; const calculateSortValue = (data) => { const column = this; const value = column.calculateCellValue(data); return column.lookup.calculateCellValue(value); } </script>
React
App.jsimport React from 'react'; import 'devextreme/dist/css/dx.light.css'; import DataGrid, { Column, Lookup } from 'devextreme-react/data-grid'; function calculateSortValue (data) { const column = this; const value = column.calculateCellValue(data); return column.lookup.calculateCellValue(value); } export default function App() { return ( <DataGrid ... > <Column ... calculateSortValue={calculateSortValue}> <Lookup ... /> </Column> </DataGrid> ); }
When headerFilter and columns.lookup are both enabled, ensure data is not
null
before accessing its properties in the displayExpr method of lookup columns:JavaScriptdisplayExpr: (data) => { return data ? `${data.ID} ${data.Name}` : '' }
The
null
value adds a "(Blanks)" item to column header filters. To avoid displaying this "(Blanks)" item, implement DataSource.postProcess in the columns.headerFilter.dataSource method of lookup columns:jQuery
index.js$(function() { $("#dataGridContainer").dxDataGrid({ columns: [{ lookup: { // ... }, headerFilter: { dataSource: function (options) { options.dataSource.postProcess = function (results) { return results.filter((item) => item.value !== null) } } } }] }) })
Angular
app.component.htmlapp.component.ts<dx-data-grid ... > <dxo-header-filter [visible]="true"></dxo-header-filter> <dxi-column ... > <dxo-header-filter ... [dataSource]="calculateDataSource" ></dxo-header-filter> <dxo-lookup ... ></dxo-lookup> </dxi-column> </dx-data-grid>
export class AppComponent { calculateDataSource (options) { options.dataSource.postProcess = function (results) { return results.filter((item) => item.value !== null) } } }
Vue
App.vue<template> <DxDataGrid ... > <DxHeaderFilter :visible="true" /> <DxColumn ... > <DxHeaderFilter ... :data-source="calculateDataSource" /> <DxLookup ... /> </DxColumn> </DxDataGrid> </template> <script setup> import DxDataGrid, { DxColumn, DxLookup, DxHeaderFilter } from 'devextreme-vue/data-grid'; const calculateDataSource = (options) => { options.dataSource.postProcess = function (results) { return results.filter((item) => item.value !== null) } } </script>
React
App.jsimport DataGrid, { Column, Lookup, HeaderFilter } from 'devextreme-react/data-grid'; function calculateDataSource(options) { options.dataSource.postProcess = function (results) { return results.filter((item) => item.value !== null) } } export default function App() { return ( <DataGrid ... > <HeaderFilter visible={true} /> <Column ... <HeaderFilter ... dataSource={calculateDataSource} /> <Lookup ... /> </Column> </DataGrid> ); }
valueExpr
Specifies the data field whose values should be replaced with values from the displayExpr field.
Values from this data field must have the same type as the column's dataField values.