SharePoint Field Relationship Delete Behavior

I am trying to stay focused only on writing my Business Management in Office 365 and SharePoint posts, but ran into an old problem in a new scenario today and wanted to share within this post. That is, how to programmatically enforce field relationships with the RelationshipDeleteBehavior property using the managed .NET CSOM for SharePoint.

Let’s say you’ve built a SharePoint field XML as something like the following:

fieldXml = “<Field ID=\”” + fieldGuidString + “\” ” +
“Name=\”” + fieldName + “\” DisplayName=\”” + displayName + “\” ” +
“Required=\”FALSE\” Type=\”Lookup\” ” +
“EnforceUniqueValues=\”FALSE\” ” +
“Overwrite=\”TRUE\” ” +
“UnlimitedLengthInDocumentLibrary=\”FALSE\” ” +
“List=\”” + listId + “\” ” +
“ShowField=\”Title\” ” +
“Mult=\”FALSE\” ” +
“Group=\”” + columnGroupString + “\”/>”;

If you deploy the above XML as a SiteColumn, everything will work fine. As a result, you’ll have a lookup column to the list defined by GUID “listId” which references that list’s Title field on items in the consuming list. But, if you try to include the RelationshipDeleteBehavior attribute to that XML…for example:

fieldXml = “<Field ID=\”” + fieldGuidString + “\” ” +
“Name=\”” + fieldName + “\” DisplayName=\”” + displayName + “\” ” +
“Required=\”FALSE\” Type=\”Lookup\” ” +
“EnforceUniqueValues=\”FALSE\” ” +
“Overwrite=\”TRUE\” ” +
“UnlimitedLengthInDocumentLibrary=\”FALSE\” ” +
“List=\”” + listId + “\” ” +
“ShowField=\”Title\” ” +
“Mult=\”FALSE\” ” +
“RelationshipDeleteBehavior=\”Cascade\” ” +
“Group=\”” + columnGroupString + “\”/>”;

…your code will error out with the message, “A site column cannot enforce a relationship behavior”.

SharePoint Field Relationship After Deployment

If you have a background on this, you may be thinking that you need to add the “Indexed” attribute to the XML, but that is secondary to this error. No matter how many attributes you add to the XML, if you deploy the field as SharePoint Site Column with RelationshipDeleteBehavior defined, it will produce the error above. Instead, you need to create the site column as shown in the first XML definition, and then run the code below to set the RelationshipDeleteBehavior.

The absolute key to this is, not only setting the field’s Index property to “true”, but also that you must perform the field-get operation (e.g. the second ‘get’ below) using Fields.GetFieldByInternalName and not Fields.GetByTitle, Fields.GetByName, or some other method. The GetFieldByInternalName method returns a TField object that can be cast as a FieldLookup column and the others do not (i.e. they cannot be cast from a Field to a FieldLookup object). It was this nuance that stumped me for a bit and it was only through the use of intellisense that I saw the difference (and later confirmed on https://docs.microsoft.com).

Here’s the code:

Microsoft.SharePoint.Client.Web targetWeb = clientContext.Web;
List targetList = targetWeb.Lists.GetByTitle(targetListTitle);
try
{

// first, set the Indexed property to true as a prerequisite to setting the RelationshipDeleteBehavior. Then set the RelationshipDeleteBehavior property.

clientContext.Load(targetList, itemList => itemList.Fields);
clientContext.ExecuteQuery();
Field targetField = targetList.Fields.GetByTitle(targetFieldTitle);
clientContext.Load(targetField);
clientContext.ExecuteQuery();
targetField.Indexed = true;
targetField.Update();
clientContext.ExecuteQuery();
FieldLookup targetFieldLookup = (FieldLookup)targetList.Fields.GetFieldByInternalName(targetField.InternalName);
clientContext.Load(targetFieldLookup);
clientContext.ExecuteQuery();
targetFieldLookup.RelationshipDeleteBehavior = RelationshipDeleteBehaviorType.Cascade;
targetFieldLookup.Update();
clientContext.ExecuteQuery();
}
catch (Exception ex)
{

}

Hope that helps somebody. I’ll be getting back to the Business Management in O365 and SharePoint series now.

Categories: Apps, Office 365 and O365, SharePoint, Software Development

Tags: Apps, Office 365 and O365, SharePoint