Skip to main content

DevExpress v24.1 Update — Your Feedback Matters

Our What's New in v24.1 webpage includes product-specific surveys. Your response to our survey questions will help us measure product satisfaction for features released in this major update and help us refine our plans for our next major release.

Take the survey Not interested

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.

LookupEdit-Cascading-animation.gif

#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

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

LookupEdit-Cascading-animation.gif

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.

See Also