Cascading Lookup Columns Vs Filtered Lookup Fields

What are Cascading Lookup Columns?

Cascading lookup columns are a type of lookup field in SharePoint lists and libraries that allow the available options in one lookup column to be determined by the value selected in another lookup column. The selections made in the first lookup column will filter and constrain the options presented in the second lookup column dynamically. This creates a cascading effect where users can drill down through a hierarchy of data.

For example, consider a document library that contains fields for Country, State/Province, and City. The available values in the State/Province and City columns can be filtered based on the selected country. If “United States” is selected in the Country column, then only US states would appear as options in the State/Province column. Selecting a state would then filter the cities to those within that state.

This is accomplished by configuring lookup relationships in SharePoint that limit the options presented to the user with each subsequent selection. The lookup columns are tied to other lists which define the available options. Relationships between those lists are used to filter the cascade down to the relevant subset of options.

The code to set up such a cascading relationship would involve creating the lookup columns in the target list, then configuring the data source and filter parameters. Here is an example:

<View>
  <ViewFields>
    <FieldRef Name="Country"/> 
    <FieldRef Name="StateProvince" List="StatesList" ShowField="State" />
    <FieldRef Name="City" List="CitiesList" ShowField="City" />
  </ViewFields>

  <ParameterBindings>
    <ParameterBinding Name="StateProvince_CascadedFromCountry" Location="CAMLVariable" DefaultValue="Country"/>
    <ParameterBinding Name="City_CascadedFromState" Location="CAMLVariable" DefaultValue="StateProvince"/>
  </ParameterBindings>
</View>

This binds the State/Province options to the selected Country value, and the City options to the selected State/Province. The relationships must also be defined through lookup list settings or separate CAML code.

What are Filtered Lookup Fields?

Filtered lookup columns also constrain the available options shown to users, but do so by applying a filter to the entries loaded from the source list rather than through cascading relationships. The filter criteria is based on the value of one or more columns within that source list.

For example, consider an Asset Tracking list with a lookup column pointing to a list of Computer assets. We could configure that lookup to only show computer name values where the “Location” column in the Computers list matches the location selected for that list item.

The code would define the lookup column itself, along with a CAML Where clause to apply the appropriate filter:

<Field ID="{Guid}" 
       Type="Lookup"
       Name="Computer"
       List="Computers"
       ShowField="Title">

  <Filter>
    <Eq>
      <FieldRef Name="Location"/>
      <Value Type="Text">[Location]</Value>
    </Eq>
  </Filter>
  
</Field>  

This filters the Computer name options to only those records where the Location column value matches the [Location] variable, which would be set to the current list item’s Location value.

Key Differences

While both cascading lookups and filtered lookups serve to limit the options shown in a lookup column, there are some key differences in how they function:

• Cascading lookups filter sequentially based on previous column selections, while filtered lookups apply a one-time filter based on a single criteria
• Cascading lookups require defining relationships between lists, filtered lookups only require a filter on the source list
• Cascading lookups allow drilling down through multiple levels of hierarchical data, while filtered lookups show a filtered subset of a single list

Essentially, cascading lookups are designed for stepping users through a series of conditional, interdependent choices to populate related data fields. Filtered lookups simply constrain the options based on a match criteria.

When to Use Each

Given their distinct capabilities, cascading lookups and filtered lookups each lend themselves to certain use cases:

• Use cascading lookups when you want to guide users through selecting related or hierarchical data, such as categories and subcategories, geographical locations, product catalogs, etc. They allow drilling down through nested data.
• Use filtered lookups when you want to provide a conditional subset of options based on a single filter criteria, without chaining multiple fields. They can prevent irrelevant values from appearing.

Cascading lookups enable an interactive exploration and narrowing down of options across multiple parameters. Filtered lookups simply limit selection to a target subset of data based on matching values in the source.

Conclusion

In summary, cascading lookup columns and filtered lookup fields both provide methods to constrain the available options shown in SharePoint lookup fields. They take contrasting approaches to achieving this goal:

• Cascading lookups are sequential – each selection filters the subsequent selections across multiple columns
• Filtered lookups apply a one-time filter to the source list based on defined criteria

Cascading lookups enable a multi-step drill down through relationships in the data. Filtered lookups filter on matching criteria. Choose cascading lookups to guide iterative selection across information categories, and filtered lookups when you simply need to filter available options based on a field value match.