Setting up multiple cascading lookups in SharePoint forms

Introduction

Lookups are one of the most commonly use column type in SharePoint. Users across the globe take advantage of these columns on a daily basis.

A lookup column displays a dropdown selection of item[s] from a different SharePoint list. Usually you would use a list from the current site. But it is also possible to perform cross-site lookups, by using site columns.

Despite their common usage, regular SharePoint lookup columns are quite limited. In some cases, as with large lists of over 2000 list items, they are completely useless.

Enter Connected Lookup

Connected Lookup column is a component of Ultimate Forms. It expands the features of the regular lookup column and adds the following valuable additions:

• Parent/child filtering - for example, by selecting a state, you pick only from the cities of that state.
• Filter/sort by view - only allow to pick relevant items. For instance, filter out archived/discontinued items automatically.
• Add new items - add new items directly from the form, without having to switch to the lookup list and lose your data.
• Easy way of implementing cross-site lookups - no need to use site columns, simply select the site and then list.
• Auto-complete mode - users enter a few characters and only values containing these characters appear. This is a life saver when you have to work with large lists.

Connected Lookup works by filtering the dropdown items by one or more parent values. Parent values have to come from another column in the same list.

We support both lookup and choice columns as parents. Note the the lookup list itself must contain a similar lookup/choice column. Each items in the list has to have a value in that column.

When the user selects a parent value (or values), it triggers the Connected Lookup column. The column will then query the lookup list for all the items matching the selected parent[s]. We support both single and multiple relationship types.

We designed the column to have the easiest possible setup with the widest variety of features. As long as you remember to add the column in the lookup list, the settings will offer you that column automatically.

Multiple Levels

In this blog entry I'm going to show you how easy it is to set multiple levels of cascading lookups. In our example, we will set up list of sales. We will then assign each sales figure to country, state and city. Selecting a country filters the available states and selecting a state filters the available cities.

First, let's create our Countries, States and Cities lists. For all lists we are going to be using the versatile Custom list type.

Countries

Simply create a new custom list, no need to add any columns. The built-in Title column is all we need.

States

Create a new list, then add a lookup column pointing to Countries. You can use the built-in lookup column or our Connected Lookup for the additional functionality.

Consider this: you are adding a new state. But then you realize you forgot to enter the relevant country. Now you can do it directly, without having to switch lists. Make sure Filter according to parent lookup checkbox is unchecked, as this is a stand-alone lookup.

Cities

Create a regular custom list. Add regular lookup or Connected Lookup (without Filter according to parent lookup) pointing to Countries. Then add Connected Lookup pointing to States list. Leave Filter according to parent lookup checked, the field should pre-select Country as the parent field.

Sales

Create a regular SharePoint list. Add regular lookup or Connected Lookup (without Filter according to parent lookup) pointing to Countries.

Next add Connected Lookup pointing to States list. Leave Filter according to parent lookup checked, the field should pre-select Country as the parent field.

Next add Connected Lookup pointing to Cities list. Leave Filter according to parent lookup checked, switch the parent field to be State.

Finally, add a currency field for sales amount.

That's it, add some data to your lists. Now when you create a new sales entry, you can see the cascading functionality in action!

Need to perform lookups across site collection boundaries? Read this blog to learn how you can accomplish that.