Export (0) Print
Expand All

Lookups and List Relationships

SharePoint 2010

Last modified: December 01, 2010

Applies to: SharePoint Foundation 2010

In this article
List Relationships and Data Integrity
Creating a List Relationship
Creating Multiple-Column Lookups
Discovering List Relationships
Discovering Related Items

In Microsoft SharePoint Foundation, a lookup field is a field that is configured to get its value from a field in another list. Adding a lookup field to a list creates a relationship between that list and the list that provides data.

How you refer to the two lists depends on your point of view. From the lookup field's point of view, the list that provides data is the target list and the field where the data comes from is the target field. From the target list's point of view, the list that contains the lookup field is a related list. The lookup field itself is a related field.

Tip Tip

The SPList class has a GetRelatedFields method that you can use to discover lookup fields that point to a list.

For the sake of simplicity, and to prevent confusion, the two lists are sometimes said to have a parent-child relationship. The child list has the lookup field. The parent list has the data source, which is the field that is the target of the lookup field.

In Microsoft SharePoint Foundation 2010, you can configure a lookup field in a child list so that it places a deletion constraint on items in the parent list. When you create or edit a lookup column through the user interface, you have the option to Enforce relationship behavior. If you select this option, you can then select either Restrict delete or Cascade delete. Restrict delete prevents an item in the parent list from being deleted if it is referenced by one or more related items in the child list. With cascade delete, deleting an item from the parent list causes all related items to be deleted from the child list also.

To enable the same behavior in code, set the RelationshipDeleteBehavior property of an SPFieldLookup object to a value in the SPRelationshipDeleteBehavior enumeration, either Restrict or Cascade.

Important note Important

For SharePoint Foundation to either restrict or cascade deletions, the lookup field must be indexed. Set the Indexed property of the field to true.

The purpose of a deletion constraint is to enforce referential integrity. You apply the constraint when you do not want an item that contains a lookup field to be orphaned by pointing to a nonexistent item in the target list.

For example, suppose that you have two lists, Customers and Addresses. You decide to relate the two lists by adding a lookup field named Customer ID to the Addresses list, and you point the lookup field to the ID field in the Customers list. After you configure the lookup, you place a constraint on the relationship between the two lists by specifying that deletions from the Customers list must cascade to the Addresses list. Choosing to cascade deletions enforces referential integrity by ensuring that the Addresses list does not collect orphaned addresses. Whenever an item is deleted from Customers, related items are automatically deleted from Addresses.

Suppose that you also have a Pending Orders list that, like Addresses, is linked to the Customers list by a lookup field that points to the ID field in Customers. You might decide that you do not want a customer record to be deleted from the Customers list if the customer has a pending order on the Pending Orders list. In this case, you could enforce referential integrity by configuring the lookup field on Pending Orders to restrict deletions from the parent list, Customers.

A side-effect of choosing either to cascade or to restrict deletions is that the values of lookup fields are validated when they are set. When someone creates a new item through the user interface, the lookup column is represented on the form by a drop-down list if the target list has fewer than 20 items; in this case, the user is limited to selecting only valid values. If the target has more than 20 items, a different control is used and the user must type a value; in this case, typing an invalid value results in red validation text on the form. Similar validation is done when you use the object model to set the value of a lookup field. In this case, if the specified value does not point to an existing item on the target list, SharePoint Foundation throws an exception.

You have the option not to place any deletion constraint on the lookup list. For example, in addition to Addresses and Pending Orders, you might have a Complete Orders list that, like the other related lists, points to the ID field in the Customers list. You could decide that you want to keep a record of any complete order even when you no longer keep a record for the customer who placed the order. Referential integrity is not an issue, so you specify no constraint on deletions from the lookup list. In this case, deleting an item from the Customers list does not affect the Complete Orders list.

A lookup field is represented by an instance of the SPFieldLookup class. The class includes constructors, but the easiest way to create a lookup field is to call the AddLookup method of the SPFieldCollection class. The AddLookup method has an overload that allows you to create a lookup to a list in a different website from the site where you are creating the lookup.

Note Note

You can create a relationship between lists in different websites, but you cannot set a restrictive behavior on the relationship. SharePoint Foundation does not enforce referential integrity on cross-web lookups.

To add a lookup column to a list

  1. Get a reference to the list's field collection by accessing its Fields property.

  2. Call the AddLookup method of the SPFieldCollection object, passing the ID of the lookup list (the data source) in the second parameter.

    The AddLookup method returns a string with the internal name of the new field.

  3. Get a reference to the SPFieldLookup object that represents the new field by calling the GetFieldByInternalName method, passing the string that is returned by the AddLookup method as an argument.

    The GetFieldByInternalName method returns an SPField object, which you must cast to type SPFieldLookup.

  4. Set the LookupField property to the internal name of the field in the lookup list that the SPFieldLookup object should use as its data source.

    The target of the LookupField property must be one of the following field types:

    In addition, the target can be type SPFieldCalculated if the output is text. For more information, see the OutputType property. The target can be type SPFieldComputed if lookups are enabled for the field. For more information, see the EnableLookup property.

  5. (Optional) If you intend to specify a deletion behavior other than None (the default), set the Indexed property to true.

  6. (Optional) Set the RelationshipDeleteBehavior property with an SPRelationshipDeleteBehavior enumeration value.

    Important note Important

    The user must have ManageLists permission on the lookup list in order to specify either Cascade or Restrict. For more information, see the SPBasePermissions enumeration.

    In addition, setting a relationship behavior fails if:

    • The lookup field allows multiple values.

      Before setting the RelationshipDeleteBehavior property, be sure that the AllowMultipleValues property returns false.

    • Existing list items contain invalid values for the lookup field.

      This can occur if the lookup field's RelationshipDeleteBehavior property was previously set to None and someone deleted the item on the target list that the lookup field points to. The lookup field is thus orphaned; it contains an invalid value. A relationship behavior cannot be enforced if the list contains orphaned lookup fields.

    • The lookup field points to a list in another website.

      Check the value of the lookup field's LookupWebId property.

    • The number of items in the list exceeds the maximum set for large lists.

      Compare the value that is returned by the list's ItemCount property with the value that is returned by the web application's MaxItemsPerThrottledOperation property.

  7. Call the Update method to commit changes to the object.

Example

The following example is a console application that creates a relationship between the Customers list and the Pending Orders list. The application calls the AddLookup method to add a lookup field named Customer ID to the Pending Orders list and points the field at the ID field on the Customers list. The new Customer ID field is indexed and set to restrict deletions from the lookup list.

Imports System
Imports Microsoft.SharePoint

Module ConsoleApp

    Sub Main()
        Using siteCollection As New SPSite("http://localhost")
            Using site As SPWeb = siteCollection.OpenWeb()
                Dim lookupList As SPList = site.Lists.TryGetList("Customers")
                Dim relatedList As SPList = site.Lists.TryGetList("Pending Orders")

                If lookupList IsNot Nothing AndAlso relatedList IsNot Nothing Then
                    Dim strPrimaryCol As String = relatedList.Fields.AddLookup("Customer ID", lookupList.ID, True)
                    Dim primaryCol As SPFieldLookup = _
                        DirectCast(relatedList.Fields.GetFieldByInternalName(strPrimaryCol), SPFieldLookup)

                    primaryCol.LookupField = lookupList.Fields("ID").InternalName
                    primaryCol.Indexed = True
                    primaryCol.RelationshipDeleteBehavior = SPRelationshipDeleteBehavior.Restrict
                    primaryCol.Update()
                End If
            End Using
        End Using
        Console.Write(vbLf & "Press ENTER to continue...")
        Console.ReadLine()
    End Sub
End Module

In SharePoint Foundation 2010, you can create multiple-column lookups to the same lookup list. For example, suppose that you have two lists, Customers and Pending Orders. You create a relationship between the lists by adding a Customer ID column to the Pending Orders list and point the column at the ID field in the Customers list. When you view items in the Pending Orders list, you want to see important information about the customer who placed the order in addition to information that describes the order itself. To solve this problem, you decide that in addition to the primary lookup column, Customer ID, you will add three secondary lookup columns to show the customer's first name, last name, and phone number.

Secondary columns in a multiple-column lookup depend on the primary column for their relationship to the lookup list. You cannot establish a deletion constraint on a secondary lookup column. The constraint that is set for the primary column is the only one that is applied to the lookup list.

The procedure for creating a secondary lookup column is nearly identical to the one for creating a primary lookup column. The key difference is that you create the field by calling the AddDependentLookup method of the SPFieldCollection class instead of the AddLookup method. The AddDependentLookup method has two parameters: the display name to use for the secondary field, and the ID of the primary field.

Example

The following example is a console application that creates a relationship between the Customers list and the Pending Orders list. The application calls the AddLookup method to add a primary lookup field named Customer ID to the Pending Orders list and points the field at the ID field on the Customers list. The new Customer ID field is indexed and set to restrict deletions from the lookup list.

After creating the primary lookup field, the application creates three secondary fields named First Name, Last Name, and Phone. The application creates these fields by calling the AddDependentLookup method of the object that represents the fields collection of the Pending Orders list.

Imports System
Imports Microsoft.SharePoint

Module ConsoleApp

    Sub Main()
        Using siteCollection As New SPSite("http://localhost")
            Using site As SPWeb = siteCollection.OpenWeb()
                Dim lookupList As SPList = site.Lists.TryGetList("Customers")
                Dim relatedList As SPList = site.Lists.TryGetList("Pending Orders")

                If lookupList IsNot Nothing AndAlso relatedList IsNot Nothing Then

                    ' Create the primary column.
                    Dim strPrimaryCol As String = relatedList.Fields.AddLookup("Customer ID", lookupList.ID, True)
                    Dim primaryCol As SPFieldLookup = _
                        DirectCast(relatedList.Fields.GetFieldByInternalName(strPrimaryCol), SPFieldLookup)

                    primaryCol.LookupField = lookupList.Fields("ID").InternalName
                    primaryCol.Indexed = True
                    primaryCol.RelationshipDeleteBehavior = SPRelationshipDeleteBehavior.Restrict
                    primaryCol.Update()


                    ' Create the secondary columns.

                    Dim strFirstNameCol As String = relatedList.Fields.AddDependentLookup("First Name", primaryCol.Id)
                    Dim firstNameCol As SPFieldLookup = _
                        DirectCast(relatedList.Fields.GetFieldByInternalName(strFirstNameCol), SPFieldLookup)
                    firstNameCol.LookupField = lookupList.Fields("First Name").InternalName
                    firstNameCol.Update()

                    Dim strLastNameCol As String = relatedList.Fields.AddDependentLookup("Last Name", primaryCol.Id)
                    Dim lastNameCol As SPFieldLookup = _
                        DirectCast(relatedList.Fields.GetFieldByInternalName(strLastNameCol), SPFieldLookup)
                    lastNameCol.LookupField = lookupList.Fields("Last Name").InternalName
                    lastNameCol.Update()

                    Dim strPhoneCol As String = relatedList.Fields.AddDependentLookup("Phone", primaryCol.Id)
                    Dim phoneCol As SPFieldLookup = _
                        DirectCast(relatedList.Fields.GetFieldByInternalName(strPhoneCol), SPFieldLookup)
                    phoneCol.LookupField = lookupList.Fields("Phone").InternalName
                    phoneCol.Update()
                End If
            End Using
        End Using
        Console.Write(vbLf & "Press ENTER to continue...")
        Console.ReadLine()
    End Sub
End Module

You can discover which fields in related lists depend on information in a lookup list by calling the GetRelatedFields method on an SPList object that represents the lookup list. The method returns a collection of SPRelatedField objects with the following properties:

  • FieldId . Gets the Id of the lookup field in the related list.

  • ListId . Gets the ID of the related list.

  • WebId . Gets the ID of the website where the related list is located.

  • RelationshipDeleteBehavior . Gets the deletion constraint that is defined for the relationship.

The GetRelatedFields method has an overload that allows you to get information about only those fields that specify a particular deletion constraint.

Information is returned only for related fields that represent the primary column in a multiple-column lookup. To get information about secondary columns, call the GetDependentLookupInternalNames method of the SPFieldLookup object that represents the primary column.

Example

The following example is a console application that reports information about fields in the Customers list that provide data to lookup fields in other lists. For each related field that represents a primary column, the application calls the GetDependentLookupInternalNames method to get a list of fields that represent secondary columns.

Imports System
Imports System.Collections.Generic
Imports Microsoft.SharePoint

Module ConsoleApp

    Sub Main()
        Using siteCollection As New SPSite("http://localhost")
            Using site As SPWeb = siteCollection.OpenWeb()
                Dim targetList As SPList = site.Lists.TryGetList("Customers")
                Dim relatedFields As SPRelatedFieldCollection = targetList.GetRelatedFields()

                Dim strSeparator As String = New [String]("="c, 70)
                Dim strUnderline As String = New [String]("-"c, 20)
                Dim strFormat As String = "Target Field: {0} | Related Field: {1}"

                Console.WriteLine(strSeparator)
                For Each fieldInfo As SPRelatedField In relatedFields
                    Using relatedSite As SPWeb = siteCollection.AllWebs(fieldInfo.WebId)
                        Dim relatedList As SPList = relatedSite.Lists.GetList(fieldInfo.ListId, False)
                        Dim relatedField As SPFieldLookup = TryCast(relatedList.Fields(fieldInfo.FieldId), SPFieldLookup)
                        Dim targetField As SPField = targetList.Fields.GetFieldByInternalName(relatedField.LookupField)

                        Console.WriteLine(vbLf & "Target List: {0} ({1}) | Related List: {2} ({3})", _
                                          targetList.Title, site.Title, relatedList.Title, relatedSite.Title)

                        Console.WriteLine(vbLf & "Primary Column")
                        Console.WriteLine(strUnderline)
                        Console.WriteLine(strFormat, targetField.Title, relatedField.Title)
                        Console.WriteLine("Deletion behavior: {0}", relatedField.RelationshipDeleteBehavior)

                        Console.WriteLine(vbLf & "Secondary Columns")
                        Console.WriteLine(strUnderline)

                        Dim dependents As List(Of String) = relatedField.GetDependentLookupInternalNames()
                        Dim i As Integer = 0
                        While i < dependents.Count
                            Dim lookup As SPFieldLookup = _
                                TryCast(relatedList.Fields.GetFieldByInternalName(dependents(i)), SPFieldLookup)
                            Dim field As SPField = targetList.Fields.GetFieldByInternalName(lookup.LookupField)

                            Console.WriteLine(strFormat, field.Title, lookup.Title)
                            i = i + 1
                        End While
                        Console.WriteLine()
                        Console.WriteLine(strSeparator)
                    End Using
                Next
            End Using
        End Using
        Console.Write(vbLf & "Press ENTER to continue...")
        Console.ReadLine()
    End Sub
End Module

The console application prints output that resembles the following example.

======================================================================

Target List: Customers (Team Site) | Related List: Complete Orders (Team Site)

Primary Column
--------------------
Target Field: ID | Related Field: Customer ID
Deletion behavior: None

Secondary Columns
--------------------
Target Field: Last Name | Related Field: Customer

======================================================================

Target List: Customers (Team Site) | Related List: Addresses (Team Site)

Primary Column
--------------------
Target Field: ID | Related Field: Customer ID
Deletion behavior: Cascade

Secondary Columns
--------------------
Target Field: Last Name | Related Field: Last Name
Target Field: First Name | Related Field: First Name

======================================================================

Target List: Customers (Team Site) | Related List: Pending Orders (Team Site)

Primary Column
--------------------
Target Field: ID | Related Field: Customer ID
Deletion behavior: Restrict

Secondary Columns
--------------------
Target Field: First Name | Related Field: First Name
Target Field: Last Name | Related Field: Last Name
Target Field: Phone | Related Field: Phone

======================================================================

Press ENTER to continue...

The user interface for SharePoint Foundation 2010 has an Insert Related List button on the ribbon that enables users to edit the page that displays a list by adding a related list Web Part. The related list Web Part displays items in other lists that are related to the selected item in the current list. For example, you could add a related list Web Part to the Customers list so that users can select a customer record and see items on the related Pending Orders list that pertain to the selected customer.

You can get the same information about related items through the object model by using the properties of the SPRelatedField objects that are returned by a call to the GetRelatedFields method of the SPList object that represents the lookup list.

Example

The following example is a console application that gets an item from the Customers list and finds related items in other lists in the site collection. The heavy lifting is done by the application's PrintRelatedItems method, which uses an item from the lookup list and information from an SPRelatedField object to construct a query against the related list.

Imports System
Imports Microsoft.SharePoint

Module ConsoleApp

    Sub Main()
        Using siteCollection As New SPSite("http://localhost")
            Using site As SPWeb = siteCollection.OpenWeb()
                Dim customerID As Integer = 1

                Dim list As SPList = site.Lists.TryGetList("Customers")
                If list IsNot Nothing Then

                    ' Get a customer record.
                    Dim customerRecord As SPListItem = Nothing
                    Try
                        customerRecord = list.GetItemById(customerID)
                    Catch ex As ArgumentException
                        Console.WriteLine(ex.Message)
                    End Try

                    ' Print related items.
                    If customerRecord IsNot Nothing Then
                        Console.WriteLine("Customer: {0} {1}", _
                        customerRecord(SPBuiltInFieldId.FirstName), customerRecord(SPBuiltInFieldId.Title))

                        ' Get related list items.
                        Dim relatedFields As SPRelatedFieldCollection = list.GetRelatedFields()
                        For Each fieldInfo As SPRelatedField In relatedFields
                            Using relatedSite As SPWeb = siteCollection.AllWebs(fieldInfo.WebId)
                                PrintRelatedItems(customerRecord, relatedSite, fieldInfo)
                            End Using
                        Next
                    End If
                End If
            End Using
        End Using
        Console.Write(vbLf & "Press ENTER to continue...")
        Console.ReadLine()
    End Sub

    Sub PrintRelatedItems(ByVal match As SPListItem, ByVal site As SPWeb, ByVal fieldInfo As SPRelatedField)
        Dim targetList As SPList = fieldInfo.LookupList
        Dim relatedList As SPList = site.Lists(fieldInfo.ListId)

        Dim relatedField As SPFieldLookup = TryCast(relatedList.Fields(fieldInfo.FieldId), SPFieldLookup)
        Dim targetField As SPField = targetList.Fields.GetFieldByInternalName(relatedField.LookupField)

        Dim value As Object = match(targetField.Id)

        Dim q As New SPQuery()
        q.Query = String.Format( _
        "<Where><Eq><FieldRef Name=""{0}""/><Value Type=""{1}"">{2}</Value></Eq></Where>", _
        relatedField.InternalName, value.GetType(), value)

        Dim items As SPListItemCollection = relatedList.GetItems(q)

        If items.Count > 0 Then
            Console.WriteLine(vbLf & "{0} has {1} related items:", relatedList.Title, items.Count)
            For Each item As SPListItem In items
                Console.WriteLine(item.DisplayName)
            Next
        End If
    End Sub

End Module
Show:
© 2014 Microsoft