Tag: How to Lookup Selector Column

Lookup Selector Column also give you the ability to allow multiple selections to be made within a single Lookup Selector. This allows you to flexibly automate multi-step data entry processes by simply setting up a series of dependent Lookup Selectors.

Let’s look at an example that takes advantage of this functionality. The topic How to Establish Dependent Lookup Criteria demonstrated the ability to regulate the entry of City information based on previously entered State information, by restricting the available choices in the “City” field to those associated with the selected “State”. That example is based on the following column structure:

Now, let’s take this a step further, by adding a third field and allowing users to make multiple selections in each field. The third field will be “Zip Code”, and should only display Zip Codes for any of the selected cities. If a city is removed from the list, all zip codes related to that city should be removed. If a state is removed from the list, all cities in that state should be removed, as well as all zip codes related to any cities.

Here’s our new column structure.

Remember, we’re also adding the ability for users to make multiple selections from any of these lists (provided multiple choices are available at the time). To do that, we need to select the Allow multiple values option when setting up each column.

Other than that, the column setup process is exactly the same as it was in the topic How to Establish Dependent Lookup Criteria; the only difference is that we’re adding an additional column – Zip Code – that is linked to the results of “City”. Once the columns are set up, the New Item screen will look something like what is pictured below. Notice the additional controls for making multiple selections; instead of selecting from a drop down, users add and remove their choice(s) to or from a list.

With this column structure, adding a selection to one list immediately changes the available choices in the next one. When we add “Australia” and “Canada” to the country list, the city list becomes populated with all of the cities associated with these two countries. Then, when we select a group of cities, all zip codes associated with those cities become available from the Zip Code list.

When a selection is removed, all fields associated with it are removed from subsequent lists. In this example, removing a country (the most basic unit in our column structure) from the list would remove all cities associated with the country, and in turn, all zip codes associated with it as well.

As this example demonstrates, with multi-select and nested Lookup Selector Column, you can regulate even complex data entry, making it as simple as possible for end users and greatly reducing the use of inaccurate or inconsistent metadata.

Scenario

You have a single list called States and Cities that you use for the source list of two Bamboo Lookup Selector columns on your main list. One Bamboo Selector looks up to a State column and the other looks up to a City column. The City lookup is dependent so only the cities in the selected state are shown.

The Issue

There are some states that have only one city in the list. When users select this state, they want the city to be selected automatically in order to save time for users. How to do that?

How To

The list with the Bamboo Lookup Selector columns is called Opportunities. The configuration of the State column in the Opportunities list is shown below.

The configuration of the City column is shown below. The important thing to note is that the City column is configured as required column.

The configuration of a lookup as a required column (i.e., one that must have a value), is the key part of this scenario. When the City column is NOT required, the new item form looks like this; the City selection defaults to (None), even though CA has been selected as the State.

If the City column requires a value, once the State is selected, the first available value will be displayed.

IMPORTANT: If the Lookup list has more than 20 items, the lookup column will NOT have a default. This is the same behavior as SharePoint Lookup columns. When they are required, the value defaults to the first available lookup value. However, if the lookup list has more than 20 items, no default value is displayed. A value must be selected.

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.
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. 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.
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”.
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.

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.
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.
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.
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.