Cascading Lookups
- 9 minutes to read
A lookup editor can filter items in its dropdown based on a value in another lookup.
The following animation illustrates cascading lookups. The first lookup displays the categories. The second lookup displays products. Each product belongs to a specific category. When a user selects a category in the first lookup, the second lookup filters its items to display only products that match the selected category.
In-place Cascading Lookups
Handle the ShownEditor event of the lookup’s container control (for example, Data Grid, TreeList, Vertical Grid, etc.) to filter the lookup’s data source (items) based on the value in another cell.
using System;
using System.Data;
using System.Linq;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using DevExpress.XtraEditors;
using DevExpress.XtraEditors.Repository;
namespace DXGridCascadingLookups {
public partial class Form1 : XtraForm {
public Form1() {
InitializeComponent();
gridControl1.DataSource = Order.Init();
gridControl1.ForceInitialize();
// Initializes a lookup in-place editor for the 'Product' column.
gridView1.Columns["ProductID"].ColumnEdit = new RepositoryItemLookUpEdit() {
DataSource = Product.Init(),
DisplayMember = "Name",
ValueMember = "ProductID",
};
// Initializes a lookup in-place editor for the 'Category' column.
gridView1.Columns["CategoryID"].ColumnEdit = new RepositoryItemLookUpEdit() {
DataSource = Category.Init(),
DisplayMember = "CategoryName",
ValueMember = "CategoryID"
};
gridView1.ShownEditor += new EventHandler(gridView1_ShownEditor);
}
private void gridView1_ShownEditor(object sender, EventArgs e) {
if(gridView1.FocusedColumn.FieldName == "ProductID") {
LookUpEdit lookup = gridView1.ActiveEditor as LookUpEdit;
int categoryId = (int)gridView1.GetFocusedRowCellValue("CategoryID");
lookup.Properties.DataSource = Product.GetProductsByCategory(categoryId);
}
}
}
public class Order {
[Display(Name = "Product")]
public int ProductID { get; set; }
[Display(Name = "Category")]
public int CategoryID { get; set; }
public DateTime CreateDate { get; set; }
static public List<Order> Init() {
return new List<Order>() {
new Order(){ ProductID = 0, CategoryID = 0, CreateDate = DateTime.Now },
new Order(){ ProductID = 1, CategoryID = 1, CreateDate = DateTime.Now },
new Order(){ ProductID = 3, CategoryID = 2, CreateDate = DateTime.Now },
new Order(){ ProductID = 3, CategoryID = 2, CreateDate = DateTime.Now },
};
}
}
public class Product {
[Display(Order = -1)]
public int ProductID { get; set; }
public string Name { get; set; }
[Display(Order = -1)]
public int CategoryID { get; set; }
[DisplayFormat(DataFormatString = "c2")]
public double Price { get; set; }
static public List<Product> Init() {
return new List<Product>() {
new Product(){ ProductID = 0, Name = "Product A-1", CategoryID = 0, Price = 12.99 },
new Product(){ ProductID = 1, Name = "Product B-1", CategoryID = 1, Price = 16.99 },
new Product(){ ProductID = 2, Name = "Product B-2", CategoryID = 1, Price = 21.99 },
new Product(){ ProductID = 3, Name = "Product C-1", CategoryID = 2, Price = 29.99 },
new Product(){ ProductID = 4, Name = "Product C-2", CategoryID = 2, Price = 9.99 },
};
}
static public List<Product> GetProductsByCategory(int categoryId) {
return Init().Where<Product>(p => p.CategoryID == categoryId).ToList<Product>();
}
}
public class Category {
[Display(Order = -1)]
public int CategoryID { get; set; }
public string CategoryName { get; set; }
static public List<Category> Init() {
return new List<Category>() {
new Category(){ CategoryID = 0, CategoryName = "Category A"},
new Category(){ CategoryID = 1, CategoryName = "Category B"},
new Category(){ CategoryID = 2, CategoryName = "Category C"}
};
}
}
}
Standalone Cascading Lookups
LookUpEdit, GridLookUpEdit, and SearchLookUpEdit controls can automatically filter their data sources based on a value in another lookup.
Use the secondary lookup’s CascadingOwner property to specify the primary lookup. Lookups use an internal algorithm that identifies a key field in a data source. The algorithm checks the names of data objects, the key attributes, and the data type of the primary keys. The algorithm is based on a common naming convention of key fields (for example, ID, Key, and OID).
Use the CascadingMember property to manually specify a key field of the secondary lookup if the lookup’s algorithm cannot identify a key field. Use the ; character to delimit field names in a compound foreign key field.
Example
This example shows how to filter the dropdown items of one lookup editor (Products) based on a value in another lookup (Category).
using System.ComponentModel.DataAnnotations;
public Form1() {
// Initializes the primary lookup that displays product categories.
lookupCategory.Properties.DataSource = Category.Init();
lookupCategory.Properties.DisplayMember = "CategoryName";
lookupCategory.Properties.ValueMember = "CategoryID";
// Initializes the secondary lookup that displays products.
lookupProduct.Properties.DataSource = Product.Init();
lookupProduct.Properties.DisplayMember = "Name";
lookupProduct.Properties.ValueMember = "ProductID";
// Links the secondary lookup to the primary lookup.
lookupProduct.CascadingOwner = lookupCategory;
}
private void lookupCategory_EditValueChanged(object sender, EventArgs e) {
lookupProduct.EditValue = null;
}
public class Product {
[Display(Order = -1)]
public int ProductID { get; set; }
public string Name { get; set; }
[Display(Order = -1)]
public int CategoryID { get; set; }
[DisplayFormat(DataFormatString = "c2")]
public double Price { get; set; }
static public List<Product> Init() {
return new List<Product>() {
new Product(){ ProductID = 0, Name = "Product A-1", CategoryID = 0, Price = 12.99 },
new Product(){ ProductID = 1, Name = "Product B-1", CategoryID = 1, Price = 16.99 },
new Product(){ ProductID = 2, Name = "Product B-2", CategoryID = 1, Price = 21.99 },
new Product(){ ProductID = 3, Name = "Product C-1", CategoryID = 2, Price = 29.99 },
new Product(){ ProductID = 4, Name = "Product C-2", CategoryID = 2, Price = 9.99 },
};
}
}
public class Category {
[Display(Order = -1)]
public int CategoryID { get; set; }
public string CategoryName { get; set; }
static public List<Category> Init() {
return new List<Category>() {
new Category(){ CategoryID = 0, CategoryName = "Category A"},
new Category(){ CategoryID = 1, CategoryName = "Category B"},
new Category(){ CategoryID = 2, CategoryName = "Category C"}
};
}
}
Custom Filtering
Handle the PopupFilter event to manually filter lookup items.
using DevExpress.Data.Filtering;
using DevExpress.XtraEditors.Controls;
private void lookUpEdit1_Properties_PopupFilter(object sender, PopupFilterEventArgs e) {
e.Criteria = CriteriaOperator.Parse("ShipCountry == 'Brazil'");
}
In GridLookUpEdit and SearchLookUpEdit controls, handle the View’s SubstituteFilter event. Use the PopupView property to get the View.