SharePoint 2010 What’s New – Lookup Columns !

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!