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.

The Insanity of getting Versions of a MultiLineText box set to Append Changes

Overcoming the limitations of the SharePoint Solution Generator