Creating Filtered Lookup Fields In Sharepoint

What are Filtered Lookup Fields

Filtered lookup fields are a type of lookup field in SharePoint that allows users to filter the values available for selection based on predefined criteria. They serve the purpose of only showing relevant options to users by connecting items between SharePoint lists and libraries based on metadata and column values.

The key definition of a filtered lookup field is a lookup field with restrictions configured on which values are available from the source list. It dynamically filters the options presented based on filters set up by the SharePoint administrator or designer. This allows for more targeted options catering specifically to the item the field is being utilized for.

Filtered lookup columns should be utilized instead of standard lookup columns when the goal is to restrict available options for better accuracy. For example, limiting a customer selection to only customers located in the Midwest when the list item is designated for Midwestern accounts.

How Filtered Lookup Fields Work

Filtered lookup columns work through preset filters that target column values in the source list and dynamically change the available results. When a user goes to select an option from a filtered lookup, it connects to the source list specified and filters down based on filters set on specific columns.

For example, a filtered lookup could be configured to only show results where the “Location” column in the source list equals “New York”. So when a user goes to make their selection, only items meeting that criteria would load as options.

The difference between cascaded lookup fields and filtered lookup fields is that cascaded lookups narrow options based on previous selections in a chain of fields. Filtered lookups merely filter on preset metadata conditions without regard to other column selections.

Setting Up a Filtered Lookup Field

When creating a new filtered lookup column through the SharePoint interface, the first step is to navigate to List Settings > Create Column and choose the type as “Lookup” column. On the next page, you must configure the source list you want the lookup to connect to just as with a standard lookup field.

However, there will also be a “Filter” section visible where filter criteria can be set based on columns in the source list. For example, choosing the “Location” column from the linked source list and setting filter as “Equals” Midwest. This will dynamically limit results to only items with Midwest in the location column.

Configuring the Filter Criteria

Some common examples of filter criteria configuration syntax includes:

• Location Equals Midwest
• Status Not Equals Closed
• Price Greater Than 500
• Product Contains Camera

Looking up user profiles is a common usage of filtered fields as well. To filter based on a “Person or Group” type of column, the syntax looks like:

[Me] (=Filters for current user)

[Today] (=Filters for current date)

Connecting Filtered Fields to Multiple Lists

A powerful capability of filtered lookup fields is the ability to connect them to multiple source lists, not just a single list. After configuring the initial source, additional lookup list sources can be specified under the “Add a new source…” link.

This allows combining relevant options from diverse lists across the SharePoint farm based on the filter criteria. For example, filtering product catalogs from 5 different product category sites all into one filtered lookup column.

When utilizing multiple sources, be conscious of access rights and data separation needs between the list sites. Changes to source lists can also impact downstream filtered fields, so be cautious when renaming or removing columns referenced in filter rules.

Example Code for Filtered Lookup Fields

In SharePoint columns like filtered lookup fields can either be created through the UI or by using declarative, imperative code. Here is an example CAML snippet for declaratively creating a filtered lookup in a ListInstance schema:

<Field Type="Lookup"
   DisplayName="FilteredLocation" 
   List="Locations"
   ShowField="Title"
   FilterOp="Equals"
   FilterVal="Midwest">
</Field>

To imperatively create filtered lookups via JavaScript, code like below can be used:

var filteredField = SP.FieldLookup.newFilteredLookupField("{ListId}", "{FieldName}");
filteredField.setFilterOperation("Equals"); 
filteredField.setFilterValue("Midwest");

Troubleshooting Filtered Lookups

Some common issues that can arise with filtered lookup columns include no values appearing, wrong results showing, or errors when selecting an option. Typical solutions include:

• Verify filter criteria is typed correctly based on source list metadata
• Check for problems with access rights between the lists
• Enable “Display list” on column to show filtering working correctly
• Review list thresholds are not blocking filtered items

Testing filtered lookups in a dev environment prior to production use allows validation of expected filtering behavior. The ULS logs can also provide helpful troubleshooting clues on configuration issues.

Additional Tips for Filtered Fields

Due to their dynamic nature, filtered lookups can impact performance at scale if extremely complex filters are applied. Best practices are to avoid unnecessary multi-source chaining and apply reasonable text filters.

Displaying the source list for reference and manually limiting height/number of rows is also advised for better user experience. Understanding caching and index utilization also helps tune large result set behavior.