Cascading Lookups

• Feb 24, 2022
• 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

  1. The lookup editor identifies a subset of potential foreign keys.
  2. 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.
  3. 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.
  4. 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.

C#

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.

View Example

Form1.cs

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 + "}";
        }
    }


}

Program.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Lookup_Cascading {
    static class Program {
        /// <summary>
        /// The main entry point for the application.
        /// </summary>
        [STAThread]
        static void Main() {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new Form1());
        }
    }
}

Form1.vb

Imports DevExpress.XtraEditors
Imports System
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.ComponentModel.DataAnnotations
Imports System.Data
Imports System.Drawing
Imports System.Linq
Imports System.Text
Imports System.Threading.Tasks
Imports System.Windows.Forms

Namespace Lookup_Cascading
    Partial Public Class Form1
        Inherits Form

        Public Sub New()

            InitializeComponent()
        End Sub

        Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
            Dim categories As New List(Of Category)() From { _
                New Category() With {.Key = 0, .CategoryName = "Beverages"}, _
                New Category() With {.Key = 1, .CategoryName = "Grains"}, _
                New Category() With {.Key = 2, .CategoryName = "Seafood"} _
            }
            lookUpEdit1.Properties.DataSource = categories
            lookUpEdit1.Properties.DisplayMember = "CategoryName"
            lookUpEdit1.Properties.KeyMember = "Key"


            Dim products As New List(Of Product)() From { _
                New Product() With {.ID=0, .ProductName="Chang", .CategoryID = 0}, _
                New Product() With {.ID=1, .ProductName="Ipoh Coffee", .CategoryID = 0}, _
                New Product() With {.ID=2, .ProductName="Ravioli Angelo", .CategoryID = 1}, _
                New Product() With {.ID=3, .ProductName="Filo Mix", .CategoryID = 1}, _
                New Product() With {.ID=4, .ProductName="Tunnbröd", .CategoryID = 1}, _
                New Product() With {.ID=5, .ProductName="Konbu", .CategoryID = 2}, _
                New Product() With {.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";


        End Sub

        Private Function getObjectString(ByVal obj As Object) As String
            If obj Is Nothing Then
                Return "EditValue: null"
            End If
            Return obj.ToString()
        End Function
        Private Sub timer1_Tick(ByVal sender As Object, ByVal e As EventArgs) Handles timer1.Tick
            labelControl1.Text = getObjectString(lookUpEdit1.EditValue)
            labelControl2.Text = getObjectString(lookUpEdit2.EditValue)
        End Sub

        Private Sub lookUpEdit1_EditValueChanged(sender As Object, e As EventArgs) Handles lookUpEdit1.EditValueChanged
            lookUpEdit2.EditValue = Nothing

        End Sub
    End Class

    Public Class Category
        Public Property Key() As Integer
        Public Property CategoryName() As String
        Public Overrides Function ToString() As String
            Return "Category object" & " { Key:" & Key & ", CategoryName:" & CategoryName & "}"
        End Function
    End Class

    Public Class Product
        Public Property ID() As Integer
        Public Property CategoryID() As Integer
        Public Property ProductName() As String
        Public Overrides Function ToString() As String
            Return "Product object" & " { CategoryID:" & CategoryID & ", ProductName:" & ProductName & "}"
        End Function
    End Class


End Namespace

Program.vb

Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Threading.Tasks
Imports System.Windows.Forms

Namespace Lookup_Cascading
    Friend NotInheritable Class Program

        Private Sub New()
        End Sub

        ''' <summary>
        ''' The main entry point for the application.
        ''' </summary>
        <STAThread> _
        Shared Sub Main()
            Application.EnableVisualStyles()
            Application.SetCompatibleTextRenderingDefault(False)
            Application.Run(New Form1())
        End Sub
    End Class
End Namespace