This post describes an issue with the Office 2007 document information panel displaying properties for Lookup type columns in SharePoint, where those Lookup type columns were created programmatically via a feature receiver.

You have a document item in a document library, with a Lookup type column configured to reference a target list in the site collection. You edit the document with Office and open the document information panel, select the dropdown control for your Lookup type column and it displays blank entries or the Title values of the documents in the document library – not the Title values of the list items in the Lookup columns target list. Yet when you edit the document properties using the SharePoint UI, the Lookup column works as expected.

The Title value described here will vary according to the column selected as the value source in the target list of the Lookup column (SPFieldLookup.LookupField).

The code for creating a Lookup column might look something this;

targetWeb.Fields.AddFieldAsXml(columnSchemaXml);

var baseField = targetWeb.Fields.Cast<SPField>().FirstOrDefault(f => f.Id.Equals(new Guid(schemaFieldId)));

lookupField = baseField as SPFieldLookup;
lookupField.LookupWebId = lookupWebId;
lookupField.LookupList = lookupList.ID.ToString();
lookupField.LookupField = "Title";
lookupField.AllowMultipleValues = true | false;

lookupField.Update(true);
targetWeb.Update();

And the CAML schema XML for such a column might look like;

<Field
	Type="LookupMulti"
	DisplayName="My Lookup Column"
	ShowField="Title"
	Mult="TRUE"
	Group="My Columns"
	ID="{FD70101B-DAA7-4EE2-A42D-9512137670AB}"
	StaticName="MyLookupColumn"
	Name="MyLookupColumn" />

All perfectly reasonable and as mentioned works fine using the SharePoint UI, so why won’t the Office 2007 DIP place nice?

The answer lies in the way the SPFieldLookup.LookupList property value was set when creating the field;

lookupField.LookupList = lookupList.ID.ToString();

Which results in the property being set to, say, 68ABD356-8BC4-443b-B935-3BB5E8B25D5A, note without the enclosing curly brace characters. For some reason Office requires that the SPFieldLookup.LookupList has the enclosing curly brace characters for it to work correctly. So the code above should be rewritten as;

lookupField.LookupList = lookupList.ID.ToString("B");

So how do you fix this on an already existing site?

Using the SharePoint UI, you could remove the column and add it back, but then you may have a whole load of post-mod content editing to do.

You can try to modify the column programmatically, but attempting to set the SPFieldLookup.LookupList property will now result in a Exception because the property setter won’t allow you to set once it already has a value.

The following code, however, can be used to workaround this issue, although it may not work in versions beyond 2010.

The code below extracts the fields underlying schema XML, updates the List attribute, sets the schema XML back and updates the field, as fortuneatly, the SPFieldLookup.SchemaXml property is settable.

using (var site = new SPSite("http://portal"))
using (var web = site.OpenWeb())
{
	web.AllowUnsafeUpdates = true;
	try
	{
		var field = web.Fields.Cast<SPField>().FirstOrDefault(f => f.InternalName == "MyLookupColumn");
		var fld = field as SPFieldLookup;
		if (!fld.LookupList.StartsWith("{"))
		{
			var xml = fld.SchemaXml;
			var xd = new XmlDocument();
			xd.LoadXml(xml);
			var xn = xd.CreateNavigator();
			var at = xn.SelectSingleNode("Field/@List");
			at.SetValue(string.Format("{{{0}}}", fld.LookupList));
			xml = xd.OuterXml;
			fld.SchemaXml = xml;
			fld.Update(true);
			web.Update();
		}
	}
	finally
	{
		web.AllowUnsafeUpdates = false;
	}
}