January 21, 2012

Lookup columns in SharePoint

In this article, we are going to discuss a simple and useful feature of SharePoint which is called lookup columns.

What are Lookup columns in SharePoint?
Ø  In simple words, it is a referential integrity between the lists in SharePoint (It is something like relating the two columns in a table in the database using queries).
Ø  We will create lookup columns, when we need to create relationship between two lists.
Ø  To create a relationship between two lists, in the source list, you create a lookup column that retrieves one or more values from a target list if those values match the value in the lookup column in the source list.

Advantages of Lookup columns
Ø  Easily create relationships between the lists without using calculated fields
Ø  Creates multiple relations between the lists

Lookup columns in WSS 3.0 and MOSS 2007
We have two custom lists created named Asset Details and Operating Systems
In Operating Systems list I have the following columns
ID                                   - Default column
Title                                - Single Line of Text
Release Version               - Number

The Operating System list is shown below

In Asset Details I have the following columns
Machine Name                - Number
Location                          - Single Line of Text
Operating System           - Lookup column
The below is the screenshot which shows how the lookup column is created for the Operating System column in the asset details list
Now, I will add a new item to the Asset details list
Hence, here for the operating system column, you will get the lookup values from the operating systems list as shown above.
Once created, when you click on the Windows 7 in the list, you will be navigated to the windows which shows the properties of the Windows 7 as shown below
Now I have added 3 values in the asset details list as shown below
Now, I will delete the Windows Vista data from the Operating Systems List as shown below
Now, we have deleted the Windows Vista item from this list, which is integrated or mapped in the Asset details list. Either, it should not allow us to delete or should show some warning about it. But it is deleted.
There’s no referential integrity between the two.
When we go and see the asset details list, the value is left empty as shown below
This disadvantage has been resolved in the SharePoint 2010. Read this article to know about lookup columns in SP 2010.


Post a Comment

Dear Readers,

I LOVE to hear from you! Your feedback is always appreciated. I will try to reply to your query as soon as possible.

1. Make sure to click the "Notify me" check box at the right side to be notified of follow up comments and replies.
2. Please Do Not Spam - Spam comments will be deleted immediately upon review.