Programmatically enforcing relationship behavior for lookup columns in SharePoint 2010

Adding lookup fields programmatically using feature is a common task in SharePoint 2007/2010. Normally, you would create the site column using a Field feature element. However, The lookup field definition requires the Id of the parent list and you have no way to determine this at design time, besides they will be different from one site to another. ( List Instances are different than Site Columns or Content Types; you cannot specify a list instance ID as the case with Site Columns or Content Types).

This dependency will force you to create Lookup columns using code that look up the parent List ID and uses it to create a new field object to define the site column.

I used to do this frequently in SharePoint 2007 but when yesterday when testing that for SharePoint 2010, I just asked myself, how can I programmatically enforce the relationship behavior ?

If you need more info about list relationships in SharePoint 2010, refer to this blog post.

This is straightforward using the UI from the column settings page and fortunately it’s as easy using code.

3

 

Consider the following example, I have two lists :

Parent List –> Employees
Child List –> Projects

I have a “Team Leader” field in the “Projects” list referencing the Employees List to specify the team leader of each project in the “Projects List”. To create a lookup field and enforcing the relationship behavior, see the following code snippet:

string teamLeaderFieldInternalName = projectsList.Fields.AddLookup(“Team Leader”, employeesList.ID, false);
SPFieldLookup teamLeaderField = (SPFieldLookup)projectsList.Fields.GetFieldByInternalName(teamLeaderFieldInternalName);
teamLeaderField.LookupField = employeesList.Fields[“Title”].InternalName;
teamLeaderField.RelationshipDeleteBehavior = SPRelationshipDeleteBehavior.Cascade;
teamLeaderField.Update();

The previous code will raise the following exception “This lookup field cannot enforce a relationship behavior because it is not indexed”. To fix you need to set the Indexed property of teamLeaderField to true as shown below :

string teamLeaderFieldInternalName = projectsList.Fields.AddLookup(“Team Leader”, employeesList.ID, false);
SPFieldLookup teamLeaderField = (SPFieldLookup)projectsList.Fields.GetFieldByInternalName(teamLeaderFieldInternalName);
teamLeaderField.LookupField = employeesList.Fields[“Title”].InternalName;
teamLeaderField.RelationshipDeleteBehavior = SPRelationshipDeleteBehavior.Cascade;
teamleaderField.Indexed = true;
teamLeaderField.Update();

You might also want to check the other members in the SPRelationshipDeleteBehavior Enum, I have copied the members along with their description from MSDN for easier reference below :

None: Nothing happens when a parent item is deleted and the child item is left pointing to the parent as an orphan.

Cascade: A cascade delete happens when deleting a parent item deletes all related children items of the related list.

Restrict: Restricts a parent item having any children items in the related list from being deleted.

Advertisements

One thought on “Programmatically enforcing relationship behavior for lookup columns in SharePoint 2010”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s