“Oldie, but a goldie”. In this post I will describe how to configure SharePoint Lookup field programmatically. People are still asking for help on this topic a lot. Hopefully more and more developers are taking Code approach instead of XML to provision SharePoint features. I am still getting used to it since doing everything in XML is still a habit for me. In this case the approach is hybrid – the field itself is defined in XML, but the properties are set in code.

Let’s take the following Lookup field as an example:

 <Field ID="{FD47C240-961D-4AB0-9143-36172414E243}"
         Type="Lookup"
         Name="SPBB_Programme"
         StaticName="SPBB_Programme"
         DisplayName="Programme"
         Description=""
         Group="SPBB Columns"
         DisplaceOnUpgrade="TRUE"
         Required="TRUE" />

This field will reside in a list called Projects and it will allow users to select a Programme from another list. Usually you would have List and ShowField attributes set in XML. But what if you wanted the Lookup field to point to a List in different Web? There would be no way how to do it in XML.

Setting Lookup field properties by code might tricky, since one of the main properties SPFieldLookup.LookupList is Read Only. Luckily SPFieldLookup.SchemaXml is not. This is were you set List and WebId attributes of the Lookup field. I’ve got this handy function here to replace the XML attributes:

public static string ReplaceXmlAttributeValue(string xml, string attributeName, string value)
{
    if (string.IsNullOrEmpty(xml))
    {
        throw new ArgumentNullException("xml");
    }
    if (string.IsNullOrEmpty(value))
    {
        throw new ArgumentNullException("value");
    }
    int indexOfAttributeName = xml.IndexOf(attributeName, StringComparison.CurrentCultureIgnoreCase);
    if (indexOfAttributeName == -1)
    {
        xml = xml.Replace("<Field", "<Field " + attributeName + "='" + value + "'");

        return xml;
    }
    int indexOfAttibuteValueBegin = xml.IndexOf('"', indexOfAttributeName);
    int indexOfAttributeValueEnd = xml.IndexOf('"', indexOfAttibuteValueBegin + 1);
    return xml.Substring(0, indexOfAttibuteValueBegin + 1) + value + xml.Substring(indexOfAttributeValueEnd);
}

First you get both lists and the Lookup field that you want to configure:

var projectsList = web.GetList(SPUtility.ConcatUrls(web.ServerRelativeUrl, "Lists/Projects"));
var programmesLookupField = (SPFieldLookup)projectsList.Fields.GetFieldByInternalName("SPBB_Programmes");
var programmesList = web.GetList(SPUtility.ConcatUrls(web.ServerRelativeUrl, "Lists/Programmes"));

Then you replace List and WebId attributes in your field’s schema:

programmesLookupField.SchemaXml = ReplaceXmlAttributeValue(ReplaceXmlAttributeValue(programmesLookupField.SchemaXml, "List", programmesList.ID.ToString()), "WebId", web.ID.ToString());
programmesLookupField.LookupField = "Title";
programmesLookupField.Update();

And voila – you have a Lookup field pointing to a List in a different web site.

Hope this helps!

Permissions play an important role in SharePoint. Today in my first blog post I’d like to share a solution to a rare exception when SharePoint fails to assign permissions and you are left with an item that you can’t even delete. But first some background on the project which made me break the first rule of SharePoint – never mess with a SharePoint database directly.

The project that I finished working on half a year ago is a business process management system. Lots of lists, lots of libraries, over 30.000 sites (still counting) and of course the biggest pain for users (performance) and support team (issues) – item level permissions. These are assigned using event receivers (ItemAdded and ItemUpdated) based on a Department site column which was used in every site content type. This wouldn’t be an issue if we had kept everything according to Microsoft’s best practices and recommendations. The magic number of 5.000 was left far behind – after half year of usage some lists already had over 25.000 items with item level permissions. Almost every action (opening an item, saving it etc.) took from 3 to 10 seconds.

It was slow, but it worked until we hit about 15.000 items in a single list. Then every once in a while (1 in a 1000) an item was created without any permissions – we called them orphans. Too bad the exception was random and we couldn’t reproduce it. ULS logs would show this error:

System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint ‘Perms_PK’. Cannot insert duplicate key in object ‘dbo.Perms’. The duplicate key value is (9f07b6ef-25e1-4a7a-b06e-f60019e20255, 0x, ok/objektai/16855_.000).  The statement has been terminated.   

Users (except for site collection administrators) weren’t able to see these items in lists. They could open view or edit forms but couldn’t do anything that called an SPListItem.Update() or SPListItem.Delete(). Trying to view item permissions would result in an error:

Cannot complete this action

Luckily the URL contained ID of the SPListItem so we were able to check the properties of that item using PowerShell.

http://site/web/_layouts/User.aspx?obj={60782643-80D2-4F96-956F-C74A56263DC6},16855,LISTITEM&List={60782643-80D2-4F96-956F-C74A56263DC6

Usually items with unique permissions look like this:

HasUniqueRoleAssignments True
EffectiveBasePermissions FullMask
FirstUniqueAncestorSecurableObject Microsoft.SharePoint.SPListItem
ReusableAcl Microsoft.SharePoint.SPReusableAcl
RoleAssignments {Microsoft.SharePoint.SPRoleAssignment, Microsoft.SharePoint.SPRoleAssignment…}
AllRolesForCurrentUser {Full Control, Read, Limited Access, View Only…}
FirstUniqueAncestor Microsoft.SharePoint.SPListItem

but instead our items looked like this:

HasUniqueRoleAssignments
EffectiveBasePermissions FullMask
FirstUniqueAncestorSecurableObject
ReusableAcl
RoleAssignments
AllRolesForCurrentUser
FirstUniqueAncestor

Yes, all of these blank values were nulls. There were no possible ways of fixing these orphans either by code or using PowerShell – we couldn’t delete them (UnauthorizedAccessException) and SPListItem.ResetRoleInheritance() was returning an error System.Runtime.InteropServices.COMException (0x80004005): Cannot complete this action. The only way to do ResetRoleInheritance was to use a SharePoint database stored procedure proc_SecResetItemPerm. It takes SiteId, WebId, OldScopeId, Item Url and DocId as parameters so first we need to get them. PowerShell works great for that. Here is an example script with it’s output:

Add-PSSnapin Microsoft.Sharepoint.Powershell
$site = Get-SPSite http://site
$site.ID
#>>9f07b6ef-25e1-4a7a-b06e-f60019e20255

$web = Get-SPWeb http://site/web
$web.ID
#>>c3536be5-a419-4c27-88fd-269316c18757

$list = $web.Lists[“List”]
$item = $list.GetItemById(16855)
$item.Url
#>>list/16855_.000

$item.UniqueId
#>>fab7dc68-50ad-4ea0-9c99-230dfdbc0567

Now the last parameter that we need is OldScopeId. The way to get it is by querying Perms table:

USE [WSS_Content]
GO

SELECT TOP 1 [ScopeId]
FROM [WSS_Content].[dbo].[Perms]
WHERE ScopeUrl = ‘web/list/16855_.000’
GO

Since we have all the parameters we can execute proc_SecResetItemPerm stored procedure. We do this by writing a query against WSS_Content database:

USE [WSS_Content]
GO

GODECLARE @return_value int,
@NewScopeId uniqueidentifier,
@RequestGuid uniqueidentifier

EXEC @return_value = [dbo].[proc_SecResetItemPerm]
@SiteId = ‘9f07b6ef-25e1-4a7a-b06e-f60019e20255’,
@WebId = ‘c3536be5-a419-4c27-88fd-269316c18757’,
@OldScopeId = ‘B2D7B4C0-8E02-4E5F-A611-020BE5770A8F’,
@Url = N’web/list/16855_.000′,
@DocId = ‘fab7dc68-50ad-4ea0-9c99-230dfdbc0567′,
@NewScopeId = @NewScopeId OUTPUT,
@RequestGuid = @RequestGuid OUTPUT

SELECT @NewScopeId as N’@NewScopeId’,
@RequestGuid as N’@RequestGuid’

SELECT ‘Return Value’ = @return_value
GO

The query returns NewScopeId and we end up with broken role inheritance and no new roles assigned. Now we can assign new permissions or delete that item.

Hope it helps!

Paul.