SharePoint 2010 What’s New – Lookup Columns #SPC09

An enhancement that’s been long waiting for SharePoint is around lookup columns. In 2007 you can create a lookup column which essentially “looks up” values from another list. Here’s creating a lookup column in 2007:

Basic stuff. You select the list you want to get information from and the column you want to use as a display value. You can allow multiple values which makes for an interesting setup. Imagine you want to keep an inventory of servers in SharePoint. You might create a list of servers then you want to know what operating systems are installed. So you could create a list called “Operating Systems” and then in your Server list create a lookup column called “OS” that gets information from “Operating Systems” using the column “Title”.

A few problems with lookup columns you want to be aware of. If you delete say “Windows XP” from your “Operating Systems” list, any reference in the Servers list are going to be blank. Also there’s no restriction on deleting your lookup values if they’re in use. There’s no referential integrity between the two.

Another simplistic thing in 2007 is that you only get one column to pick from. This might be fine but what if you want to display more information in the lookup (for example Operating System + Version). You can create a calculated field in the original list which can be something the user picks but a) it requires the creation of that calculated field b) If you have a lot of fields to display the dropdown can get quite wide and c) what if you just want to display additional information for the lookup but not include it in the picklist?

In 2010 Lookup columns have been greatly enhanced and provide these features out of the box. Here’s what creating a lookup column looks like now:

You’ll notice two major enhancements here (and a third I’ll get to later). First is the additional column to show. This is a list of all the columns in the original list. These are not added to the dropdown that the user will select, they’re added to the view when you display the list item. This is the same behavior as you would see in a BDC column in 2007. These additional columns can also be filtered and sorted on giving you some additional capabilities in your views.

The second major enhancement is the Relationships section at the bottom. This is huge and provides you the ability to specify the relationship between items in your list and the lookups they use. It’s an optional choice on the lookup and you can choose between cascaded deletes or restricted deletes.

Cascaded delete will delete delete any related items in the list you’re defining the lookup on when an item in the target list is deleted. So delete “Windows XP” in your lookup table, any records that reference it are deleted in your Servers list.

Restricted delete means if you try to delete the “Windows XP” lookup value you’ll be prevented from doing so if your Servers list contains items that use it as a reference.

Very cool indeed!

Finally one last thing. You might have noticed there was an option to allow duplicate values or not.

This is actually a property of the base ListItem and included on *all* column types. So anything (text, number, lookups, etc.) can be set to unique or not.

Enjoy learning the new features of SharePoint 2010!


  • Still missing Order by and filtering option :-)

  • But still no cross-site lookups???? I have this as a hack in 2007 but was hoping to see it native in 2010.

  • Plug - cross-site, cross-site collection, cross-web app, cross-server farm lookups standard with CorasWorks Data Integration Toolset on sp2007 and sp2010

  • Does the "additional column to display" functionality always display all the columns from original list? In my tests it only seems to display 2 custom columns (along with the default ones)..

  • "This is a list of all the columns in the original list".

    Not at all! Only single text, numeric and some other. So, in most of the cases this "new feature" is useless.

    And again, no cross-site list.

    All in all, a real pity

  • I agree with frankie. A common type of example is I have a list of Vendors that are in Title. I have a column I created which is the person who is responsible to track the vendor which is a People Picker. In one list I want to lookup the vendor and have the Person come over as well but that field type is excluded. The fields that are available have limited functionality so I haven't even used this feature. SP 2010 has been not been living up to all of the sales promises.

  • I want this additional column info to be displayed in ma work.
    but i use only sharepoint 2007. is it possible to get this to be done in 2007?????

Comments have been disabled for this content.