SharePoint Online: Add Lookup Column to List using PowerShell

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

How to Create a 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. Adding a lookup column using PowerShell is a powerful way to automate and manage your SharePoint list columns more efficiently. By leveraging the CSOM or PnP PowerShell module, we can create the lookup column with the desired properties. This method provides an effective and scriptable solution for managing SharePoint columns, where manual configuration through the user interface might be cumbersome or time-consuming.

To add a lookup field, follow these steps:

  1. Browse to your SharePoint Online site and navigate to the target list where you want to add a lookup column.
  2. Under the List tab, click on the “Create Column” button in the ribbon.
  3. Provide the Name to your new column, specify the type as “Lookup” 
    Add Lookup column to SharePoint Online List using PowerShell
  4. 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 the SharePoint Online list.

PowerShell to Add Lookup Column to List in SharePoint Online:

Lookup columns are helpful when you want to associate a list with another list or when you want to look up values from another list. Let’s see how to add a lookup column to a list in SharePoint Online using PowerShell.

#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

Lookup columns allow you to create relationships between lists in SharePoint by linking one list’s item to another list’s item. Let me show you how to quickly add a lookup column to a SharePoint list using PnP PowerShell.

#Parameters 
$SiteURL = "https://crescent.sharepoint.com/sites/PMO"
$ListName= "Projects"
$DisplayName = "Parent Project"
$InternalName = "ParentProject"

$ParentListName="Parent Projects"
$LookupField = "Title" #Internal Name

#Connect to the site
Connect-PnPOnline -Url $SiteURL -Interactive        

#Add Lookup Field to the List    
Add-PnPField -List $ListName -Type Lookup -DisplayName $DisplayName -InternalName $InternalName -AddToDefaultView

#Set Field Properties
Set-PnPField -List $ListName -Identity $InternalName -Values @{LookupList=(Get-PnPList $ParentListName).Id.ToString(); LookupField=$LookupField}

We can also add a lookup column with the Field schema XML:

#Config Variables
$SiteURL = "https://Crescent.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 - Information Technology Expert with Two-decades of hands-on experience, specializing in SharePoint, PowerShell, Microsoft 365, and related products. He has held various positions including SharePoint Architect, Administrator, Developer and consultant, has helped many organizations to implement and optimize SharePoint solutions. Known for his deep technical expertise, He's passionate about sharing the knowledge and insights to help others, through the real-world articles!

Leave a Reply

Your email address will not be published. Required fields are marked *