Cascading Lookups
- 9 minutes to read
In standalone mode, a lookup editor supports automatic filtration of its popup data source based on a value of another lookup editor. This topic shows how to customize lookup editors to perform this task.
Automatic filtration of popup data sources is supported for the LookUpEdit, GridLookUpEdit and SearchLookUpEdit controls in standalone mode. In in-place mode, data source filtration needs to be performed manually, as demonstrated in the How to: Filter a LookUp(ComboBox) Column Based on Another Column Value example.
Assume that two lookup editors display lists of Categories and Products in their dropdowns, respectively. Each Product corresponds to a certain Category. When an end-user selects a Category in the first lookup editor, the second editor’s popup data source needs to be filtered to show only products that correspond to the selected category.
To link the secondary lookup editor to the primary lookup editor, the following properties on the secondary lookup editor are used.
LookUpEditBase.CascadingOwner - Specifies the primary lookup editor, whose value changes are tracked by the secondary lookup editor. Once a value change is detected, corresponding filter criteria are created and applied to the secondary popup data source. The filter criteria are created in the form:
ForeignKeyField='Value'where the ForeignKeyField is a key field that uniquely identifies a record in the primary lookup data source.In case of multiple key fields in the primary lookup data source, the filter criteria are formed as follows.
ForeignKeyField1=’Value’ AND ForeignKeyField2=’Value2’…
RepositoryItemLookUpEditBase.CascadingMember (can be omitted in some cases) - Allows you to manually specify the ForeignKeyField(s) for the filter criteria applied to the secondary popup data source. In most cases, there is no need to specify the CascadingMember property, as the lookup editor provides an internal algorithm that identifies the ForeignKeyField(s) based on names of business objects, data types of primary keys, certain methods provided by lookup data sources (when the data source is a typed DataTable), key attributes, and other logic. The algorithm relies on the naming convention of key fields using the “ID”, “Key” and “OID” strings/suffixes.
Here is the general description of how the foreign key identification works when lookup data sources are collections of business objects (the algorithm may be changed in future versions). When identifying a subset of potential foreign keys, the lookup editor first excludes the primary key on the secondary lookup data source from this subset. Primary keys are typically named “ID”, “Key” or “OID”, or they have the “ID”, “Key” or “OID” suffix. You can manually mark your primary key field on the secondary lookup data source with a dedicated key attribute (e.g., System.ComponentModel.DataAnnotations.KeyAttribute). This unambiguously identifies the primary key field and prevents a foreign key from being considered a primary key.
The name of objects in the primary data source is concatenated with the “ID”, “Key” and “OID” suffixes. The result of concatenation is compared with field names in the secondary lookup data source. If a match is found, this found field’s data type is compared with the data type of the primary key field in the primary data source. If the found field satisfies all requirements, it is considered a foreign key and is used to build the filter criteria.
If the internal algorithm is not able to identify a foreign key, manually assign this key to the CascadingMember property. In case of multiple foreign keys, set the CascadingMember property to a string that concatenates foreign key names by delimiting them with the ‘;’ character.
You can customize the filter criteria applied to the LookUpEdit control’s popup data source by handling the RepositoryItemLookUpEdit.PopupFilter event. For GridLookUpEdit and SearchLookUpEdit, you can customize the filter by handling the ColumnView.SubstituteFilter event for a ColumnView object stored in the RepositoryItemGridLookUpEditBase.PopupView property.
Example
This example shows how to filter the popup data source of a standalone LookUpEdit control based on the value of another standalone LookUpEdit control.
The example demonstrates the automatic filtration feature supported by the LookUpEditBase.CascadingOwner property.
In the example, data sources of two lookup editors are lists of Category and Product business objects, respectively. When an end-user selects a certain category in the first lookup editor, the second lookup editor’s popup should display those products that correspond to the selected category.
public class Category {
public int Key { get; set; }
public string CategoryName { get; set; }
}
public class Product {
public int ID { get; set; }
public int CategoryID { get; set; }
public string ProductName { get; set; }
}
For the second lookup editor, the LookUpEditBase.CascadingOwner property is set to the first lookup editor. This specifies that the editor builds filter criteria based on the first lookup editor’s value.
The filter criteria applied to the second data source is created in the form:ForeignKeyField='Value'
The lookup editor’s built-in algorithm identifies the “CategoryID” field as the foreign key for the filter criteria.
The algorithm identifies the primary key field (“Key”) for the first business object (this field’s data type is taken into account for further analysis).
The algorithm also obtains the first business object name (“Category”) and concatenates it with the “ID”, “Key” and “OID” suffixes. The “Category”+”ID” combination produces a valid property name in the second data source (“CategoryID”). In addition, the types of the “CategoryID” and “Key” fields match. Thus “CategoryID” is regarded as a foreign key field.
Note
The algorithm may be changed in future versions.
Note
If the built-in algorithm is not able to find a foreign key(s) in your own projects, you can manually specify the foreign key(s) with the RepositoryItemLookUpEditBase.CascadingMember property.
Note
A complete sample project is available at https://github.com/DevExpress-Examples/how-to-filter-standalone-lookupedit-based-on-another-lookupedits-valuecascadingowner-t380359
using DevExpress.XtraEditors;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace Lookup_Cascading {
public partial class Form1 : Form {
public Form1() {
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e) {
List<Category> categories = new List<Category> {
new Category(){ Key = 0, CategoryName = "Beverages" },
new Category(){ Key = 1, CategoryName = "Grains" },
new Category(){ Key = 2, CategoryName = "Seafood" },
};
lookUpEdit1.Properties.DataSource = categories;
lookUpEdit1.Properties.DisplayMember = "CategoryName";
lookUpEdit1.Properties.KeyMember = "Key";
List <Product> products = new List<Product> {
new Product(){ ID=0, ProductName="Chang", CategoryID = 0 },
new Product(){ ID=1, ProductName="Ipoh Coffee", CategoryID = 0 },
new Product(){ ID=2, ProductName="Ravioli Angelo", CategoryID = 1 },
new Product(){ ID=3, ProductName="Filo Mix", CategoryID = 1 },
new Product(){ ID=4, ProductName="Tunnbröd", CategoryID = 1 },
new Product(){ ID=5, ProductName="Konbu", CategoryID = 2 },
new Product(){ ID=6, ProductName="Boston Crab Meat", CategoryID = 2 }
};
lookUpEdit2.Properties.DataSource = products;
lookUpEdit2.Properties.KeyMember = "ID";
lookUpEdit2.Properties.DisplayMember = "ProductName";
lookUpEdit2.CascadingOwner = lookUpEdit1;
// The following line is not required, as lookUpEdit2 automatically identifies a foreign key ("CategoryID") in its data source
// by appending the "ID" suffix to primary object's class name ("Category")
//lookUpEdit2.Properties.CascadingMember = "CategoryID";
}
string getObjectString(object obj) {
if (obj == null) return "EditValue: null";
return obj.ToString();
}
private void timer1_Tick(object sender, EventArgs e) {
labelControl1.Text = getObjectString(lookUpEdit1.EditValue);
labelControl2.Text = getObjectString(lookUpEdit2.EditValue);
}
private void lookUpEdit1_EditValueChanged(object sender, EventArgs e)
{
lookUpEdit2.EditValue = null;
}
}
public class Category {
public int Key { get; set; }
public string CategoryName { get; set; }
public override string ToString() {
return "Category object" + " { Key:" + Key + ", CategoryName:" + CategoryName + "}";
}
}
public class Product {
public int ID { get; set; }
public int CategoryID { get; set; }
public string ProductName { get; set; }
public override string ToString() {
return "Product object" + " { CategoryID:" + CategoryID + ", ProductName:" + ProductName + "}";
}
}
}