Friday, August 26, 2016

SharePoint Online: Add Lookup Column to List using PowerShell

Requirement: Create new Lookup Column to a SharePoint Online List.

How to Create Lookup Column in SharePoint Online List?

  • Browse to your SharePoint Online site and Navigate to the target list in which you want to add lookup column.
  • Under the List tab, click on "Create Column" button in the ribbon.
  • Provide the Name to your new column, specify the type as "Lookup" 
    Add Lookup column to SharePoint Online List using PowerShell
  • Select the source list and field for lookup, Specify other optional values such as column description, Required field value, etc. and Click on "OK" to create lookup field in SharePoint Online list.

PowerShell to Add Lookup Column to List in SharePoint Online:
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

#Custom function to add column to list
Function Add-LookupColumnToList()
        [Parameter(Mandatory=$true)] [string] $SiteURL,
        [Parameter(Mandatory=$true)] [string] $ListName,
        [Parameter(Mandatory=$true)] [string] $Name,
        [Parameter(Mandatory=$true)] [string] $DisplayName,
        [Parameter(Mandatory=$false)] [string] $Description="",
        [Parameter(Mandatory=$false)] [string] $IsRequired = "FALSE",
        [Parameter(Mandatory=$false)] [string] $EnforceUniqueValues = "FALSE",
        [Parameter(Mandatory=$true)] [string] $LookupListName,
        [Parameter(Mandatory=$true)] [string] $LookupField

    #Generate new GUID for Field ID
    $FieldID = New-Guid

    Try {
        $Cred= Get-Credential
        $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

        #Setup the context
        $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
        $Ctx.Credentials = $Credentials
        #Get the web, List and Lookup list
        $Web = $Ctx.web
        $List = $Web.Lists.GetByTitle($ListName)
        $LookupList = $Web.Lists.GetByTitle($LookupListName)

        #Check if the column exists in list already
        $Fields = $List.Fields
        $NewField = $Fields | where { ($_.Internalname -eq $Name) -or ($_.Title -eq $DisplayName) }
        if($NewField -ne $NULL)  
            Write-host "Column $Name already exists in the List!" -f Yellow
            #Get IDs of Lookup List and Web
            $LookupListID= $

            $FieldSchema = "<Field Type='Lookup' ID='{$FieldID}' DisplayName='$DisplayName' Name='$Name' Description='$Description' Required='$IsRequired' EnforceUniqueValues='$EnforceUniqueValues' List='$LookupListID' WebId='$LookupWebID' ShowField='$LookupField' />"
            $NewField = $List.Fields.AddFieldAsXml($FieldSchema,$True,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldInternalNameHint)

            Write-host "New Column Added to the List Successfully!" -ForegroundColor Green  
    Catch {
        write-host -f Red "Error Adding Column to List!" $_.Exception.Message

#Set parameter values
$ListName="Project Milestone"
$DisplayName="Parent Project"
$Description="Select the Parent Project from the List"
$LookupListName="Projects" #Parent List to Lookup

#Call the function to add column to list
Add-LookupColumnToList -SiteURL $SiteURL -ListName $ListName -Name $Name -DisplayName $DisplayName -Description $Description -LookupListName $LookupListName -LookupField $LookupField
Tips: For Multi-Lookup Field, simply change the Field type from "Lookup" to "LookupMulti" and add an another attribute Mult='TRUE' to the $FieldSchema variable !

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.

Check out these SharePoint products:

No comments :

Post a Comment

Please Login and comment to get your questions answered!

You might also like:

Related Posts Plugin for WordPress, Blogger...