Lookup Columns in Kendo Grid

There’s a number of forum posts wanting to use a lookup table for a column in a Kendo grid, where the lookup has text and a numeric id, and where all of the operations like grouping, sorting and editing need to behave as expected. The trick is to provide the lookup without causing problems for sorting, filtering, grouping or editing at the same time.
Many of the online examples use a drop down list to lookup into a list, where both the text and value for the drop down list use the text data. Real-world scenarios use a foreign key that points to an Id field in a lookup table. In this example we will start with a small sample of vacation location data that has a field named CountryId.

[
 {
 "Id":1,
 "CountryId":4,
 "Name":"Maui",
 "Description":"The second largest Hawaiian island",
 "ImagePath":"images/locations/maui.jpeg",
 "Votes":12,
 "StartDate":"2014-05-08T00:00:00-07:00",
 "EndDate":"2014-05-19T00:00:00-07:00"
 },. . .
]

Rather than show the id, we want to show a drop down list. The CountryId points to an Id field in a separate set of lookup data that describes countries: ​

[ 
 {
 "Id":1,
 "Name":"Egypt",
 "CountryCode":"EG",
 "ImagePath":"images/countries/egypt.jpeg"
 }, . . .
 ]

The key to working with foreign keys in a Kendo Grid is the column.values property. values expects an array of objects that have two fields named text and value. You can use a simple array like this:

The key to working with foreign keys in a Kendo Grid is the column.values property. values expects an array of objects that have two fields named text and value. You can use a simple array like this:

var localCountryList = [
 { text: "Egypt", value: 1 },
 { text: "France", value: 2 },
 { text: "United Kingdom", value: 3 },
 { text: "United States", value: 4 },
 { text: "United Arab Emirates", value: 5 },
 { text: "Italy", value: 6 }
];

The array is simply assigned to the values property:

$("#grid").kendoGrid({
 dataSource: dataSource,
 columns: [
 {
 field: "CountryId", title: "Country", width: 175,
 values: localCountryList, . . .

When the app runs, the Country column of the grid displays the text and not the country ids stored in the data for the grid. When the grid is edited (editable: true) or filtered (filterable: true), you get a drop down list of country names for free without having to build a custom editor or filter yourself.

Kendo123

Typically, the lookup data comes from a remote service and the field names are likely not text and value. You can use the schema.parse() method to map the incoming data to the text and value names expected by the Kendo Grid foreign key mechanism.

Review the code below and walk through the schema.parse() method. The data parameter is passed to parse(), the data is iterated and a new countries array is populated with text and value fields. The remote data also has an ImagePath to a flag picture that we can use later, so that is mapped at the same time.

Also notice that the transport.read.async property is set false for immediate synchronous loading of data. It allows you to call read() (brings records back every time) or fetch() (brings records back only the first time it is called) and count on the data being available before proceeding.

var countryLookupDS = new kendo.data.DataSource({
 transport: {
 read: {
 url: "http://localhost:51491/api/country/countries/",
 async: false
 }
 },
 schema: {
 parse: function (data) {
 var countries = [];
 for (var i = 0; i < data.length; i++) {
 var country = {
 value: data[i].Id,
 text: data[i].Name,
 imagePath: data[i].ImagePath
 };
 countries.push(country);
 }
 return countries;
 }
 }
});
countryLookupDS.read();

You would expect the values to be assigned countryLookupDS.data(), but actually, data() returns a Kendo ObservableObject and the foreign key mechanism is looking for a simple array. If you were to assign countryLookupDS.data(), there would be no error, but the Country column would display country ids and not country names. The ObservableObject toJSON() method returns the simple array we need. Now the grid columns.values assignment looks like the code below:

. . .
values: countryLookupDS.data().toJSON(),
. . .

If you were to test filtering at this point, you would get the commonly reported error “Object doesn’t support property or method ‘toLowerCase’”. The underlying issue is that the grid assumes all the columns are string if you don’t tell it otherwise. To let the grid know how to treat each field, define theDataSource.model.fields collection. It’s very important to assign the type as number for lookup columns if you want to avoid the “toLowerCase” error. You could get away with just defining the CountryId field, but now is a good time to define all the fields to avoid issues like this later.

var dataSource = new kendo.data.DataSource({ . . .
 schema: { . . .
 model: {
 id: "Id",
 fields: {
 Id: { editable: false, type: 'number' },
 CountryId: { type: 'number' },
 ImagePath: { type: 'string' },
 Name: { type: 'string' },
 Description: { type: 'string' },
 StartDate: { type: 'date' },
 EndDate: { type: 'date' },
 Votes: { type: 'number' }
 }
 }
 }
});

At this point everything is working pretty well. The country text appears in the column, editing or filtering automatically displays a drop down list of country names and grouping will show the country name, not the country id.

Unfortunately, sorting will actually be on the country id, not the country name as expected. To fix this, create your own custom sort using the Gridcolumns.sortable.compare(a,b) method. The method takes two items that are to be compared and returns -1 if a is less than b, 0 if a and b are the same and 1 if ais greater than b. In this case you want to compare country names, not ids, so you will need to lookup the names for both a and b, then compare the two. The custom getCountry(id) JavaScript function (explained below) takes an id for the country and returns a country object.

$("#grid").kendoGrid({
 dataSource: dataSource,
 columns: [
 {
 field: "CountryId", title: "Country", width: 175,
 values: countryLookupDS.data().toJSON(),
 sortable: {
 compare: function (a, b) {
 var countryA = getCountry(a.CountryId).text;
 var countryB = getCountry(b.CountryId).text;
 if (countryA < countryB) return -1
 else if (countryA === countryB) return 0
 else if (countryA > countryB) return 1;
 }
 }
 }, . . .

The getCountry() method gets a reference to a JSON array of countries, iterates the array and returns the object that corresponds to the id passed in.

function getCountry(id) {
 var data = countryLookupDS.data().toJSON();
 for (var i = 0; i < data.length; i++) {
 if (data[i].value == id) {
 return data[i];
 }
 }
}

When the application runs, the Country column sorts alphabetically even though the column is actually storing numeric ids.

Finally, the grid grouping shows the country name in the group header as expected, but it would be nice to display a flag image instead. We can reuse the customgetCountry() JavaScript function to make this happen. The groupHeaderTemplate in the country column definition below calls the getCountry() method and passesvalue, where value is the numeric CountryId. The template includes an image of the appropriate country’s flag by using the object returned by getCountry() and referencing its text for the <img> title and imagePath to populate the <img> src attribute.

. . .
columns: [
 {
 field: "CountryId", title: "Country", width: 175,
 values: countryLookupDS.data().toJSON(),
 groupHeaderTemplate:
 "<img id='location-image' Title='#= getCountry(value).text #' " +
 "src='http://localhost:51491/#= getCountry(value).imagePath #' />",
 sortable: {
 compare: function (a, b) {
 var countryA = getCountry(a.CountryId).text;
 var countryB = getCountry(b.CountryId).text;
 if (countryA < countryB) return -1
 else if (countryA === countryB) return 0
 else if (countryA > countryB) return 1;
 }
 }
 }, . . .

The running app shows the custom group heading layout complete with flag. If you hover the mouse over the flag, the country name will pop up.

Kendo125

The following two tabs change content below.

via Lookup Columns in Kendo Grid – Falafel Software Blog.