Jan 23, 2012

Lookup columns in SharePoint 2010

In the previous article, we have learned about basics of lookup columns and the disadvantages in lower versions of SharePoint.
In this article, we will discuss the capabilities of lookup columns in SP 2010.
Types
There are two types of lookup columns:
1. Lookup columns with an enforced relation
2. Lookup columns with an unenforced relation
Lookup columns with an enforced relation
In a lookup column with an enforced relationship (also called referential integrity), you can lookup single values and maintain the integrity of your data in the target list in one of two ways:
Cascade Delete When an item in the target list is deleted; the related item or items in the source list are also deleted. In this case, you want to make sure that all related items are deleted as part of one database transaction.
Restrict Delete When you try to delete an item in the target list, and it has one or more related items in the source list, you are prevented from deleting the item in the target list. In this case, you want to ensure that the related item in the target list is preserved.
Lookup columns with an unenforced relation
In a lookup column with an unenforced relationship, from the source list, you can lookup single or multiple values in the target list. Also, when you delete an item in the source list, as long as you have delete permission on the list, there are no additional delete restrictions or delete operations that occur in the target list.
Enhancements in SP 2010
In SharePoint 2010, three major enhancements are added in the lookup columns:
1.   Additional column to show
2.   Cascade or restrict delete options
3.   To allow duplicate values or not options
As described in these article, I will create two custom lists such as Operating systems and Asset details as shown below
In the Asset details list, we will create a column which has lookup column from Operating Systems list as shown below

Note: I have created enforce relationship in this with restrict delete option as shown above.Now, we will add an item to asset details list
Now I have added some more items with different lookup fields from it.
Now, we will try to delete the Windows Vista item from the operating systems list which has lookup in the asset details list.
Now, as per the relationship this cannot be deleted and we will get an error which shows that asset details and operating systems list are related and it cannot be deleted as shown below.
Now, we will check with Cascade delete option
As per the relationship, when an item in the Operating System list is deleted, then the related item in the Asset Details will be deleted as shown below
I have deleted Windows Vista and hence, the corresponding items are deleted in the operating system list as shown below
When the relationship is not set, deleting an item in the source list will leave a blank space in the target list as shown below (this is the case same in WSS 3.0 and MOSS 2007)



Hope, this article helped in understanding the lookup column capabilities in SharePoint 2010 easily.
Now, there is another question arises, is it possible to create a cascading drop downs using lookup columns in SharePoint?
For answer, see this article.

7 comments:

  1. Hi,
    Nice Post. Very clear walk through about Lookup Columns.

    ReplyDelete
  2. How do I create a view of my "lookup" list that will show all the related items in the first list? IE: In your example above, can I create a view of Operating Systems that shows all the assets using it?

    ReplyDelete
  3. Does any of the settings work in reverse to take a maually added (fill-in) item in the target list and add it to the source file? I'm working to build a cascading dropdown where this could come in handy.

    To take your cascading dropdown as an example, let's say that Germany (Country) currently has 25 cities listed. My user doesn't find his city, so the new city is filled in. I would like that new city to be automatically added to the City List. For the next user that comes along, there are now 26 cities to choose from.

    TIA

    ReplyDelete
    Replies
    1. Hi,
      By Out of Box features, the solution you are expecting is not possible.

      Delete
    2. Take a look here:
      http://spservices.codeplex.com/wikipage?title=$().SPServices.SPLookupAddNew&referringTitle=Documentation

      Delete
  4. Is there an alternative to using the Lookup option so that when an item in the source data is deleted (a specific list item; say, a name) the value remains in the list item doing the lookup? Choice works but it is not an efficient option, and it can't be used by other tools like Nintex Workflow.

    ReplyDelete

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.