The views expressed on this weblog are mine alone and do not necessarily reflect the views of my employer, Avanade.

« The Insanity of getting Versions of a MultiLineText box set to Append Changes | Main | Overcoming the limitations of the SharePoint Solution Generator »

Adding Back Lookup Columns in the Provisioning Handler

When you point the SharePoint Solution Generator at an existing site, the biggest ommission in the code that is generated is that lookup columns disappear from any list that contains one. This is a big problem as it probably will have a cascading affect to your views, webparts and other areas of the site.

If you look through the generated code, you'll find the missing lookup columns aren't completely gone. If you look at the schema XML file for each list, the views actually still references the columns. The Content Types also still reference the columns. And it actually does show up in the Field definitions. The only problem is that it is commented out there.

Unfortunately the fix isn't as simple as uncommenting the field entry in the schema xml file. The entire reason it was commented out in the first place is the fact that SharePoint can't resolve the lookup column because the ListID Guid and SourceID Guid will never be correct. When the site is being provisioned, a new list with a new Guid for each column ID and the list id itself will be created. Because of this, we need to add back the column when the site is being provisioned.

When trying to get this working, here is the code I first tried to use:

public static void fixLookupColumn(SPWeb web, string colToFix, string listToAdd, string lookupCol, string listToLink)
    SPList list = web.Lists[listToAdd];
    SPList linkList = web.Lists[listToLink];

    web.AllowUnsafeUpdates = true;

    list.Fields.AddLookup(colToFix, linkList.ID, true);

This code successfully adds the lookup columns back to the list. But I found a not so subtle problem in the sites after a site was provisioned. The new site had the columns added in each of the views it normally was. But when you went to add an item to the list,or edit an item on the list, you would see a second duplicate column. for each of the lookup columns on the form. One would be in the same location it was in your original list, the second would be at the end of the list

At first I thought this was puzzling. After some digging (and debugging) I realized that the problem was with where SharePoint keeps track of column ordering. It is actually kept in the ContentType, not in the list's field collection itself. If you look at the original schema xml file, you will notice that the original lookup column was still defined in the content type section. This is why it shows up in the correct position when you add the column back to the list. But the side effect of readding the column programatically is that another entry gets added to the content types at the end of the list. To fix this, we need to add a little more code to our function:

foreach (SPContentType type in list.ContentTypes)
    int counter = 0;
    foreach (SPFieldLink field in type.FieldLinks)
        if (field.DisplayName == colToFix)
            if (counter > 1)
               //The second's a dupe record.

The code above will delete the second entry of the column in the ContentTypes defined for the list. This ensures that the column is ordered in the position we originally wanted it. You may have noticed, I am manipulating the ContentType's FieldLink's collection and not the Field Collection directly. This is because I discovered that you can not call the Delete method directly on Fields if they are not attached to a list. If you do you will get an exception saying as much.

Happy Coding.

EmailEmail Article to Friend

Reader Comments (7)


what works for me when creating custom lists with lookup-columns. Don't use the GUID of the referred lookup-list. Just use the url to this list!

Like so:
<Field Type="Lookup" DisplayName="Customer" Required="TRUE" List="Lists/Customer" ShowField="Title" ID="{2c4f0f01-addc-46a2-8606-2078a3bdf5eb}" StaticName="Customer" Name="Customer" />

Now this field is connected to a list name Customer located in Lists/Customer (relative to the list that uses the lookup list). If you combine it with resource-files you are very portable.

Greetings from Germany
Eric Bartels
October 15, 2007 | Unregistered CommenterEric Bartels
Awesome comment. I wish I knew that in the first place. It makes me wonder why the Solution Generator didn't do that in the first place.
October 16, 2007 | Registered CommenterDavid San Filippo
Thank you :-)

I really do not know why this way isn't documented. This makes creating lookup-list-relations very, very easy ...
October 17, 2007 | Unregistered CommenterEric Bartels
I created my lists in that way, but the require="TRUE" option does not work for me, i created a custom list and i realized that required option neither work.
I do not know if it is my WSS3.0 installation!! Any idea???

January 2, 2008 | Unregistered CommenterRandall Ulloa Acuña
This is one of the best sites I have ever found. Thanks!!! Very nice and informal. I enjoy being here.
January 17, 2008 | Unregistered CommenterAssissotom
Eric Bartels,
Fantastic comment. I got this working on first go!! saved me hours.

March 18, 2009 | Unregistered CommenterSameer Dhoot
@Eric Bartels
Thanks for the comment. That information solved a problem that I had been researching for 10+ hours.
June 18, 2009 | Unregistered CommenterSteven S

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
All HTML will be escaped. Hyperlinks will be created for URLs automatically.