windowsforms-116018-controls-and-libraries-editors-and-simple-controls-lookup-editors-cascading-lookups.md
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.
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"}
};
}
}
}
Imports System
Imports System.Data
Imports System.Linq
Imports System.Collections.Generic
Imports System.ComponentModel.DataAnnotations
Imports DevExpress.XtraEditors
Imports DevExpress.XtraEditors.Repository
Namespace DXGridCascadingLookups
Partial Public Class Form1
Inherits XtraForm
Public Sub New()
InitializeComponent()
gridControl1.DataSource = Order.Init()
gridControl1.ForceInitialize()
' Initializes a lookup in-place editor for the 'Product' column.
gridView1.Columns("ProductID").ColumnEdit = New RepositoryItemLookUpEdit() With {.DataSource = Product.Init(), .DisplayMember = "Name", .ValueMember = "ProductID"}
' Initializes a lookup in-place editor for the 'Category' column.
gridView1.Columns("CategoryID").ColumnEdit = New RepositoryItemLookUpEdit() With {.DataSource = Category.Init(), .DisplayMember = "CategoryName", .ValueMember = "CategoryID"}
AddHandler gridView1.ShownEditor, AddressOf gridView1_ShownEditor
End Sub
Private Sub gridView1_ShownEditor(ByVal sender As Object, ByVal e As EventArgs)
If gridView1.FocusedColumn.FieldName = "ProductID" Then
Dim lookup As LookUpEdit = TryCast(gridView1.ActiveEditor, LookUpEdit)
Dim categoryId As Integer = CInt(Math.Truncate(gridView1.GetFocusedRowCellValue("CategoryID")))
lookup.Properties.DataSource = Product.GetProductsByCategory(categoryId)
End If
End Sub
End Class
Public Class Order
<Display(Name := "Product")>
Public Property ProductID() As Integer
<Display(Name := "Category")>
Public Property CategoryID() As Integer
Public Property CreateDate() As Date
Public Shared Function Init() As List(Of Order)
Return New List(Of Order)() From {
New Order() With {.ProductID = 0, .CategoryID = 0, .CreateDate = Date.Now},
New Order() With {.ProductID = 1, .CategoryID = 1, .CreateDate = Date.Now},
New Order() With {.ProductID = 3, .CategoryID = 2, .CreateDate = Date.Now},
New Order() With {.ProductID = 3, .CategoryID = 2, .CreateDate = Date.Now}
}
End Function
End Class
Public Class Product
<Display(Order := -1)>
Public Property ProductID() As Integer
Public Property Name() As String
<Display(Order := -1)>
Public Property CategoryID() As Integer
<DisplayFormat(DataFormatString := "c2")>
Public Property Price() As Double
Public Shared Function Init() As List(Of Product)
Return New List(Of Product)() From {
New Product() With {.ProductID = 0, .Name = "Product A-1", .CategoryID = 0, .Price = 12.99},
New Product() With {.ProductID = 1, .Name = "Product B-1", .CategoryID = 1, .Price = 16.99},
New Product() With {.ProductID = 2, .Name = "Product B-2", .CategoryID = 1, .Price = 21.99},
New Product() With {.ProductID = 3, .Name = "Product C-1", .CategoryID = 2, .Price = 29.99},
New Product() With {.ProductID = 4, .Name = "Product C-2", .CategoryID = 2, .Price = 9.99}
}
End Function
Public Shared Function GetProductsByCategory(ByVal categoryId As Integer) As List(Of Product)
Return Init().Where(Function(p) p.CategoryID = categoryId).ToList()
End Function
End Class
Public Class Category
<Display(Order := -1)>
Public Property CategoryID() As Integer
Public Property CategoryName() As String
Public Shared Function Init() As List(Of Category)
Return New List(Of Category)() From {
New Category() With {.CategoryID = 0, .CategoryName = "Category A"},
New Category() With {.CategoryID = 1, .CategoryName = "Category B"},
New Category() With {.CategoryID = 2, .CategoryName = "Category C"}
}
End Function
End Class
End Namespace
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.
This example filters 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"}
};
}
}
Imports System.ComponentModel.DataAnnotations
Public Sub New()
' 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
End Sub
Private Sub lookupCategory_EditValueChanged(ByVal sender As Object, ByVal e As EventArgs)
lookupProduct.EditValue = Nothing
End Sub
Public Class Product
<Display(Order := -1)>
Public Property ProductID() As Integer
Public Property Name() As String
<Display(Order := -1)>
Public Property CategoryID() As Integer
<DisplayFormat(DataFormatString := "c2")>
Public Property Price() As Double
Public Shared Function Init() As List(Of Product)
Return New List(Of Product)() From {
New Product() With {.ProductID = 0, .Name = "Product A-1", .CategoryID = 0, .Price = 12.99},
New Product() With {.ProductID = 1, .Name = "Product B-1", .CategoryID = 1, .Price = 16.99},
New Product() With {.ProductID = 2, .Name = "Product B-2", .CategoryID = 1, .Price = 21.99},
New Product() With {.ProductID = 3, .Name = "Product C-1", .CategoryID = 2, .Price = 29.99},
New Product() With {.ProductID = 4, .Name = "Product C-2", .CategoryID = 2, .Price = 9.99}
}
End Function
End Class
Public Class Category
<Display(Order := -1)>
Public Property CategoryID() As Integer
Public Property CategoryName() As String
Public Shared Function Init() As List(Of Category)
Return New List(Of Category)() From {
New Category() With {.CategoryID = 0, .CategoryName = "Category A"},
New Category() With {.CategoryID = 1, .CategoryName = "Category B"},
New Category() With {.CategoryID = 2, .CategoryName = "Category C"}
}
End Function
End Class
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'");
}
Imports DevExpress.Data.Filtering
Imports DevExpress.XtraEditors.Controls
Private Sub LookUpEdit1_Properties_PopupFilter(sender As Object, e As PopupFilterEventArgs) Handles LookUpEdit1.Properties.PopupFilter
e.Criteria = CriteriaOperator.Parse("ShipCountry == 'Brazil'")
End Sub
In GridLookUpEdit and SearchLookUpEdit controls, handle the View’s SubstituteFilter event. Use the PopupView property to get the View.
This example utilizes the Model-View-ViewModel design pattern to implement cascading LookUpEdit controls. The example binds a child LookUpEdit data source to a collection in a view model that returns a list of business objects depending on a selected object in the master lookup.
See Also