Others

How to Create Cascading SharePoint Lookup Lists and Custom Forms

Chanuka Geekiyanage Sep 27, 2026 869 views

Question details

The user wants to build cascading drop-down lists in customized SharePoint forms but finds lookup columns and managed metadata fields too difficult and complex to implement.

How to Create Cascading SharePoint Lookup Lists and Custom Forms

Product: SharePoint
Device & OS: not provided
Scenario: Creating cascading or dependent drop-down lists within customized SharePoint forms.
Observed behavior: Native lookup columns make cascading drop-downs and Patch formulas overly complex because they require both an ID and a display value to function correctly.

Before you start

Ensure you have the necessary permissions to edit the SharePoint list structure and access to the form customization environment (such as Power Apps) before making changes.

Solution 1Recommended

Use Numeric Fields and Filter Formulas

Bypass the complexity of native lookup columns by using a numeric field to store the parent item ID and filtering the controls accordingly.

By changing the data structure to use a numeric field instead of a lookup field, you can easily simulate list relationships. This significantly simplifies the Patch formula and allows for straightforward filtering of drop-down controls.

Remove native lookup fields

Modify your SharePoint list structure so the lists are no longer related through complex lookup columns.

Create a numeric ID column

Add a new numeric field to your list to store the parent-list IDs, which will simulate the relational connection.

Set up a source list

Create a dedicated source list containing the categories and subcategories for your cascading drop-downs.

Apply Distinct and Filter formulas

In your custom form, use the Distinct and Filter formulas on your drop-down or combo box controls.

Configure cascading logic

Filter each subsequent drop-down control using the selected value from the previously selected control to create the cascading effect.

Simplified Patch Formula: Because you are now passing a simple numeric ID rather than both an ID and a display value, your Patch formula will run without lookup column errors.

Free Microsoft Office alternative

Easily Manage Data Lists with WPS Office

If you are looking for an easier way to handle dependent drop-down lists and data validation without complex SharePoint formulas, WPS Office provides a free, lightweight, and highly compatible alternative for your spreadsheet needs.

1. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite.
2. Open WPS Spreadsheet: Launch WPS Spreadsheet and open or create a new workbook to organize your data.
3. Use Data Validation: Navigate to the Data tab and use the Data Validation feature to set up straightforward drop-down lists without writing complex code.

Seamless format compatibility with Microsoft Excel, Word, and PowerPoint.Intuitive Data Validation tools to create cascading drop-down lists easily.Lightweight architecture ensures fast loading on any device.Free to use with a familiar, user-friendly interface for quick onboarding.

microsoft office alternative - wps office

Frequently Asked Questions

Why are SharePoint lookup columns difficult to use in custom forms?

Lookup columns require both an ID and a display value to be passed in formulas (like the Patch formula). This dual-requirement makes setting up cascading drop-down relationships much more complex than using plain text or numeric fields.

What is the advantage of using a numeric field for list relationships?

Using a numeric field to store the parent-list ID simplifies your list data structure. It makes writing Patch and Filter formulas easier and helps avoid common errors associated with saving native lookup fields.

Can I build cascading drop-downs without using managed metadata?

Yes. By creating a separate source list and using Distinct and Filter formulas directly on your drop-down or combo box controls, you can effectively build and manage cascading lists without relying on managed metadata.

Office Quick Solutions