Fear and Loathing

The SharePoint UI is great for end users. With very little training they can go off and create new custom lists and through the magic of a Lookup field they can create lookups into other lists and life is grand. For the developer though life is a rotten bag of apples when it comes to Lookup fields.

There are two ways to create new fields in SharePoint sites. You can define them through Xml or create them programatically. With the Xml definitions, it's a matter of copying the CUSTLIST definition (which is just a simple empty list with a single field, Title) to your new definition and add fields. Here's the definition for a new text field:

<Fields>
   <Field Name="MyField" DisplayName="My Special Field" Type="Text" />
</Fields>

Simple and easy. The <Field> tag is defined in SCHEMA.XML for your custom list and supports all the tags that are in the SDK documentation. Well, almost. The Lookup type is there and so if you wanted to define it you would think you do something like this:

<Fields>
   <Field Name="MyLookupField" DisplayName="My Special Field" Type="Lookup" />
</Fields>

The documentation says that the List and ShowField attributes can be used with this type. The List attribute just says it's the internal name of the list (which we would assume that it would be the list we want to lookup values from). The ShowField attribute says it's the field name to display and be used to override the default (Title) and display another field from an external list. There's also another attribute called FieldRef which is the name of another field to which the field refers to, such as for a Lookup field. All in all, it's very confusing but you would think you could do this:

<Fields>
   <Field Name="MyLookupField" DisplayName="My Special Field" Type="Lookup" List="MyLookupList" />
</Fields>

And if you don't want the lookup to use the Title Field in MyLookupList then you can use:

<Fields>
   <Field Name="MyLookupField" DisplayName="My Special Field" Type="Lookup" List="MyLookupList" FieldRef="LookupFieldName" />
</Fields>

So let's put this to test and have some real data. Let's create two custom lists called Employee and Department. Each entry in the Employee list has a Lookup field that points to the Name field in the Department list. Here's the Lookup field definition in our Employee list:

<Fields>
   <Field Name="Department" DisplayName="Department Name" Type="Lookup" List="Department" FieldRef="Name" />
</Fields>

However if you create your lists you'll notice two things. First, if you add an item to your Employee list (the one with the Lookup field in it) you'll see there's no choices available for Department (assuming you added values to that list first). Second, if you try to modify the Lookup field through the UI you get this nasty message:

Guid should contain 32 digits with 4 dashes (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx).

So what gives? Simple. The List attribute, while it says it's supposed to be Text is but it's not the name of the list. It's the Guid (in the form listed above). The problem is of course that Guids are unique and only known after they're generated. There's nothing in an Xml file (no matter how great the Xml file might be) that can dynamically retrieve the Guid. So Lookup fields, IMHO, can't be used in SCHEMA.XML because they have to be the Guid of the list and that's not known until the list is created first (feel free to jump in and correct me if I'm wrong).

Okay if we can't use SCHEMA.XML to do this, we can write code. Yes, beautiful glorius code. If you have a Lookup field and you retrieve the raw text from it, it looks like this:

42;#Information Services

The 42 refers to (besides the answer to life, the universe, and everything) the ID of the item in whatever list you're looking up. When you retrieve the lookup value you get "42;#Information Services" which you're going to have to transform with a simple little RegEx call if you want to show it to a user.

So now you're thinking if I can retrieve it and get "42;#Information Services" I should set it the same way right? Nope. What you need to do is set the Lookup field with the ID of the value it's looking up in the other list. Internally when you set that, SharePoint will do a join and retrieve the textual representation of the lookup information and save it for you.

Okay, some code to explain all this. This assumes that the site is created with both an Employee and Department list. This snippet will:

• Add a new Lookup field called Department to our Employee List
• Fill in some imaginary Department Names
• Fill in some imaginary Employess that report to various Departments

The trick here is that you need to retrieve the ID of the item in the Lookup list based on name, then use that ID and set it in the other list. This is done by a simple call to the GetItems method on the list we're looking for. There are other ways to do this so for example if you have a small list you can load it up into a Hashtable and use the name as the key and the ID as the value. Whatever works for you as the call to the query can be expensive so you wouldn't want to do this for everything but if you just need it for a report or some data loading it's not too bad. Now when you look at your Employee record you'll see it's got a Hyperlink to the Department Name field in the Department list. 15 seconds work for a user in the UI, a couple of hours for you in Visual Studio. Enjoy.