Knowledge Base

How to filter lookup column based on another column value?

SharePoint lookup column enables you to get information from another list or library. But there is no way to filter the data retrieved to show only limited set of entries based on value in another field.

BoostSolutions’ Cascaded Lookup enables you to create a lookup column that depends on the value of other list columns to narrow down your choice.

In this article we will demonstrate how to create dependent relationships between Continent, Country and City columns (Continent -> Country -> City) to show how it’s done.

WHAT YOU'LL GET

The options for Country column is defined by what you chose for Continent column, and the options for City column is defined by what you chose for Country column as well.

WHAT YOU'LL NEED

The sample data we use for this case

Then, create another list named as Customer from the custom list template with following columns and items on the same site collection.

Filter lookup column based on another column value

  1. Download the trial version of Cascaded Lookup, and install it to your SharePoint server.

  2. Navigate to the Customer list prepared above, click List tab. In the Manage Views group, click Create Column.

  3. In the Name and Type section, enter a name in the Column Name field to give it a meaningful name such as Continent, then select Cascaded Lookup as the type of information in this column.

  4. In the Additional Column Settings section, keep all settings as default.

  5. In the General Settings section, configure settings as following.

    In the Get information from this site box, select Marketing Center, because in our example the Country list is located in a SharePoint site named as Marketing Center. You need to select the corresponding SharePoint site in which your Country list is located.

    In the From this list box, select Country.

    In the In this column box, select Continent.

  6. Keep other settings as default and click OK.

  7. Then, you will find that a Cascaded Lookup column named as Continent has been added to Customer list as following.

  8. Let’s create the second Cascaded Lookup column and name it as Country. And in the General Settings section, configure settings as following.

    In the Get information from this sit box, select Marketing Center.

    In the From this list box, select Country.

    In the In this column box, select Country.

  9. In the Cascading section, select the checkbox Make this a dependent column.

    In the Parent column in this list box, select Continent.

    In the Filter by column in Target list box, select Continent.

    Make this a dependent column means that the current column will be depend on another column. In this case, it means that the “Country” column we are creating will be depend on the “Continent” column. In our example here, it means that if you select Europe for the “Continent” column then you will only see German and United Kingdom in the “Country” column.

    Note:

    Target List - list where Cascaded Lookup will refer to for information. The Target List can be in any site in the current site collection.

    Target Column - located in the Target List, contains data for your drop-down menu.

    Filter Column - located in the Target List, it is used to filter out irrelevant data from the Target Column and is also available as a drop-down menu.

    Parent Column - located in the current list (the list where you want to add look up information).

  10. Keep other settings as default and click OK.

  11. And then, you will find that another Cascaded Lookup column named as Country has been added to Customer list as following.

  12. Now, let’s create the third Cascaded Lookup column named as City. And in the General Settings section, configure settings as following.

    In the Get information from this site box, select Marketing Center.

    In the From this list box, select Country.

    In the In this column box, select City.

  13. In the Cascading section, select the checkbox Make this a dependent column.

    In the Parent column in this list box, select Country.

    In the Filter by column in Target list box, select Country.

    Make this a dependent column means that the current column will be depend on another column. In this case, it means that the “City” column we are creating will be depend on the “Country” column. In our example here, it means that if you select North America for the “Country” column then you will only see San Jose and New York in the “City” column.

  14. Keep other settings as default and click OK.

  15. Then, you will find that a new Cascaded Lookup column named as City has been added to Customer list as following.

  16. Select item Diff Inc and click Edit Item.

  17. In Continent column, select South Asia. And then the Country (India) and City (Pune) will be populated as following.

Login