Introduction to Lookup Columns
One of the problems faced when implementing a good collaboration Team Site is that you need to reference the same material in multiple locations within the Team Site. It is not always possible and it is also not best practice to put all the information you require in a single SharePoint app such as a list. There are however, times when you need to reference fields from another list in a Team Site. SharePoint supports this ability natively via Lookup columns that you can insert into an app as unique columns.
Although SharePoint should not be considered as a true relational database, good design encourages the minimisation of duplicated information by separating out information into unique apps (like lists) so that it can be used in many different ways. A good example of this is a contact list that contains names, email addresses, employers, etc. That contact list may need to be referenced in an “items purchased” list as well as perhaps a “phone log” list. It doesn’t make sense to have multiple contact items, it makes more sense to have a single point of truth that once updated is valid throughout the Team Site.
In this case the lookup will be performed on an existing contact list as shown above called Members. This is why designing a good structure with Team Sites is so important, because you need the look up item needs to exist prior to using it elsewhere.
In the list in which you wish to perform the lookup from (here a list called Locations) go into the List Settings and then locate the Column area towards the middle of the page as shown above.
Select the option Create Column at the bottom of this section.
You will need to give the new column a name. Then you need select the option below Lookup (information already on this site). Scroll down the page when you have made these changes.
You can then add a description and select whether the column must contain information and have unique values. Below these options you will now be able to select where the lookup information for those column comes from.
If you select the Get information from: field you will see a list of existing Team Site apps that you can select from. In this case the Members list will be selected.
Once you have selected where to get the lookup information from the In this column: field will change to be a list of all the columns in that app as shown above (here all the columns from the Members list). Now simply select the field you wish to display from the list that is being referenced. In this case the selection will be Full Name to avoid confusion.
With these two options now selected you will see that you can also display additional columns from the list that is being looked up. This is handy because you may need to display more than a single field from the list being looked up. In the case with the members list, perhaps you need not only their full name but also their email and mobile number.
In this case the additional fields Email Address and Business Phone have been selected.
At the bottom of the list of fields that can be added from the lookup location is a selection option that also allow these fields to be automatically added to the default List View. Normally, you will leave this selected, however fields displayed by Views can easily be customised at any point in the future.
The last option that can be set is how the relationship between the two lists will be handled. Basically here you determine what happens to this list when an item in the list being looked up is deleted. If you select the option to Enforce relationship behavior and an item is deleted in the list being looked up then any items in the current list that refer to that now deleted lookuped up item will also be deleted from this list.
You want to consider this cascading delete option carefully before you enable it because it can result in unexpected data removal. Best practice is generally to always retain the data, even if it no longer refers to something in the list being looked up. Best practice is therefore to leave the Enforce relationship beahvior unchecked.
Scroll down to the bottom of the page and select the OK button to save the changes.
If you now view the list you will see the additional columns just added. Here the Person column is a lookup to the Full Name field in the existing list Members and the fields Person:Email Address and Person:Business Phone are also taken from this location.
When you add a new record to this list you will only see two options (as the other fields are referenced once the lookup is performed).
If you select the Person field (which is a lookup to the Members list) you should see a list of all the rows from that location as shown above.
If you now complete a new record and save it you’ll be returned to the list summary as shown above. Because the lookup field was selected the associated values for that entry (here from the record for member Robert Crane) will automatically be populated in the email and phone fields as shown above.
You will also see that lookup field (here Person) also becomes a hyperlink that you can select.
When you do that, a dialog window will open and display that record from the list being looked up (here Robert Crane’s record). You can now edit and make changes to this looked up item quickly and easily if needed.
It is important to remember that the lookup functionality between SharePoint lists is not a true relational database. This means that there may be times when you update one field and a dependant location doesn’t appear to be updated. In this case, the information has indeed been updated but the display simply hasn’t. By viewing or editing that item you should find that the dependent information that was changed is automatically updated and displayed.
Lookup fields are an important part of designing good Team Site structures to ensure that information is not replicated in multiple locations. This however means that more time needs to be devoted to planning these linkages up front, however the payoff can be a significant reduction in overhead and complexity.
CIAOPS 