SPFieldLookup.CountRelated Property

Gets or sets a Boolean value that specifies whether to display the count of items in the lookup list that look up to the current list item.

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

Syntax

'Declaration
Public Overridable Property CountRelated As Boolean
    Get
    Set
'Usage
Dim instance As SPFieldLookup
Dim value As Boolean

value = instance.CountRelated

instance.CountRelated = value

public virtual bool CountRelated { get; set; }

Property Value

Type: System.Boolean
true to return the number of items related to the current item; otherwise, false.

Remarks

Count-related Lookup fields are a variant of Lookup fields that perform a reverse lookup and return the count of items on the target list that look up to an item on the current list.

When you set the CountRelated property to true, you should configure the current Lookup field so that it points to another Lookup field on the target list. Do this by setting the LookupList property so that it identifies the target list and the LookupField property so that it specifies the internal name of an SPFieldLookup object on the target list. The count-related Lookup field then has a computed value equal to the number of items in the target list that are related to the current list item.

For example, suppose you have two lists, Customers and Orders. You want items on the Orders list to show who has placed an order, so you add a Customer ID Lookup field to the Orders list and configure it to point to the ID field on the Customers list. You also decide that when you look at the Customers list you want to be able to see at a glance how many orders each customer has. To make this possible, you add a count-related Orders Lookup field to the Customers list and configure it to point to the Customer ID Lookup field on the Orders list. The Orders field on the Customers list then displays the number of orders that each customer has placed.

Examples

The following example is a console application that works with two lists, Customers and Orders. The goal is to enable a user who views an item in the Customers list to see at a glance how many items that customer has in the Orders list.

The application begins by linking the two lists. It does this by creating a Customer ID lookup field in the Orders list that points to the ID field in the Customers list. Then the application creates an Orders field in the Customers list, points it to the Customer ID field in the Orders list, and sets the new field's CountRelated property to true.

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.OpenWeb())
                {
                    SPList customers = web.Lists.TryGetList("Customers");
                    SPList orders = web.Lists.TryGetList("Orders");

                    if (null != customers && null != orders)
                    {
                        SPField idField = customers.Fields.TryGetFieldByStaticName("ID");
                        if (null != idField)
                        {
                            // Create a Customer ID field on the Orders list. 
                            // Point it to the ID field on the Customers list.
                            string customerIdName = orders.Fields.AddLookup("Customer ID", customers.ID, true);
                            SPFieldLookup customerIdField = 
                                (SPFieldLookup)orders.Fields.GetFieldByInternalName(customerIdName);
                            customerIdField.LookupField = idField.InternalName;
                            customerIdField.Update();

                            // Add the field to the default view.
                            AddToDefaultView(orders, customerIdName);

                            // Create an Orders field on the Customers list.
                            // Point it to the Customer ID field on the Orders list.
                            string numOrdersName = customers.Fields.AddLookup("Orders", orders.ID, false);
                            SPFieldLookup numOrdersField = 
                                (SPFieldLookup)customers.Fields.GetFieldByInternalName(numOrdersName);
                            numOrdersField.LookupField = customerIdField.InternalName;
                            numOrdersField.CountRelated = true;
                            numOrdersField.Update();

                            // Add the field to the default view.
                            AddToDefaultView(customers, numOrdersName);
                        }
                    }
                }
            }
            Console.Write("\nPress ENTER to continue...");
            Console.ReadLine();
        }

        static void AddToDefaultView(SPList list, string fieldName)
        {
            if (list != null && list.Fields.ContainsField(fieldName) && !list.DefaultView.ViewFields.Exists(fieldName))
            {
                SPView defaultView = list.DefaultView;
                defaultView.ViewFields.Add(fieldName);
                defaultView.Update();
            }
        }
    }
}

Imports System
Imports Microsoft.SharePoint

Module ConsoleApp

    Sub Main()
        Using site As New SPSite("https://localhost")
            Using web As SPWeb = site.OpenWeb()

                Dim customers As SPList = web.Lists.TryGetList("Customers")
                Dim orders As SPList = web.Lists.TryGetList("Orders")

                If customers IsNot Nothing AndAlso orders IsNot Nothing Then
                    Dim idField As SPField = customers.Fields.TryGetFieldByStaticName("ID")
                    If idField IsNot Nothing Then
                        ' Create a Customer ID field on the Orders list. 
                        ' Point it to the ID field on the Customers list.
                        Dim customerIdName As String = orders.Fields.AddLookup("Customer ID", customers.ID, True)
                        Dim customerIdField As SPFieldLookup = _
                           DirectCast(orders.Fields.GetFieldByInternalName(customerIdName), SPFieldLookup)
                        customerIdField.LookupField = idField.InternalName
                        customerIdField.Update()

                        ' Add the field to the default view.
                        AddToDefaultView(orders, customerIdName)

                        ' Create an Orders field on the Customers list.
                        ' Point it to the Customer ID field on the Orders list.
                        Dim numOrdersName As String = customers.Fields.AddLookup("Orders", orders.ID, False)
                        Dim numOrdersField As SPFieldLookup = _
                           DirectCast(customers.Fields.GetFieldByInternalName(numOrdersName), SPFieldLookup)
                        numOrdersField.LookupField = customerIdField.InternalName
                        numOrdersField.CountRelated = True
                        numOrdersField.Update()

                        ' Add the field to the default view.
                        AddToDefaultView(customers, numOrdersName)
                    End If
                End If
            End Using
        End Using
        Console.Write(vbLf & "Press ENTER to continue...")
        Console.ReadLine()
    End Sub

    Sub AddToDefaultView(ByVal list As SPList, ByVal fieldName As String)
        If list IsNot Nothing AndAlso list.Fields.ContainsField(fieldName) _
            AndAlso Not list.DefaultView.ViewFields.Exists(fieldName) Then

            Dim defaultView As SPView = list.DefaultView
            defaultView.ViewFields.Add(fieldName)
            defaultView.Update()

        End If
    End Sub

End Module

See Also

Reference

SPFieldLookup Class

SPFieldLookup Members

Microsoft.SharePoint Namespace