Cascading Lookups
- 9 minutes to read
You can filter a lookup editor’s popup data source based on a value of another lookup editor. This topic shows how to customize lookup editors to perform this task.
Assume that two lookup editors (primary and secondary) 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.
In-place Lookup Editors
You need to manually filter the secondary lookup editor’s data source when a user activates the editor. To perform this task, handle the ShownEditor
event of the editor’s container (GridControl’s View, Tree List, Vertical Grid, etc.)
See the following topic for an example: How to: Filter a LookUp(ComboBox) Column Based on Another Column Value.
Standalone Lookup Editors
Standalone LookUpEdit, GridLookUpEdit, and SearchLookUpEdit controls support automatic filtering of popup data sources.
To link the secondary lookup editor to the primary lookup editor, set the following properties of the secondary lookup editor:
LookUpEditBase.CascadingOwner — Specifies the primary lookup editor. The secondary lookup editor tracks changes in the primary lookup editor. Once a value change is detected, the control uses one of the following filter criteria to filter the data source:
ForeignKeyField='Value'
— The ForeignKeyField is a key field that uniquely identifies a record in the primary lookup data source.ForeignKeyField1='Value1' AND ForeignKeyField2='Value2'...
— The control uses this expression in case of multiple key fields in the primary lookup data source.
RepositoryItemLookUpEditBase.CascadingMember (optional 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 — the lookup editor has an internal algorithm that identifies the ForeignKeyField(s) based on the information exposed by the data source. This information includes:
- Names of business objects.
- Data types of primary keys.
- Presence of certain methods in lookup data sources (when the data source is a typed DataTable).
- Key attributes.
The algorithm relies on the common naming convention of key fields. Key field names typically have the “ID”, “Key”, and “OID” suffixes.
You can mark your primary key field in the secondary lookup data source with a dedicated key attribute (for example,
System.ComponentModel.DataAnnotations.KeyAttribute
). This attribute unambiguously identifies the primary key field, and prevents a foreign key from being considered a primary key.Show Key Field Identification Details- The lookup editor identifies a subset of potential foreign keys.
- The control excludes the primary key in the secondary lookup data source from this subset. Primary keys are typically named “ID”, “Key”, or “OID”, have the “ID”, “Key”, or “OID” suffix, or have the
KeyAttribute
attribute. - The control concatenates the name of the primary business object with the “ID”, “Key”, and “OID” suffixes. The resulting strings are compared with field names in the secondary lookup data source.
- If a field match is found, this field’s data type is compared with the data type of the primary key field in the primary data source. If the field satisfies all requirements, it is considered a foreign key.
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 concatenation of foreign key names (use the ‘;’ character as a delimiter).
Customize Filter Criteria
You can customize the filter criteria applied to lookup editors as follows:
LookUpEdit — Handle the RepositoryItemLookUpEdit.PopupFilter event.
GridLookUpEdit and SearchLookUpEdit controls — Access the View stored in the editor’s RepositoryItemGridLookUpEditBase.PopupView property, and handle the View’s SubstituteFilter event.
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 automatic filtering 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.
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 + "}";
}
}
}