Case Study

Mastering Hierarchical Data in SharePoint Online: A Complete Guide to Region > Country > City Cascading Lookups

Introduction: Solving Global Employee Data Challenges

In multinational organizations, managing accurate employee location data across regions, countries, and cities is a common struggle. Manual data entry often leads to errors like "Paris, USA" or "Berlin, Canada"—mistakes that create inconsistencies in HR records, reporting, and operations.

The Solution? Cascading Lookups—a dynamic filtering system that enforces logical hierarchies, ensuring users select valid combinations (e.g., selecting a Region first, then a Country, and finally a City).

This guide provides a detailed, followable walkthrough for implementing cascading lookups in SharePoint Online using BoostSolutions' Cascaded Lookup App—from setup to testing—ensuring your global employee directory remains error-free.

  • Region → Country → City cascading dropdowns
  • Cross-site implementation (source list in a different site within the same site collection)
  • Step-by-step column configuration
  • Foolproof data integrity

Scenario: Global Employee Directory with 3-Level Cascading Lookups

1. System Structure & Data Flow

Source List: World Locations Current List: Employee Directory
Site: Reference Data Hub
https://contoso.sharepoint.com/sites/ReferenceDataHub 		Site: Employee Records
https://contoso.sharepoint.com/sites/EmployeeRecords
Columns: Region, Country, City Columns: Employee Name, Employee Region, Employee Country, Employee City

2. Column Relationships

Source List (World Locations) Columns:

Column Type
Region Single Line of Text
Country Single Line of Text
City Single Line of Text

Current List (Employee Directory) Columns:

Column Type Relationship Cross-Site Reference
Employee Name Single Line of Text    
Employee Region Cascaded Lookup Parent of Employee Country Cross-site lookup → Region in Source List
Employee Country Cascaded Lookup Child of Employee Region
Parent of Employee City		 Cascaded Lookup → Country in Source List
Employee City Cascaded Lookup Child of Employee Country Cascaded Lookup → City in Source List

Cascading Logic:

  1. User selects Employee Region → Filters Employee Country dropdown
  2. User selects Employee Country → Filters Employee City dropdown

Implementation Guide

Phase 1: Prepare the Source List (World Locations)

📍 Site: Reference Data Hub

  1. Create the list:

    • Go to Site Contents → + New → List
    • Name: World Locations

  2. Add columns:

    • Region (Single line of text)
    • Country (Single line of text)
    • City (Single line of text)

  3. Enter sample data:

    Region Country City
    Americas USA New York
    Americas USA Los Angeles
    Europe Germany Berlin
    Sample data in World Locations list showing Region, Country, and City columns with example entries

Phase 2: Prepare the Current List (Employee Directory)

📍 Site: Employee Records

Prerequisites:

  1. Add/Deploy BoostSolutions' Cascaded Lookup App:

    • Ensure the app is installed in your SharePoint Online environment

  2. Create the Employee Directory list with:

    • Employee Name (Single line of text)

      Employee Name
       
       
      Employee Directory list with Employee Name column created

Phase 3: Creating Cascading Lookup Columns in the Current List (Employee Directory)

📍 Site: Employee Records

Step 1: Create Employee Region (Top-Level Cascaded Lookup)

  1. Navigate to the Employee Directory list

    • Go to your SharePoint Online site Employee Records
    • Open the Employee Directory list

  2. Access Cascaded Lookup Settings

    • Click on Cascaded Lookup Settings in the list command bar at the top of the page
    • This opens the Cascaded Lookup Settings panel
      • Cascaded Lookup Settings panel opened in the list command bar

  3. Create a new cascaded lookup column

    • In the Manage your Cascaded Lookup column settings section
    • Expand the Create a Cascaded Lookup Column dropdown menu
    • Select New Cascaded Lookup Column
      • Create a Cascaded Lookup Column with New Cascaded Lookup Column option

  4. Configure the Employee Region column

    • Name: Enter "Employee Region"

    • Data Source Configuration:

      • Expand the "Get information from this site" dropdown
      • Select: Reference Data Hub
      • Expand the "Select a list as a source" dropdown
      • Select: World Locations
      • Expand the "Select a column from the list above" dropdown
      • Select: Region
        • New Cascaded Lookup Column configuration panel for Employee Region with data source settings

    • Cascading Settings:

      • Skip this section (since this is the top-level lookup)

    • Remove Duplicate Items:

      • Toggle on the Remove Duplicate Items from the dropdown list switch to avoid displaying duplicate entries in the cascaded lookup column dropdown list.
        • Remove Duplicate Items toggle switch in the New Cascaded Lookup Column panel

    • Additional Options:

      • Leave other options as default
    • Click Save

Step 2: Create Employee Country (Mid-Level Cascaded Lookup)

  1. Access the cascaded lookup settings again

    • Click Cascaded Lookup Settings in the list command bar
    • Expand Create a Cascaded Lookup Column
    • Select New Cascaded Lookup Column

  2. Configure the Employee Country column

    • Name: Enter "Employee Country"

    • Data Source Configuration:

      • Expand "Get information from this site" → Select Reference Data Hub
      • Expand "Select a list as a source" → Select World Locations
      • Expand "Select a column from the list above" → Select Country
        • New Cascaded Lookup Column configuration panel for Employee Country with data source settings

    • Cascading Settings:

      • Click Set Cascading → +Add cascading
      • Parent Column in This List: Select "Employee Region" (in Current List_ Employee Directory)
      • Filter by Column in Source List: Select "Region" (in Source List_World Locations)
        • Cascading Settings panel for Employee Country showing parent column and filter column configuration

    • Remove Duplicate Items:

      • Toggle on the Remove Duplicate Items from the dropdown list switch to avoid displaying duplicate entries in the cascaded lookup column dropdown list.

    • Additional Options:

      • Leave other options as default
    • Click Save

Step 3: Create Employee City (Bottom-Level Cascaded Lookup)

  1. Access the cascaded lookup settings again

    • Click Cascaded Lookup Settings in the list command bar
    • Expand Create a Cascaded Lookup Column
    • Select New Cascaded Lookup Column

  2. Configure the Employee City column

    • Name: Enter "Employee City"

    • Data Source Configuration:

      • Expand "Get information from this site" → Select Reference Data Hub
      • Expand "Select a list as a source" → Select World Locations
      • Expand "Select a column from the list above" → Select City
        • New Cascaded Lookup Column configuration panel for Employee City with data source settings

    • Cascading Settings:

      • Click Set Cascading → +Add cascading
      • Parent Column in This List: Select "Employee Country" (in Current List_ Employee Directory)
      • Filter by Column in Source List: Select "Country" (in Source List_World Locations)
        • Cascading Settings panel for Employee City showing parent column and filter column configuration

    • Additional Options:

      • Leave other options as default
    • Click Save

Verification of Cascaded Lookup Columns

After creating all three columns, they will be listed below the Create a Cascaded Lookup Column dropdown in the Cascaded Lookup Settings panel:

  • Employee Region
  • Employee Country
  • Employee City
    • List of created Cascaded Lookup columns in the Cascaded Lookup Settings panel

Important Notes

  • Cross-Site Lookup: The app successfully references the source list (World Locations) even though it's in a different site (Reference Data Hub)

  • Hierarchy Enforcement: The cascading logic ensures proper filtering:

    • Selecting an Employee Region filters the Employee Country dropdown
    • Selecting an Employee Country filters the Employee City dropdown
  • Data Integrity: Only valid combinations can be selected (e.g., you can't accidentally choose "Berlin" when "USA" is selected as the country)

Appendix: Supported Column Types for Cascaded Lookups

Reference Guide for Column Type Compatibility

1. For "Select a column from the list above" (Source Column):

Supported column types include:

  • Single Line of Text
  • Number
  • Date and Time
  • Calculated (when output is single line of text)
  • ID
  • Modified
  • Created
  • Version

2. For "Parent column in this list":

Supported column types include:

  • Choice (except when using the "filled in" value mode)
  • SharePoint Lookup
  • Cascaded Lookup

3. For "Filter by column in Source list":

Supported column types include:

  • Choice
  • Single Line of Text
  • SharePoint Lookup (single value only, where the source column is not Number or Date and Time)
  • Cascaded Lookup (single value only, where the source column is not Number or Date and Time)

Phase 4: Testing the Cascading Workflow

Testing Method 1: Adding a New Employee Record

  1. Initiate new item creation

    • In the Employee Directory list
    • Click New Cascaded Item in the list command bar
      • New Cascaded Item form opened from the list command bar

  2. Enter basic information

    • Employee Name: Enter "John Doe"

  3. Test location selection

    • First Selection:

      • Select Employee Region: "Americas"
      • Observe: Employee Country dropdown updates to show only "USA"
        • Employee Region dropdown showing Americas selected and Employee Country filtered to show USA

    • Second Selection:

      • Select Employee Country: "USA"
      • Observe: Employee City dropdown updates to show "New York" and "Los Angeles"
        • Employee Country dropdown showing USA selected and Employee City filtered to show New York and Los Angeles

    • Final Selection:

      • Select Employee City: "New York"

  4. Save the new record

    • Click Save
    • Verify the new entry appears correctly:
    Employee Name Employee Region Employee Country Employee City
    John Doe Americas USA New York
    New employee record saved with John Doe's location information

Testing Method 2: Editing an Existing Employee Record

  1. Open the Employee Directory list

  2. Initiate edit mode

    • Locate the existing employee (e.g., "John Doe")
    • Click on the check circle before the item to select it.
    • Click Edit Cascaded Item in the list command bar
      • Edit Cascaded Item command selected for existing employee record

  3. Test the cascading behavior

    • Current Values:

      • Employee Region: "Americas"
      • Employee Country: "USA"
      • Employee City: "New York"

  4. Modify the location data

    • Change Employee Region to "Europe"

      • Observe: Employee Country dropdown automatically updates to show only "Germany"
        • Employee Region changed to Europe and Employee Country filtered to show Germany

    • Select Employee Country: "Germany"

      • Observe: Employee City dropdown updates to show only "Berlin"
        • Employee Country changed to Germany and Employee City filtered to show Berlin
    • Select Employee City: "Berlin"

  5. Save changes

    • Click Save
    • Verify the updated record displays correctly:
    Employee Name Employee Region Employee Country Employee City
    John Doe Europe Germany Berlin
    Updated employee record saved with John Doe's new location information

Key Benefits of This Solution

  • ✔ No More Data Errors – Ensures only valid combinations (e.g., no "Berlin, USA").
  • ✔ User-Friendly – Dropdowns dynamically filter, reducing manual input.
  • ✔ Centralized Management – Master data stored in one list, used across sites.
  • ✔ Works Cross-Site – Source list can be in a different SharePoint site.

Key Configuration Summary

Cascaded Lookup Column (Current List) Parent Column (Current List) Filter by Column (Source List) Purpose
Employee Region None None Root level
Employee Country Employee Region Region (Source) Filters Employee Country by Employee Region
Employee City Employee Country Country (Source) Filters Employee City by Employee Country

Login