Cascading Lookups

Jan 25, 2023
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.

Cascading Lookups in WinForms Data Grid

C#
VB.NET

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

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).

C#
VB.NET

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

Custom Filtering

Handle the PopupFilter event to manually filter lookup items.

C#
VB.NET

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.

Controls

Tools

Controls and Extensions

Tools

Maintenance Mode

Controls

Tools

Controls and Extensions

Tools

Maintenance Mode

In-place Cascading Lookups

Standalone Cascading Lookups

Example

Custom Filtering