Continuing our series on the complete line of Bamboo Custom Column products available in the Bamboo Store, we’ll now introduce the Bamboo Lookup Selector Column. This product extends the capabilities of the SharePoint Lookup column, offering the capability to perform lookups across sites, and provide dependency between columns. So let’s jump right in and discuss the options this custom column provides.
To begin, first we need to create a new Bamboo Lookup Selector column. To create a new column, select the action Create Column located under the Lists Ribbon:
You can also reach this option going to List Settings in the same Ribbon, and then Add a Column under the Columns section.
In the Create Column dialog, provide a name for the column and then select Bamboo Lookup Selector from the list of column types. Notice that the Additional Column Settings section updates to display a set of options related to the column type Bamboo Lookup Selector:
Now we need to define the source data for the lookup values. Remember, one of the benefits of the Lookup Selector Column is its ability to connect to lists across sites in the same site collection. First, we need to enter the URL of the site where the source list resides. You can enter the URL in one of two ways: Absolute site URL or Relative (./, ~/, /) URL. Bamboo Solutions recommends using a relative URL as it will provide you the means for creating List and Site Templates, and also provides the capability to move the Farm from one host URL to another.
After entering the appropriate source site URL, click the green arrow to populate the source List drop-down menu:
Select the source List you want to associate with the Bamboo Lookup Selector column, and the Display Column drop-down menu will populate accordingly:
Select OK to save your settings. Now when you view the New Item or Edit Item forms, you will see a drop-down menu similar to the standard menu of SharePoint out-of-the-box Lookup columns for a single selection:
Similar to SharePoint out-of-the-box Lookups, if a selected value in the Lookup list is modified, the new value is reflected in our Bamboo Lookup Selector column.
The difference between the SharePoint out-of-the-box Lookup and Bamboo Lookup Selector for single selection is that in our Custom Column, if there are more than 100 values in the lookup list, the drop-down becomes a “Find as you type” control, meaning the user enters a few characters and the drop-down automatically filters to display a list of possible matches, making it easier to navigate through the many possibilities.
Jumping back into the Lookup Selector column we just created and modifying the settings, we’ll now select the option to Allow Multiple values:
Now the control changes from a drop-down menu to a list box:
Now let’s talk about the best feature of all: column dependency. So what do we mean column dependency? Glad you asked! Let’s say you have a form where the user must select a city and state. To save time and maintain data integrity, you may want the users to select the options from a drop-down instead of using text entry. To save the user time, you can configure Bamboo Lookup Selector so that when users select the city, the State column is automatically filtered to display only states that contain the selected city. Pretty cool, huh?
To enable this feature, we’ll go into the column settings and select the option Make this a dependent column:
At this point, you’ll notice three new options appear. The first setting, Which column in this List triggers a change in the Display Column?, defines which column in your list you want the Bamboo Lookup Selector column to filter on. You can select from Text, Number, Choice, and Bamboo Lookup Selector columns:
Next, we need to associate the column we just selected with a column in the Lookup list that is mapped to the Bamboo Lookup Selector. Again, you can select from Text, Number, Choice, and Bamboo Lookup Selector columns:
Save these settings.
Before we jump into actually playing with this feature, let’s talk about what is in my Lookup List:
Notice that I have two items where the column Title is “ABC.” For my lookup column, in this example, Text has two different values: abcd and 123.
OK, back to our original list where Bamboo Lookup Selector resides. I have configured my Bamboo Lookup Selector column to be dependent on the column TextSP. In the TextSP column, I enter the value ABC and when I click to another column field, you’ll notice that the Bamboo Lookup Selector column updates to display only two values, not four.
The last setting, Enable Cascade Dependency, is extremely useful if you have multiple Bamboo Lookup Selector columns in your list. For example, if you have City, State, and Zip Code where you want each to filter based on the previous selection, i.e., you select City, the State column filters to display only states that contain the selected city, and then Zip Code filters based on the selected State. So to begin, we will create a new Bamboo Lookup Selector column. NOTE: All Bamboo Lookup Selector columns must map to the same lookup list for this option to be available.
After I create my three Bamboo Lookup Selector columns, on my third one, I enable the cascade dependency:
Now when I create a new item, I see the following form:
Notice that Bamboo Selector2 is currently empty. That’s because Bamboo Selector1 has nothing selected. Upon selecting a value for BambooSelector1, the drop-down menu for BambooSelector2 will be updated with available options:
If I do not select any value for the Bamboo Selector2 column, the final column in the cascade dependency, BambooSelector3, remains empty:
But once I select a value, BamboSelector3 is now populated based on the previous column selections with only 1 value available due to the combination of the previous columns:
Read our entire series dedicated to Bamboo Custom Columns:
- Extending SharePoint Lists Without InfoPath: Introducing a Series on Bamboo Custom Columns
- Part 1: Bamboo Rich Text Column
- Part 2: Bamboo Column Level Security
- Part 3: Bamboo Lookup Selector Column
- Part 4: Bamboo Visual Indicator
- Part 5: Bamboo KPI Column
- Part 6: Bamboo Rating Column
- Part 7: Bamboo Validator Column
- Part 8: Bamboo Custom Identifier