SPFieldLookup class

Represents a lookup field.

System.Object
  Microsoft.SharePoint.SPField
    Microsoft.SharePoint.SPFieldLookup
      Microsoft.SharePoint.Applications.GroupBoard.SPFieldFacilities
      Microsoft.SharePoint.SPFieldUser

Namespace:  Microsoft.SharePoint
Assembly:  Microsoft.SharePoint (in Microsoft.SharePoint.dll)

'Declaration
Public Class SPFieldLookup _
    Inherits SPField
'Usage
Dim instance As SPFieldLookup

public class SPFieldLookup : SPField

A lookup field takes its value from a field in another list. The list that provides data for the lookup field is called the lookup list. To get a reference to the lookup list, access the SPFieldLookup object's LookupList property. To determine which field in the lookup list is providing information, access the LookupField property.

Although the SPFieldLookup class does have a constructor, it is often more convenient to call the AddLookup method of the SPFieldCollection class. The AddLookup method has an overload that you can use to create a lookup to a list in a different Web site from the one where you are creating the SPFieldLookup object.

Microsoft SharePoint Foundation supports multiple-column lookups. In this case, a primary column establishes the relationship with the lookup list by pointing to a field in the target list, and one or more secondary columns point to other fields in the target list. You can create the lookup field for a secondary column by calling the AddDependentLookup method of the SPFieldCollection class. For a secondary column, the IsDependentLookup property returns true. For a primary column, IsDependentLookup returns false and the IsRelationship property returns true.

SharePoint Foundation supports referential integrity for list items by making it possible for lookup fields to set deletion constraints on the lookup list. You do this through the RelationshipDeleteBehavior property, which takes one of the following SPRelationshipDeleteBehavior enumeration values:

• Cascade. Deleting an item from the lookup list causes all related items to be deleted from the list that contains the lookup field.

• Restrict. Prevents an item on the lookup list from being deleted if related items exist on the list that contains the lookup field.

• None. No constraint (the default).

In order to specify either Cascade or Restrict, the user must have SPBasePermissions.ManageLists permission on the lookup list. In addition, both Cascade and Restrict require that the lookup field be indexed. Before setting the RelationshipDeleteBehavior property to either of these values, first set the Indexed property to true.

In SharePoint Foundation, lookup fields in a related list are discoverable from the lookup list (the target of the lookup) through the GetRelatedFields() method of the SPList class. The GetRelatedFields method has an overload that allows you to filter for lookup fields that specify a particular deletion constraint.

Lookup Field Values

By default, a lookup field contains just one value. In this case, the field value is an object of type String, and the string has the following format:

Id;#Value

Id is the identifier for the list item that the lookup field points to. Value is the value of the field that the lookup field points to. The two parts of the string are delimited by the characters ";#". If Value contains a semicolon (";"), it is escaped with a semicolon (";;").

For example, if the lookup field points to the third list item and the value of the field that the lookup field points to is "I gnaw on old tires; it strengthens my jaw", then the following string is returned:

3;#I gnaw on old tires;;it strengthens my jaw

You can parse this string yourself, or you can pass it as an argument to the constructor for an SPFieldLookupValue object. You can get the list item identifier from the LookupId property of an SPFieldLookupValue object, and the value of the field in the list item from the LookupValue property.

A lookup field can have multiple values if the AllowMultipleValues property returns true. In this case, the field value is an object of type SPFieldLookupValueCollection that is boxed as Object. You can get all the values by first casting the raw field value to SPFieldLookupValueCollection and then enumerating the SPFieldLookupValue objects in the collection.

Notes to inheritors

In SharePoint Foundation, columns based on custom field types are read-only in Datasheet view.

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("https://localhost"))
            {
                using (SPWeb site = 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();
        }
    }
}

Imports System
Imports Microsoft.SharePoint

Module ConsoleApp

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

The next example demonstrates how to interpret the value of a lookup field.

The example code looks for an Issues list in the root Web site of a site collection. The Issues list type is chosen here because it has several lookup fields. You could modify the example by selecting another list type. Having found a list, the example selects the first lookup field that it finds in the list's fields collection. Then the code iterates through list items, printing information about the value of each item's lookup field.

using System;
using Microsoft.SharePoint;

namespace ConsoleApp
{
    class Program
    {
        static void Main(string[] args)
        {
            using (SPSite site = new SPSite("https://localhost"))
            {
                using (SPWeb web = site.RootWeb)
                {
                    SPList list = null;
                    foreach (SPList webList in web.Lists)
                    {
                        if (webList.BaseType == SPBaseType.Issue)
                        {
                            list = webList;
                            break;
                        }
                    }
                    if (list != null)
                    {
                        Guid fieldId = Guid.Empty;
                        foreach (SPField field in list.Fields)
                        {
                            if (field is SPFieldLookup)
                            {
                                fieldId = field.Id;
                                break;
                            }
                        }

                        if (fieldId != Guid.Empty)
                        {
                            SPFieldLookup lookupField = list.Fields[fieldId] as SPFieldLookup;
                            Console.WriteLine("Lookup field: {0}", lookupField.Title);

                            foreach (SPListItem item in list.Items)
                            {
                                object rawValue = item[fieldId];
                                if (rawValue != null)
                                {
                                    Console.WriteLine("\nItem: {0}. {1}", item.ID, item.Title);
                                    Console.WriteLine("Raw value: {0}", rawValue);

                                    if (lookupField.AllowMultipleValues)
                                    {
                                        SPFieldLookupValueCollection values = item[fieldId] as SPFieldLookupValueCollection;
                                        foreach (SPFieldLookupValue value in values)
                                        {
                                            PrintValue(value);
                                        }
                                    }
                                    else
                                    {
                                        SPFieldLookupValue value = new SPFieldLookupValue(rawValue.ToString());
                                        PrintValue(value);
                                    }
                                }
                            }
                        }
                    }
                }
            }
            Console.Write("\nPress ENTER to continue...");
            Console.ReadLine();
        }

        public static void PrintValue(SPFieldLookupValue value)
        {
            Console.WriteLine("Lookup item ID: {0}", value.LookupId);
            Console.WriteLine("Lookup item value: {0}", value.LookupValue);
        }
    }
}

Imports System
Imports Microsoft.SharePoint

Module ConsoleApp

    Sub Main(ByVal args As String())
        Using site As New SPSite("https://localhost")
            Using web As SPWeb = site.RootWeb
                Dim list As SPList = Nothing
                For Each webList As SPList In web.Lists
                    If webList.BaseType = SPBaseType.Issue Then
                        list = webList
                        Exit For
                    End If
                Next
                If list <> Nothing Then
                    Dim fieldId As Guid = Guid.Empty
                    For Each field As SPField In list.Fields
                        If TypeOf field Is SPFieldLookup Then
                            fieldId = field.Id
                            Exit For
                        End If
                    Next

                    If fieldId <> Guid.Empty Then
                        Dim lookupField As SPFieldLookup = TryCast(list.Fields(fieldId), SPFieldLookup)
                        Console.WriteLine("Lookup field: {0}", lookupField.Title)

                        For Each item As SPListItem In list.Items
                            Dim rawValue As Object = item(fieldId)
                            If rawValue <> Nothing Then
                                Console.WriteLine(vbLf & "Item: {0}. {1}", item.ID, item.Title)
                                Console.WriteLine("Raw value: {0}", rawValue)

                                If lookupField.AllowMultipleValues Then
                                    Dim values As SPFieldLookupValueCollection = TryCast(item(fieldId), _
                                        SPFieldLookupValueCollection)
                                    For Each value As SPFieldLookupValue In values
                                        PrintValue(value)
                                    Next
                                Else
                                    Dim value As New SPFieldLookupValue(rawValue.ToString())
                                    PrintValue(value)
                                End If
                            End If
                        Next
                    End If
                End If
            End Using
        End Using
        Console.Write(vbLf & "Press ENTER to continue...")
        Console.ReadLine()
    End Sub

    Sub PrintValue(ByVal value As SPFieldLookupValue)
        Console.WriteLine("Lookup item ID: {0}", value.LookupId)
        Console.WriteLine("Lookup item value: {0}", value.LookupValue)
    End Sub

End Module

Any public static (Shared in Visual Basic) members of this type are thread safe. Any instance members are not guaranteed to be thread safe.

SPFieldLookup members

Microsoft.SharePoint namespace

GetRelatedFields()

SPRelatedField

SPRelationshipDeleteBehavior