How to Establish Dependent Lookup Criteria

How to Establish Dependent Lookup Criteria

Geographical information is one example where a dependent field is immediately useful. In this example, assume you have the following:

  • A list of your company’s various offices that contains a “State/Province” column, and a “City” column. It can have other data too, as long as it contains these two columns somewhere in the list.
  • Assuming you want to keep track of the state and specific branch office in that state where the sale occurred, you have another list that keeps track of sales (called “Sales Record”).

Using just standard SharePoint column types, you’d probably create two lookup fields on the Sales Record list, and point them to the columns on the list that contained your geographic information. Users would have to scroll through a list of states, make a selection, and then scroll through a list of cities posing two immediate problems with this setup:

  • First of all, users will be forced to spend a lot of time scrolling through a large list that contains mostly irrelevant information, because the columns do not communicate with each other. Even if a user enters “New York” under “State”, he or she will still have to scroll through all of the other options to choose “City”.
  • The second problem is the potential for human error. Since the list of cities is unfiltered, there’s nothing to keep a user from choosing a city value that is incompatible with the state value (something like “New York, Florida”). Later on, when the data is reviewed, there’s no way to know which value was incorrectly entered, so all of the data is essentially independent.

Here’s how to alleviate both of these issues by using a Lookup Selector Column’s dependency features to control the options presented to the user.

Step Action Result
1. Open the new list, called Sales Record. Assume there are two standard columns, “Title” (in this case, the name of the customer), and “Revenue” (a numeric field that contains the value of the sale).
2. Create the new list’s “State/Province” column by choosing Create Column from the Settings menu, and choosing a Lookup Selector Column type. hw41ab001.jpg
3. Under Source Site URL, enter the URL of the SharePoint site that contains the list of city and state/province data. Lookup Selector Column lets you to draw data from a list on a separate site (as long as it’s in the current site collection), something you can’t do with standard SharePoint lookup columns.
4. Once you’ve entered the location of the source site, click the green arrow to load the site’s lists and update the Source List drop-down menu. Choose the list that contains the city and state/province data. In this example, the list is titled MapDataList, as pictured above.
5.

Choose the specific column of the list to draw values from. In this example, the column is simply titled State/Province. Remember, this is the column on the source list. Click OK.
hw41ab003.jpg
You just created your state Lookup Selector Column. The result will be a drop down menu in the new list titled “State/Province” populated with choices from the source list’s Display Column (also titled “State/Province”). Notice the differences between this new column and a standard lookup.

The first thing you’ll notice about the Lookup Selector Column is the absence of duplicate choices. Lookup Selector Column filters out this data automatically, which makes navigating through potentially long lists of values much faster. The second big difference is that the Lookup Selector Column quickly and continually removes choices from the drop down as text is entered into the field. In the example pictured above, just typing “S” immediately removes every possible choice that doesn’t begin with “S”. Again, with large datasets, this is much faster than scrolling through the entire alphabet, including duplicates. Now, we’ll use the data entered into the State/Province column to make an even more restricted dependent column, called “City”.

6. Create another new column, again setting the column type as Lookup Selector Column. Since we’re pulling our geographic data (city and state/province) from the same source list, configure the Source Site URL and Source List values for this new “City” column just as you did for the “State/Province”.
7. Set the Display Column to the source column you wish to draw the new column’s choices from. In this case, set it to “City”, since that’s what the user will select from the new column.
8. Now, it’s time to assign a relationship to the column, so that only cities associated with the selected state show up in the “City” drop-down. Select the Make this a dependent column checkbox, to bring up several additional fields. hw41ab004.jpg
9. The first drop-down determines which column on the current list (Sales Record) causes – or “triggers” – the new column’s available choices to change. In this case, the “City” field changes as the user selects a “State/Province”. So set the first column to “State/Province”.
10. The second drop down determines which source column is used to filter the available choices. Again, in this example, we want to filter out the possible list of cities by the values in their corresponding state/province fields. So set the second column to “State/Province” as well, and click OK.
11. The column is configured. Your users now see an additional drop-down (called “City”) that changes based on the contents in “State”. hw41ab005.jpg
12.

Two Lookup Selector Column, one of which is dependent on the other

Notice how this setup, now that it reduces the two biggest problems of using standard lookup columns. Users entering data are now much less likely to enter incorrect city data (since only relevant cities are displayed), and no longer need to waste time scrolling through long lists of values.

Highlights of Lookup Selector

Highlights of Lookup Selector

LookupSelector.png

How does it work?

The Lookup Selector Columns is a more powerful version of the standard lookup column included in SharePoint out-of-the-box. They both work by giving users a drop-down list of choices that are pulled from a separate SharePoint list, allowing you to leverage existing data and restrict user-input to a certain set of pre-existing values. However, the Lookup Selector Column allows you to enter the URL of the site where the separate SharePoint list resides. The SharePoint Lookup column can select from lists within the current site only. When you choose the list and the specific column to display in the Lookup Selector Column, only the distinct values of that column will be shown. No duplicate values will confuse the user.

Additionally, the Lookup Selector Column allows you to take lookups a step further, by optionally using a value in another column to determine the choices presented in the lookup. This is called a dependent lookup. To set one up, you need to establish a few things:

  • Current List – the list where you are configuring a dependent Lookup Selector Column;
  • Source List – the list where the lookup values reside;
  • Display Column – the column to be displayed from the Source List;
  • Trigger Column – a column located in the Current List that will be used to filter the values to be displayed in the dependent lookup column.
  • Filter Column, a column in the Source List which is used to filter out irrelevant data from the full Display Column. The values displayed in a dependent Lookup Selector Column will be from those items in the Source list where the value of the Trigger Column equals the value of the Filter Column.

Features

  • Unlike standard SharePoint lookup columns, can reference lists located outside of the current site.
  • Hide duplicate entries from a lookup-style field’s drop-down selection. Users only see distinct values.
  • Allow list owners to create dependent relationships between two or more columns, so that the value of a parent column dictates what choices are available in the child column (which, optionally, could then dictate the values in subsequent child column).
  • Find-as-you-type functionality – simply type the first few letters of a desired value to quickly select it.
  • Allows for multiple or single selections.
  • Automatically updates lookup values as the source list data is changed.

Configure the Bamboo Lookup Selector

Configure the Bamboo Lookup Selector

Bamboo Lookup Selectors are added to a list just like the standard column types included with SharePoint.

To add a Bamboo Lookup Selector column to a SharePoint List by doing the following:

Step Action Result
1.

Add the column to a list

  • From the list, click List located in the List Tools tab.
  • From the List toolbar, click Create Column located in the Manage Views section.
2. Name the column.
3. From the Create Column screen that appears, select the Bamboo Lookup Selector option. HW41AB010.jpg
4. Additional Column Settings Enter the URL of the site that contains the list to be referenced by the new column in the Source Site URL field.
5. Click the green arrow to access the site, and update the Source List drop-down.
6. Select a list from the Source List drop-down menu.
7. From the Display Column drop-down menu, select a column from the Source List. The Lookup Selector column will populate the new drop-down with these values. HW41AB011.jpg
8. To enable users to make more than one selection, select the Allow multiple values option.
9. To create a dependency between this column and another one, select the Make this a dependent column checkbox. Once the checkbox is selected, new options will display. HW41AB012.jpg
10. Which column in this List triggers a change in the Display Column Select a column from the current list; the value entered into this field by the user will determine what values appear in the Lookup Selector itself.
11. Which column in the Source List determines the values to filter on Select a column from the Source List; this value is used to determine which values from the source list corresponds with the user-entered value in the current list.
12. Click OK. The column is now added to your list.

Complementary Products for Lookup Selector

Complementary Products for Lookup Selector

sharepointsolutions.pngYou will find many of our products complement each other quite nicely saving you the time and trouble to write custom code. With Bamboo at the heart of your SharePoint investment, you gain access to a huge catalog of enhancements, components, and accessories that add the critical functionality your business requires.

The same components can be easily used in future applications and they all come from a single, trusted vendor, ensuring an easy purchase process and support you can count on. That’s the Bamboo Way!