Tuesday, November 30, 2010

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):

image

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:

image

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”.

12 comments:

  1. Hi Stefan,

    The ide is grate, and it works.

    But have you ever tried to remove such created column?

    Each time I try to do it I get:
    "One or more field types are not installed properly. Go to the list settings page to delete these fields."

    I can not delete the column from the code. The only possible way is to delete the whole list.
    Any ideas, sugestion how to overcome it?

    Best Regards,
    Szymon

    ReplyDelete
  2. Hi Szymon,
    thanks for noticing this important bit, I already updated the posting to this effect.

    ReplyDelete
  3. Great blogpost Stefan, thank you! I've tested this solution and works perfect. Although I do have a question: do you have an idea what happens with this list/column when upgraded to 2010?

    ReplyDelete
  4. Hi Achapey,
    What kind of upgrade do you have in mind - the using of the same field provisioning feature in SharePoint 2010 or the upgrade of the content database containing the lookup fields and their list. If it is the latter I can check this for you shortly.
    Stefan

    ReplyDelete
  5. Is it possible to do this without code, possibly SPD? I have the "Projects" list with columns "Project" and "Project Status". From a second list I need to lookup "Project" and a read-only version of "Project Status". Thx, Brian

    ReplyDelete
  6. Hi,
    no, it's not possible to do this with the SPD 2007. I have a handy tool though, that can be useful - you can download it from here - https://sites.google.com/site/stefanstanev/sharepoint-samples-1/fldedit.zip
    With it you can create site and list fields specifying the SharePoint field CAML schema as you normally do in a site fields' feature. You can also modify the schema of existing site and list fields with it. Note that certain attriubtes of the field's schema cannot be changed once the field is created. Let me know if you need some help with the tool and whether it is of help for you.
    Greets
    Stefan

    ReplyDelete
  7. Hi, when I create a lookup in a list that already has data, it is not listed, it's when I sync, it appears, but it returns me an error of 32 GUID, or he lost the reference list, how can I solve this ?

    ReplyDelete
  8. Hi Anonymous,
    Can you post the CAML definition of your associated lookup field. Maybe there is something wrong in it, because normally the field should appear in the list no matter whether you have data in the list or not. Also I couldn't understand what you meant by "when I sync" - is this related to SharePoint content deployment (using content deployment paths/jobs) or something different.
    Greets
    Stefan

    ReplyDelete
  9. Hi, did you find out if having this special (undeletable) field in place will break something on upgrade from 2007 to 2010?

    ReplyDelete
    Replies
    1. Hi Anonymous,

      Unfortunately no. I haven't had time to check this yet, I will add a comment to confirm if it either works or breaks anything when I test it myself.

      Greets
      Stefan

      Delete
  10. Dear Stefan,
    your solutions seems to be exactly what I'm looking for, but I have not idea about 'SPFieldCollection.AddFieldAsXml method'.
    Is there any step-by-step walkthrough somewhere?
    Pleaseee, that would be really great!

    Thanks + Regards


    RFC

    ReplyDelete
  11. Excellent. That worked exactly as planned with a lookup column. Do you know how to get one working if it allows multiple values?

    ReplyDelete