Corey Roth [MVP]

This week, I needed to deploy lookup columns to some of my lists and as usual I wanted to avoid writing code at all costs.  As some of you may know, Kyle Kelin and I debate this topic often as he prefers a code approach.  I figured it had to be possible with CAML, but many claimed it was not even possible.  A few approaches showed up out there involving using code to modify the elements.xml file with your GUID, but that just wasn’t going to cut it for me.  One popular post on the topic by Josh Gaffey, started me in the right direction, but there were a few hurdles I ran into as I was trying to implement it.  It would create the list, show the content type, and site columns, but when I tried to create a new item, the lookup column was not there.  The basic technique is that you specify the path to the list in the form of (Lists/MyListName) in the List attribute of the Field element in both your schema.xml file of your document library template as well as the definition of the site column.  The first thing I learned here is that you cannot simply omit declaring site columns and a content type out and go with list level columns.  It simply will not work (no idea why).

Here is how I ended up getting everything to work.  I created my initial lists, content types, and site columns through the SharePoint UI.  I then used SPSource (seriously a great tool) to export the content type, site columns, and lists (both the source list and the one that contained the lookup column).  My goal was to deploy these items to another site collection.   I recommend creating separate features for your content types and the list definition.  This way you can ensure the content type is deployed first.  To export your content type / site columns, create a new stub feature.xml file as described on their site and then create an .spsource file with just the content type in it like this (no need to specify columns).

In my example, my content type was called Test.  Create a separate feature folder for your list template and instance.  Create another feature.xml and an .spsource file for the list which looks something like this.  My list is also named Test in this case.

SPSource does a good job when it exports my list definition and properly specifies the list using a path instead of a guid in my lookup column as shown below (from schema.xml).

We don’t actually have to change a thing in this file.  What I did have to change is the List attribute of the file it generated for my site columns.  As Josh’s post above stated, we have to change the List attribute from a GUID to the path of the list.  His post also mentioned you might want to set the PrependId attribute but I have since learned that this is only used when you are using the LookupMulti type (although I can’t remember the source I got this from).

At this point you can deploy your site columns, content type, and then custom list (in that order) and if all goes well you will be able to create a new item of that content type with the lookup column functioning.  One thing to note, if you get this wrong, your list will behave oddly.  One thing I noticed is that it won’t turn on content types for the list when that happens.  If this is happening go back and check your work.  Another thing people mentioned is that the source list for the lookup column has to exist before you create the lookup column.  This in fact is not true.  It will work and it will display a drop down list for the lookup column, but it will be empty of course since the list does not exist.

Along the way I figured out a few other things.  When I was trying to figure this out, I was trying to just create the content type and then manually add it to the list via the UI.  Doesn’t work.  If you try to add a content type with a lookup column defined in this way to an existing list, you will get the following error.

Exception from HRESULT: 0x80040E07 at Microsoft.SharePoint.Library.SPRequestInternalClass.AddField(String bstrUrl, String bstrListName, String bstrSchemaXml, Int32 grfAdd)
at Microsoft.SharePoint.Library.SPRequest.AddField(String bstrUrl, String bstrListName, String bstrSchemaXml, Int32 grfAdd)

The only way to get the content type associated with the list is via CAML when it is created.  Another odd thing is that the lookup column never shows it is bound to the other list correctly.  Here is what the source lookup column looked like.

Notice where it says Get information from, the name of the list is present.  Here is what my copy of the list looks like.

Notice that the list name is not present.  Strangely enough though, everything works fine in the copy.  This one through me for a loop for a while because it didn’t seem like things weren’t working and as I mentioned above I couldn’t add the content type to a list (nor could I add the column to a content type). 

As you can see there are few oddities about deploying lookup columns in this manner, but it does work.  @SPKyle informed me he could have written code to do this hours ago, but I am quite happy that I can deploy things in this manner now.

Twitter: @coreyroth