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.