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.

using System;
using Microsoft.SharePoint;

namespace RelatedLists
{
    class Program
    {
        static void Main(string[] args)
        {
            using (SPSite siteCollection = new SPSite("http://localhost"))
            {
                using (SPwebsite = siteCollection.OpenWeb())
                {
                    SPList lookupList = site.Lists.TryGetList("Customers");
                    SPList relatedList = site.Lists.TryGetList("Pending Orders");

                    if (lookupList != null && relatedList != null)
                    {
                        string strPrimaryCol = relatedList.Fields.AddLookup("Customer ID", lookupList.ID, true);
                        SPFieldLookup primaryCol = (SPFieldLookup)relatedList.Fields.GetFieldByInternalName(strPrimaryCol);

                        primaryCol.LookupField = lookupList.Fields["ID"].InternalName;
                        primaryCol.Indexed = true;
                        primaryCol.RelationshipDeleteBehavior = SPRelationshipDeleteBehavior.Restrict;
                        primaryCol.Update();
                    }
                }
            }
            Console.Write("\nPress ENTER to continue...");
            Console.ReadLine();
        }
    }
}

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.

using System;
using Microsoft.SharePoint;

namespace RelatedLists
{
    class Program
    {
        static void Main(string[] args)
        {
            using (SPSite siteCollection = new SPSite("http://localhost"))
            {
                using (SPwebsite = siteCollection.OpenWeb())
                {
                    SPList lookupList = site.Lists.TryGetList("Customers");
                    SPList relatedList = site.Lists.TryGetList("Pending Orders");

                    if (lookupList != null && relatedList != null)
                    {

                        // Create the primary column.
                        string strPrimaryCol = relatedList.Fields.AddLookup("Customer ID", lookupList.ID, true);
                        SPFieldLookup primaryCol = (SPFieldLookup)relatedList.Fields.GetFieldByInternalName(strPrimaryCol);

                        primaryCol.LookupField = lookupList.Fields["ID"].InternalName;
                        primaryCol.Indexed = true;
                        primaryCol.RelationshipDeleteBehavior = SPRelationshipDeleteBehavior.Restrict;
                        primaryCol.Update();


                        // Create the secondary columns.

                        string strFirstNameCol = relatedList.Fields.AddDependentLookup("First Name", primaryCol.Id);
                        SPFieldLookup firstNameCol = (SPFieldLookup)relatedList.Fields.GetFieldByInternalName(strFirstNameCol);
                        firstNameCol.LookupField = lookupList.Fields["First Name"].InternalName;
                        firstNameCol.Update();

                        string strLastNameCol = relatedList.Fields.AddDependentLookup("Last Name", primaryCol.Id);
                        SPFieldLookup lastNameCol = (SPFieldLookup)relatedList.Fields.GetFieldByInternalName(strLastNameCol);
                        lastNameCol.LookupField = lookupList.Fields["Last Name"].InternalName;
                        lastNameCol.Update();

                        string strPhoneCol = relatedList.Fields.AddDependentLookup("Phone", primaryCol.Id);
                        SPFieldLookup phoneCol = (SPFieldLookup)relatedList.Fields.GetFieldByInternalName(strPhoneCol);
                        phoneCol.LookupField = lookupList.Fields["Phone"].InternalName;
                        phoneCol.Update();
                    }
                }
            }
            Console.Write("\nPress ENTER to continue...");
            Console.ReadLine();
        }
    }
}

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.

using System;
using System.Collections.Generic;
using Microsoft.SharePoint;

namespace ConsoleApp
{
    class Program
    {
        static void Main(string[] args)
        {
            using (SPSite siteCollection = new SPSite("http://localhost"))
            {
                using (SPwebsite = siteCollection.OpenWeb())
                {
                    SPList targetList = site.Lists.TryGetList("Customers");
                    SPRelatedFieldCollection relatedFields = targetList.GetRelatedFields();

                    string strSeparator = new String('=', 70);
                    string strUnderline = new String('-', 20);
                    string strFormat = "Target Field: {0} | Related Field: {1}";

                    Console.WriteLine(strSeparator);
                    foreach (SPRelatedField fieldInfo in relatedFields)
                    {
                        using (SPWeb relatedSite = siteCollection.AllWebs[fieldInfo.WebId])
                        {
                            SPList relatedList = relatedSite.Lists.GetList(fieldInfo.ListId, false);
                            SPFieldLookup relatedField = relatedList.Fields[fieldInfo.FieldId] as SPFieldLookup;
                            SPField targetField = targetList.Fields.GetFieldByInternalName(relatedField.LookupField);

                            Console.WriteLine("\nTarget List: {0} ({1}) | Related List: {2} ({3})", targetList.Title, site.Title, relatedList.Title, relatedSite.Title);

                            Console.WriteLine("\nPrimary Column");
                            Console.WriteLine(strUnderline);
                            Console.WriteLine(strFormat, targetField.Title, relatedField.Title);
                            Console.WriteLine("Deletion behavior: {0}", relatedField.RelationshipDeleteBehavior);

                            Console.WriteLine("\nSecondary Columns");
                            Console.WriteLine(strUnderline);

                            List<string> dependents = relatedField.GetDependentLookupInternalNames();
                            for (int i = 0; i < dependents.Count; i++)
                            {
                                SPFieldLookup lookup = relatedList.Fields.GetFieldByInternalName(dependents[i]) as SPFieldLookup;
                                SPField field = targetList.Fields.GetFieldByInternalName(lookup.LookupField);

                                Console.WriteLine(strFormat, field.Title, lookup.Title);
                            }
                            Console.WriteLine();
                            Console.WriteLine(strSeparator);
                        }
                    }
                }
            }
            Console.Write("\nPress ENTER to continue...");
            Console.ReadLine();
        }
    }
}

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.

using System;
using Microsoft.SharePoint;

namespace ConsoleApp
{
    class Program
    {
        static void Main(string[] args)
        {
            using (SPSite siteCollection = new SPSite("http://localhost"))
            {
                using (SPwebsite = siteCollection.OpenWeb())
                {
                    int customerID = 1;

                    SPList list = site.Lists.TryGetList("Customers");
                    if (list != null)
                    {

                        // Get a customer record.
                        SPListItem customerRecord = null;
                        try
                        {
                            customerRecord = list.GetItemById(customerID);
                        }
                        catch (ArgumentException ex)
                        {
                            Console.WriteLine(ex.Message);
                        }

                        // Print related items.
                        if (customerRecord != null)
                        {
                            Console.WriteLine("Customer: {0} {1}", 
                            customerRecord[SPBuiltInFieldId.FirstName], customerRecord[SPBuiltInFieldId.Title]);

                            // Get related list items.
                            SPRelatedFieldCollection relatedFields = list.GetRelatedFields();
                            foreach (SPRelatedField fieldInfo in relatedFields)
                            {
                                using (SPWeb relatedSite = siteCollection.AllWebs[fieldInfo.WebId])
                                {
                                    PrintRelatedItems(customerRecord, relatedSite, fieldInfo);
                                }
                            }
                        }
                    }
                }
            }
            Console.Write("\nPress ENTER to continue...");
            Console.ReadLine();
        }

        static void PrintRelatedItems(SPListItem match, SPwebsite, SPRelatedField fieldInfo)
        {
            SPList targetList = fieldInfo.LookupList;
            SPList relatedList = site.Lists[fieldInfo.ListId];

            SPFieldLookup relatedField = relatedList.Fields[fieldInfo.FieldId] as SPFieldLookup;
            SPField targetField = targetList.Fields.GetFieldByInternalName(relatedField.LookupField);

            object value = match[targetField.Id];

            SPQuery q = new SPQuery();
            q.Query = string.Format(@"<Where>
                                            <Eq>
                                                <FieldRef Name=""{0}""/>
                                                <Value Type=""{1}"">{2}</Value>
                                            </Eq>
                                        </Where>", relatedField.InternalName, value.GetType(), value);

            SPListItemCollection items = relatedList.GetItems(q);

            if (items.Count > 0)
            {
                Console.WriteLine("\n{0} has {1} related items:", relatedList.Title, items.Count);
                foreach (SPListItem item in items)
                    Console.WriteLine(item.DisplayName);
            }
        }
    }
}
Show:
© 2014 Microsoft