Referencing and Linking: Using Secondary Lists to Maintain Relationships in SharePoint Server 2007

Article
10/16/2013

Summary: Learn how to extend a custom Relationship field in Microsoft Office SharePoint Server 2007 to enable users to select a relationship type and store information in a secondary table.

Office Visual How To

Applies to: 2007 Microsoft Office System, Microsoft Office SharePoint Server 2007

Microsoft Corporation

March 2008

In this Microsoft Office Visual How To, you extend a custom Relationship field in Microsoft Office SharePoint Server 2007 to enable users to select the type of relationship between two documents and store the information in a secondary table. Both documents in the relationship can then automatically reflect any relationship changes that are made.

For information about how to create the custom Relationship field and control, and how to use the AssetUrlSelector class to select a document in the relationship, see Referencing and Linking: Using the Asset URL Selector in SharePoint Server 2007.

The example is divided into three parts:

Defining the relationship.
Storing the relationship information in a secondary list.
Retrieving and displaying the relationship.

In this example, you make two main enhancements to the custom Relationship field control:

Adding a drop-down menu to select the type of relationship.
Storing the relationship information in a secondary list.

Before making these changes, you must first create two additional lists.

The first one is a lookup list that contains the types of relationships that can be created. A list named Relationship Type Lookup contains a Title field. This list contains two items, with Rendition and Copy in the respective Title fields.

The second list contains the information about the relationship between two documents. It is a custom list named Relationship Lookup, and it contains the fields shown in Table 1.

Table 1. Fields in Relationship Lookup list

Column Type Description

Relationship TypeLookupGet information from: Relationship Type LookupIn this column: TitleValue of the relationship type.

Item 1TextContains the GUID for the first item in the relationship.

List 1TextContains the GUID of the list containing the first item in the relationship.

Item 2TextContains the GUID for the second item in the relationship.

List 2TextContains the GUID of the list containing the second item in the relationship.

In edit mode, you modify the appearance of the custom Relationship field control to include a drop-down menu, shown in Figure 1. This lets the user select the relationship type when the control is in edit mode.

Figure 1. Custom Relationship field drop-down menu

To do this, in the CreateChildControls method, instantiate a new DropDownList, populate it with the values from the Relationship Type Lookup list, and add it to the control collection, as follows.

   protected override void CreateChildControls()
   {
      base.CreateChildControls();

      // Add the asset picker when in edit mode.
      if (base.ControlMode == SPControlMode.Edit)
      {

         // Get the choices from the relationship types list.
         ddRelationshipType = new DropDownList();
         SPList list = this.Web.Lists["Relationship Type Lookup"];
         foreach (SPListItem item in list.Items)
         {
            ListItem ddItem = new ListItem(item["Title"].ToString(), 
              item.ID.ToString());
            ddRelationshipType.Items.Add(ddItem);
         }
         this.Controls.Add(ddRelationshipType);

         urlSelector = new AssetUrlSelector();
         this.Controls.Add(urlSelector);
      }
   }

   Protected Overrides Sub CreateChildControls()
      MyBase.CreateChildControls()

      ' Add the asset picker when in edit mode.
      If MyBase.ControlMode = SPControlMode.Edit Then

         ' Get the choices from the relationship types list.
         ddRelationshipType = New DropDownList()
         Dim list As SPList = Me.Web.Lists("Relationship Type Lookup")
         Dim item As SPListItem
         For Each item In list.Items
            Dim ddItem As ListItem &_
              = New ListItem(item("Title").ToString(), item.ID.ToString())
            ddRelationshipType.Items.Add(ddItem)
         Next
         Me.Controls.Add(ddRelationshipType)

         urlSelector = New AssetUrlSelector()
         Me.Controls.Add(urlSelector)
      End If
   End Sub

The next step is to store the relationship information in the Relationship Lookup list (see Figure 2), rather than in the field directly. In the OnLoad event, instead of setting the list item field value, you obtain the GUIDs of the current item, current list, related item, and related list.

Figure 2. Relationship Lookup list

These values, together with the value for the relationship type, are stored as an item in the Relationship Lookup list.

      protected override void OnLoad(EventArgs e)
      {
         base.OnLoad(e);

         // Set the value if this is a postback.
         if (this.Page.IsPostBack && base.ControlMode == SPControlMode.Edit)
         {

            // this.ListItemFieldValue = urlSelector.AssetUrl;

            // Get the IDs for this item.
            string item1UniqueId = this.ListItem.UniqueId.ToString();
            string list1Id = this.ListId.ToString();

            // Get the IDs for the related item.
            SPFile relatedFile = this.Web.GetFile(urlSelector.AssetUrl);
            string item2UniqueId = relatedFile.Item.UniqueId.ToString();
            string list2Id = relatedFile.Item.ParentList.ID.ToString();
            SPFieldLookupValue relationshipType 
              = new SPFieldLookupValue(ddRelationshipType.SelectedValue);

            // Set the values in the relationship list.
            SPList list = this.Web.Lists["Relationship Lookup"];
            SPListItem item = list.Items.Add();
            item["item_x0020_1"] = item1UniqueId;
            item["list_x0020_1"] = list1Id;
            item["item_x0020_2"] = item2UniqueId;
            item["list_x0020_2"] = list2Id;
            item["Relationship_x0020_Type"] = relationshipType;
            item.Update();
         }
   }

   Protected Overrides Sub OnLoad(ByVal e As EventArgs)
      MyBase.OnLoad(e)

      ' Set the value if this is a postback
      If Me.Page.IsPostBack And MyBase.ControlMode = SPControlMode.Edit Then
         ' this.ListItemFieldValue = urlSelector.AssetUrl;

         ' Get the IDs for this item.
         Dim item1UniqueId As String = Me.ListItem.UniqueId.ToString()
         Dim list1Id As String = Me.ListId.ToString()

         ' Get the IDs for the related item.
         Dim relatedFile As SPFile = Me.Web.GetFile(urlSelector.AssetUrl)
         Dim item2UniqueId As String = relatedFile.Item.UniqueId.ToString()
         Dim list2Id As String = relatedFile.Item.ParentList.ID.ToString()
         Dim relationshipType As SPFieldLookupValue &_
           = New SPFieldLookupValue(ddRelationshipType.SelectedValue)

         ' Set the values in the relationship list.
         Dim list As SPList = Me.Web.Lists("Relationship Lookup")
         Dim item As SPListItem = list.Items.Add()
         item("item_x0020_1") = item1UniqueId
         item("list_x0020_1") = list1Id
         item("item_x0020_2") = item2UniqueId
         item("list_x0020_2") = list2Id
         item("Relationship_x0020_Type") = relationshipType
         item.Update()
      End If
   End Sub

In this example, you store the field value and additional relationship information in a secondary list instead of in the field of the list item. You do this in order to store the relationship information in a location that can be easily referenced by both documents in the relationship, and to circumvent any issues that may occur when you try to update the related document when a relationship is defined.

The relationship values are persisted to the Relationship Lookup list during the OnLoad method if a postback has occurred. You should ensure that the postback is initiated from the correct control (that is, the OK button) instead of from another control on the form. Another option is to store all the relationship information in the field value, and pick up and persist the information in the event handler.

A list that defines the relationship types is used to let users define and manage the types of relationships that can be created without having to modify any code. The SharePoint Server lookup field provides an easy way to set this up and work with it; however, this is most effective when the lookup list does not contain a large number of items.

The relationships in this example are peer-to-peer type relationships. You can expand the relationship types to include parent-child relationships so that the values in item 1 and list 1 are the "parent" and the values in item 2 and list 2 are the "children." This is useful, for example, if you want to define the relationship between an e-mail message file and its related attachment files. You can also extend the field control to retrieve and display the relationship information when the control is in display mode. For more information, see Referencing and Linking: Using Custom Fields to Display Relationships in SharePoint Server 2007.

This example uses GUIDs so that if either file in the relationship is renamed, the relationship is maintained. Also, the relationship is preserved in a backup/restore process, or during a content migration using the Content Migration API with the appropriate settings. However, if the file is moved (such as by selecting Move To in the drop-down ECB menu), the relationship is broken because the GUIDs for the moved item would change. You must account for this in an implementation and clean up the relationship information, such as during the ItemFileMoving event handler.

This approach is best suited for a site where you have a high degree of control over the repository and want to directly enforce referential integrity. There is an alternative approach that takes advantage of the link fix up infrastructure in Office SharePoint Server. This approach stores the link target data not on a separate list, but instead as data in an HTML field on the item itself. SharePoint Server then exposes these stored links on both the source and target item via the BackwardsLinks property and the ForwardLinks property on list items. In addition, SharePoint Server automatically updates the links if the item is moved or the name is changed.

Using Secondary Lists to Maintain Relationships

Watch the Video

Video Length: 00:07:17

File Size: 5.05 MB WMV

Share via

Referencing and Linking: Using Secondary Lists to Maintain Relationships in SharePoint Server 2007

Table 1. Fields in Relationship Lookup list

Additional resources