SharePoint 2007 – associated lookup columns

No, this is not a mistake – we can also have associated lookup columns in SharePoint 2007 [update (Dec 13, 2010): check for two drawbacks in SharePoint 2007 below], though not with the extended UI creation capabilities available in SharePoint 2010. Let’s first have a look at this new functionality in SharePoint 2010 (and define more accurately the terminology – I don’t think that there is a specific term for this lookup field extension, but in this article I will call it as I did in the title of the posting):

So, as you can see from the screenshot (and probably tried that many times already yourself), when you create a new lookup column in a list, you have the option to select more than one column from the target lookup list (in SharePoint 2007 we had the option to select just one “show column”). Basically what SharePoint does when you create a lookup field from the UI is to create the “normal” lookup column as before and then create another read-only “associated” lookup column for every additional show field from the check-box list that you have selected. And this functions as follows – when you create a new item, or edit an existing one in the edit form you see only the “normal” lookup column there, but when you change it to point to another item in the lookup list, all “associated” lookup columns change their values so that they correspond to the related columns in the newly selected lookup item. The associated lookup columns are read-only but you can add them to the views of the list, so that you display all additional columns that you need from the lookup item. And since the main lookup and the auxiliary lookup fields are automatically synchronized (SharePoint does that for us), you don’t need any additional code in item event receivers for instance.

Let’s now have a look at the field schema of the “normal” and “associated” lookup columns:

<Field Type="Lookup" DisplayName="Department" Required="FALSE" EnforceUniqueValues="FALSE" List="{ceaf935e-b9c6-48a0-8c23-bcec58a24c91}" ShowField="Title" UnlimitedLengthInDocumentLibrary="FALSE" RelationshipDeleteBehavior="None" ID="{98948dfd-cea5-4d6c-ac47-25bafa5218de}" SourceID="{bca1cd44-3822-49b6-b68c-2ff28ced1726}" StaticName="Department" Name="Department" ColName="int1" RowOrdinal="0" Group="" />

This is the schema of the “normal” or main lookup column – as you see, there is nothing specific in its schema – it is just a regular lookup column. And this is the schema of one of the associated lookup columns that I created for the lookup column above:

<Field Type="Lookup" DisplayName="Department:Code" List="{ceaf935e-b9c6-48a0-8c23-bcec58a24c91}" WebId="4ee36ddf-5b1b-470b-9f9a-fbd970edf5aa" ShowField="Code" FieldRef="98948dfd-cea5-4d6c-ac47-25bafa5218de" ReadOnly="TRUE" UnlimitedLengthInDocumentLibrary="FALSE" ID="{cd2b77b9-6238-4e2e-99ee-826826dc09f2}" SourceID="{bca1cd44-3822-49b6-b68c-2ff28ced1726}" StaticName="Department_x003a_Code" Name="Department_x003a_Code" Version="1" />

So, as you see we have all attributes for a normal lookup column, but one extra attribute as well – “FieldRef”. The value of this attribute is a Guid and this is exactly the ID attribute of the main lookup column. So, obviously the “FieldRef” attribute is the one that defines this association between the main and the associated lookup columns. Notice also the “ReadOnly” attribite in the associated lookup field’s schema which is set to TRUE – this guarantees that the associated lookup column doesn’t appear in the new and edit forms of the SharePoint list (you don’t need it there anyway, since it is synchronized automatically).

This is indeed a very useful new functionality in SharePoint 2010, but you may already ask yourself what SharePoint 2007 has to do in the whole matter – we don’t have this functionality there. Well, it is true that SharePoint 2007 lacks the user interface for creating “associated” lookup columns but this doesn’t mean that we cannot create columns with code specifying the values for all attributes in their schema as we decide. And it turns out that when we create a lookup column in a list and set its “FieldRef” attribute to “point” to another lookup column in the same list (both columns should target one and the same lookup list) – the “associated” lookup functionality actually works – just like in SharePoint 2010 (note here – I tested this on SharePoint 2007 service pack 2). Another detail here is that you shouldn’t forget to set the “ReadOnly” attribute of the field to TRUE – otherwise the “associated” lookup column will appear in the edit form of the list and when you try to save the list item, the page will crush with a nasty error.

As to the question of how you can create a lookup column (an associated lookup column) with code (and set its FieldRef attribute) – probably the easiest way is to use the SPFieldCollection.AddFieldAsXml method, which accepts a string parameter containing the schema XML of the new field. Note that you will have to properly format the schema XML of the associated lookup field providing the correct values to its attributes (the final XML schema should look similar to the one above):

• in the ID attribute – a unique Guid value
• in the Name and StaticName attributes – unique (within the SharePoint list) internal name of the field
• in the List attribute – the ID of the lookup list
• in the WebId attribute – the ID of the web containing the lookup list (in most cases this is the web of the current list)
• in the FieldRef attribute – the ID of the main lookup field to which we want to associate the new lookup column
• in the ShowField attribute – the internal name of the column in the lookup list whose value should be displayed in the new lookup column
• in the ReadOnly attribute – this should be set to TRUE

And the net result will look something similar to this:

In the sample screenshot, the “Department” column is the main lookup column (displaying the “Title” column from the lookup list) and the “Department country” column is the associated lookup column (displaying the custom “Country” text column from the lookup list). Note also that the value displayed in the list view in the column of the associated lookup field is not rendered as a link, unlike the value in the column of the main lookup – actually this is exactly the same behavior as we have in SharePoint 2010. And most importantly again the same as in SharePoint 2010, the values of the associated lookup column or columns get automatically synchronized with the changes of the selected lookup item in the main lookup field.

[update Dec 13, 2010]

Shortly after the publishing of this article there was a comment (by Szymon) that once you create an associated lookup field you cannot delete it – something that I have overlooked. The error that you receive is “One or more field types are not installed properly. Go to the list settings page to delete these fields.” The reason for that is some kind of restriction in SharePoint 2007 (in SharePoint 2010 it works perfectly well) and I have seen the same error when I tried to delete “computed” (not to be confused with “calculated”) fields. After some research the one common thing between the associated lookup and the computed fields is that they both lack the “ColName” attribute, which means that there is no associated database column in the SharePoint “all user data” SQL table. Which also means that these types of columns don’t take space in the content database as one can expect. The bottom line is that you have a field that doesn’t take space in the SharePoint content database, but you still can’t get rid of its schema. As a work around you can set also its “Hidden” attribute so that the field doesn’t appear in the available view fields of the list in the standard SharePoint UI. Still, it is important that you know that once created you cannot delete (at least not using standard SharePoint object model) the associated lookup fields.

The other drawback that you have with associated lookup columns in SharePoint 2007 is that if you use them in list views or as view fields in SPQuery objects you need to always specify the main lookup column as well. If you miss it, you will receive the ugly SharePoint error “Cannot complete this action”.