SPList.GetRelatedFields Method

Returns a collection of objects with information about a lookup field in another list that points to a field in this list.

Namespace:  Microsoft.SharePoint
Assembly:  Microsoft.SharePoint (in Microsoft.SharePoint.dll)
Available in Sandboxed Solutions: Yes
Available in SharePoint Online

Syntax

'Declaration
<ClientCallableExceptionConstraintAttribute(FixedId := "accessdenied", Condition := "User does not have permissions to view this list.",  _
    ErrorType := GetType(UnauthorizedAccessException))> _
<ClientCallableMethodAttribute> _
Public Function GetRelatedFields As SPRelatedFieldCollection
'Usage
Dim instance As SPList
Dim returnValue As SPRelatedFieldCollection

returnValue = instance.GetRelatedFields()
[ClientCallableExceptionConstraintAttribute(FixedId = "accessdenied", Condition = "User does not have permissions to view this list.", 
    ErrorType = typeof(UnauthorizedAccessException))]
[ClientCallableMethodAttribute]
public SPRelatedFieldCollection GetRelatedFields()

Return Value

Type: Microsoft.SharePoint.SPRelatedFieldCollection
A collection of SPRelatedField objects.

Remarks

In Microsoft SharePoint Foundation two lists become related when a user creates a lookup field on one list that takes its value from a field on another list. You can think of the list that provides data as the parent and the list that looks up data as the child. You can discover which fields in child lists depend on information in a parent list by calling the GetRelatedFields method on an SPList object that represents the parent list. The method returns a collection of SPRelatedField objects with the following properties:

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

  • ListId. Gets the ID of the child list.

  • WebId. Gets the ID of the Web site where the child list is located.

  • RelationshipDeleteBehavior. Gets the data integrity constraint defined for the relationship.

The last property, RelationshipDeleteBehavior, represents an important aspect of list relationships. A user who creates a lookup field, either through the user interface or through the object model, can set constraints on how deletions from one list affect deletions from the other. For example, the user can specify that deletions from the parent list cascade to the child; that is, when an item is deleted from the parent list, all related items on the child list are also deleted. The user can also specify that deletions from the parent list are restricted; an item cannot be deleted from the parent list if related items exist on the child list. For more information, see the SPRelationshipDeleteBehavior enumeration.

Note

In order to restrict deletions, the user must have SPBasePermissions.ManageLists permission on the parent list.

In SharePoint Foundation a user can also create a multiple-column lookup. In this case, the child list has a primary column that defines the relationship with the parent list, and it has one or more secondary columns that read values from fields on the parent list but depend on the primary column for the list relationship. SPRelatedField objects contain information about only the primary column. To get information about secondary columns, call the GetDependentLookupInternalNames method of the SPFieldLookup object that represents the primary column. For more information, see the GetDependentLookupInternalNames() method.

Examples

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("https://localhost"))
            {
                using (SPWeb site = 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, SPWeb site, 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);
            }
        }
    }
}
Imports System
Imports Microsoft.SharePoint

Module ConsoleApp

    Sub Main()
        Using siteCollection As New SPSite("https://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

See Also

Reference

SPList Class

SPList Members

GetRelatedFields Overload

Microsoft.SharePoint Namespace

SPRelatedField

SPFieldLookup

SPRelationshipDeleteBehavior