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?

The Lookup column in SharePoint Online lets you populate the column value based on another column’s value from a different list on the same site. To add a lookup field:

  • Browse to your SharePoint Online site and navigate to the target list in which you want to add a lookup column.
  • Under the List tab, click on the “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 a 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()
{ 
    param
    (
        [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=[string]::Empty,
        [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)
        $Ctx.Load($Web)
        $Ctx.Load($List)
        $Ctx.Load($LookupList)
        $Ctx.ExecuteQuery()

        #Check if the column exists in list already
        $Fields = $List.Fields
        $Ctx.Load($Fields)
        $Ctx.executeQuery()
        $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
        }
        else
        {
            #Get IDs of Lookup List and Web
            $LookupListID= $LookupList.id
            $LookupWebID=$web.Id
            
            #sharepoint online powershell create lookup field
            $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)
            $Ctx.ExecuteQuery()    

            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
$SiteURL="https://crescent.sharepoint.com"
$ListName="Project Milestone"
$Name="ParentProject"
$DisplayName="Parent Project"
$Description="Select the Parent Project from the List"
$LookupListName="Projects" #Parent List to Lookup
$LookupField="Title"

#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 attribute Mult=’TRUE’ to the $FieldSchema variable !

PnP PowerShell to Add Lookup Column to SharePoint Online List

#Config Variables
$SiteURL = "https://crescenttech.sharepoint.com"
$ListName= "Projects"
$ParentListName="Parent Projects"

#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Credentials (Get-Credential)

#Get the ID of the Parent List
$LookupListID = (Get-PnPList -Identity $ParentListName).ID

#Define XML Schema for URL Field
$FieldXML= "<Field Type='Lookup' Name='ParentProject' ID='$([GUID]::NewGuid())' DisplayName='Parent Project' List='$LookupListID' ShowField='Title'></Field>"

#Add Lookup Field to list
Add-PnPFieldFromXml -FieldXml $FieldXML -List $ListName

Salaudeen Rajack

Salaudeen Rajack is a SharePoint Architect with Two decades of SharePoint Experience. He loves sharing his knowledge and experiences with the SharePoint community, through his real-world articles!

Leave a Reply