Projected Fields in SharePoint 2010


In this article we will be seeing about projected fields in SharePoint 2010.

Introduction:

SharePoint 2010 enables you to display additional columns from the target list in the view of the list that contains the lookup column. These additional generated columns are known as projected fields. I have created two lists "Departments" and "Employees to model the relationship between them. I have created a Department lookup column for the Employees list and I also want to display the name of the department manager in the list of employees, as shown in the following illustration.

Sharepro1.gif

Departments List:

Sharepro2.gif

Employees List:

In the "Employees" list I have created a Department lookup column as shown in the following

Sharepro3.gif

Sharepro4.gif

You could be able to see the projected field "Department: Manager" as shown in the following

Sharepro5.gif

Using SharePoint Object Model:

  • Open Visual Studio 2010.
  • Go to File => New => Project.
  • Select Console Application template from the installed templates.
  • Enter the Name and click Ok.
  • Add the following assembly.

    • Microsoft.SharePoint.dll
     
  • Add the following namespace.

    • Using Microsoft.SharePoint ;
     
  • Program.cs looks like the following.

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

    namespace ProjectedFields
    {
        class Program
        {
            static void Main(string[] args)
            {
                using (SPSite site = new SPSite("http://serverName:22222/sites/test"))
                {
                    using (SPWeb web = site.RootWeb)
                    {
                        SPList lookupList = web.Lists.TryGetList("Departments");
                        SPList list = web.Lists.TryGetList("Employees");

                        if (lookupList != null && list != null)
                        {
                            // Create the lookup column.
                            string strPrimaryColumn = list.Fields.AddLookup("Department", lookupList.ID, true);
                            SPFieldLookup primaryColumn = (SPFieldLookup)list.Fields.GetFieldByInternalName(strPrimaryColumn);
                            primaryColumn.LookupField = lookupList.Fields["Name"].InternalName;
                            primaryColumn.Indexed = true;
                            primaryColumn.RelationshipDeleteBehavior = SPRelationshipDeleteBehavior.Restrict;
                            primaryColumn.Update();

                            // Projected Field
                            string strProjectedCol = list.Fields.AddDependentLookup("LookUp:Manager", primaryColumn.Id);
                            SPFieldLookup projectedCol = (SPFieldLookup)list.Fields.GetFieldByInternalName(strProjectedCol);
                            projectedCol.LookupField = lookupList.Fields["Manager"].InternalName;
                            projectedCol.Update();

                            // Add the columns to the "All Items" view
                            SPView view = list.Views["All Items"];
                            view.ViewFields.Add(primaryColumn);
                            view.ViewFields.Add(projectedCol);
                            view.Update();
                        }
                    }
                }
            }
        }
    }  

Using powershell:

  • Go to Start => All Programs => Microsoft SharePoint 2010 products => SharePoint 2010 Management Shell.
  • Run as an administrator.
  • Run the following script.

    $site=Get-SPSite "http://serverName:22222/sites/test"
    $web=$site.Rootweb
    $lookupList=$web.Lists.TryGetList("Departments")
    $list = $web.Lists.TryGetList("Employees")

    #------------------------- Create a lookup column----------------------------------------------------------

    [String]$strPrimaryColumn = $list.Fields.AddLookup("Department", $lookupList.ID, $true)
    $primaryColumn = [Microsoft.SharePoint.SPFieldLookup]$list.Fields.GetFieldByInternalName($strPrimaryColumn)
    $primaryColumn.LookupField = $lookupList.Fields["Name"].InternalName
    $primaryColumn.Indexed = $true
    $primaryColumn.RelationshipDeleteBehavior = [Microsoft.SharePoint.SPRelationshipDeleteBehavior]::Restrict
    $primaryColumn.Update()

    #------------------------- Projected field----------------------------------------------------------

    $strProjectedCol = $list.Fields.AddDependentLookup("LookUp:Manager", $primaryColumn.Id)
    $projectedCol = [Microsoft.SharePoint.SPFieldLookup]$list.Fields.GetFieldByInternalName($strProjectedCol)
    $projectedCol.LookupField = $lookupList.Fields["Manager"].InternalName
    $projectedCol.Update()

    #------------------------- Add the columns to the view----------------------------------------------------------

    $view = $list.Views["All Items"]
    $view.ViewFields.Add($primaryColumn)
    $view.ViewFields.Add($projectedCol)
    $view.Update()