SharePoint Cascaded Lookup is a powerful look-up tool that greatly enhances the lookup functionality already native to SharePoint. It quickly cross-references existing data from a list or site within the same site collection and then applies a combination of filters to reduce the number of options presented in the dropdown menu of your lookup field. To use it, you set a combination of filters available in both the source list (the list you are looking up from) and the target list (the list where you want to add looked-up information) to ensure that only applicable values are displayed as choices in the dropdown list.
Standard SharePoint Lookup Column
SharePoint Cascaded Lookup Column
SharePoint Cascaded Lookup creates relationships between columns in the same list: you build a parent-child relationship between a set of columns so that the value you choose in the parent column determines the options presented in the child column.
Above, a cascading series of parent-child columns filter the options presented in the drop-down menu. Here, the Continent column is a parent to the Country column, so choosing North America filters the options presented in the Country column so that only countries in North America are presented as options. The Country column is a parent to the State column, so the value chosen in the Country column filters the State column, which in turn filters the options available in the City column.
How Does It Work?
In order to take full advantage of the functionality of Cascaded Lookup, you need to establish several things:
- The Source List, which is the list from which Cascaded Lookup will look up information. The Source list can be in any site.
- The Source Column, located in the Source List, that contains all of the possible values for your drop-down menu.
- The Filter Column, also located in the Source List, which is used to filter out irrelevant data from the Source Column so that only values that fit your filter criteria will be possible as choices.
- The Parent Column, located in the target list (the list where you want to add looked up information).
Once you have set up your filter columns, Cascaded Lookup will automatically filter according to your parameters:
- You select a value from the Parent column.
- SharePoint takes this value and compares it to the values located in the Filter Column (in the Source List).
- SharePoint instantly builds a list of possible choices by listing the Source Column values for rows with Filter Columns that match the value of the Parent Column. These results are then listed in the drop-down menu – all non-matching values have been filtered out, so they do not appear as possible choices.
For each item you create, Cascaded Lookup will apply the filter criteria you specified when you set up the column:
SharePoint Cascaded Lookup is an invaluable lookup tool, saving you time by reducing the options presented in the drop-down list, and eliminating the possibility for error as a result of incompatible values.
Cascaded Lookup is a powerful column type which greatly enhances the functionality of the default SharePoint Lookup column. In particular, it adds filter options to the lookup columns, and allows cross-referencing to other sites in the site collection. Besides its many powerful options Cascaded Lookup is the most responsive lookup column among all competitors.
We want to create a Cascaded Lookup column in the HR Management list.
To use SharePoint Cascaded Lookup, please create a SharePointBoost Cascaded Lookup Column in any SharePoint list or library.
In the Additional Settings section for the column, you can set up the column according to your requirements. To begin, specify any site in the site collection you want to connect this Cascaded Lookup column to. A major advantage of Cascaded Lookup is that the user does not have to enter any URL - the dropdown menu helps the user to identify the correct site.
After selecting the site, you have to specify the list you want to look up from. Please note that these two options will be locked, and thus cannot be changed anymore after the initial setup. Then, you have to select the column you want to look up from ("In this column" field). This set up is more flexible and can be changed after the initial set up, but the user can only choose single line columns and some SharePoint automatic columns for this field.
In our example, it might take a long time to populate the "Documents" column with items from the list or to change items, as you have to look up the related documents and select the correct one out of the long list (see the screenshot below).
Cascaded Look remedies this shortcoming; it allows for filtering results by specifying a filter column and a parent column. This way, you can look up the correct items much faster and reduce the possibility of making mistakes. There are a few requirements for the parent and filter column.
The available column types for the filter column are:
- Single line of text
- Any type of Choice column
- Any type of lookup column (SharePoint default, as well as SharePointBoost Cascaded Lookup and Cross-Site Lookup)
The available column types for the parent column are:
- Any type of Choice column
- Any type of lookup column (SharePoint default, as well as SharePointBoost Cascaded Lookup and Cross-Site Lookup)
Please note, neither parent nor filter columns support multiple values in lookup or choice columns. Also, the contents of the parent and the filter column have to overlap; otherwise, the filtering will not lead to any results.
Cascaded Lookup gives the user the option to display multiple column values in the SharePoint View Item window. To enable this option, add the columns you want to have displayed in the Show other columns section. By moving the columns up and down, you can specify the order of the column values in the View Item window.
If you only specify a filter column and no parent column, Cascaded Lookup will display a Filter field when you add a new item. If you select no filter item there, you can choose from among all items.
Once you set the filter, only filtered results will be displayed in the dropdown menu. So for the HR Management in this example, only the reports are being displayed in the dropdown menu.
By specifying a parent column, the options you can choose from will automatically be filtered so that the options match both the parent column and the filter column contents. Please note, that the parent column must be a choice column, or any kind of lookup column. In order for the filter to work, you must name the contents of parent and filter column identically.
Once a new item is created, the options to choose from are limited to the options that match the column contents. In this example only contents for the General Manager Department are displayed.
You can also choose to allow multiple values. To do so, please check the Allow multiple values checkbox.
When editing or adding an item, the interface of the Cascaded Lookup column will change to allow multiple selections.
Please note: Data will be lost if you later choose to disable this option. If multiple values are present a notification will appear that will inform the user that only the first value will remain in the list.
You can also edit items in the Datasheet View with Cascaded Lookup. This makes working with the Cascaded Lookup column much faster and more convenient, as you do not have to change the column type into a SharePoint default lookup column to edit items in Datasheet View. Please note, that currently this functionality is not supported if the lookup field allows multiple values. Also, due to limitations of SharePoint, the filter does not work in Datasheet View, so the user has all options to choose from in the dropdown menu.
You also have the ability to choose whether you want to display all or no items in case the look-up criteria specified yield no results. By default, the Cascaded Lookup column will display all items when no items meet the filter criteria. By checking the Show empty field when no items match filter criteria checkbox in the Advanced filter options section, no items will be displayed.
If this option is enabled, the dropdown menu or selection box will be empty and no items will be displayed.
If this option is disabled and no items meet the filter criteria, a notification will appear under the dropdown menu or selection box stating that all items are displayed because no item meets the filter criteria.
To convert a SharePoint default lookup column or a SharePointBoost Cross-Site Lookup column into a Cascaded Lookup column, please create a Cascaded Lookup column, as described above. Then, please click the Click here to convert an existing SharePoint Lookup column in this list to a Cascaded Lookup Column link.
In the following window, select which lookup column should be converted into a Cascaded Lookup column.
After clicking OK, a new window opens with a Cascaded Lookup column settings site displayed. Now you can edit it as described above. Please note that you must select which list to look up from and that these settings cannot be changed after conversion.
If you want to convert a Cascaded Lookup column into a SharePoint Lookup column, please check the Convert to original SharePoint Lookup column (stop using "Cascaded Lookup" features) checkbox.
After checking this checkbox, a notification will appear informing you that certain information might be lost if the Cascaded Lookup column is converted into another column type.
You have to confirm by clicking OK in the Change Column window. Then the Customize List window will open, and the column will be displayed as a default SharePoint Lookup column.
However, if you want to reconvert this SharePoint Lookup column into a Cascaded Lookup column, SharePointBoost has an integrated memory function so that all previous settings will be in place again. That means that the Cascaded Lookup column will automatically look up from the right site and list without requiring you to recreate your previous suite of settings.
Top
