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)

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

value = instance.CountRelated

instance.CountRelated = value

Property value

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

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.

NoteNote

When you set the CountRelated property to true, the ReadOnlyField and AllowDeletion properties are automatically set to true as well.

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.

Imports System
Imports Microsoft.SharePoint

Module ConsoleApp

    Sub Main()
        Using site As New SPSite("http://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
Show: