Working with Lookup Fields in SharePoint Lists using PnP PowerShell

Lookup fields in SharePoint allow you to create relationships between two lists by referencing data from one list in another. Using PnP PowerShell, you can efficiently create, retrieve, update, and delete lookup fields in SharePoint Online.

What You’ll Learn:

✔️ How to connect to SharePoint Online
✔️ How to create a lookup field in a list
✔️ How to retrieve lookup field values
✔️ How to update lookup field values
✔️ How to delete a lookup field

Prerequisites

Before working with PnP PowerShell, ensure that:
PnP PowerShell is installed
You have the necessary SharePoint permissions
You know the SharePoint site URL

Step 1: Install and Import PnP PowerShell

If you haven’t installed PnP PowerShell, install it using:

Install-Module -Name PnP.PowerShell -Scope CurrentUser -AllowClobber -Force

Then, import the module:

Import-Module PnP.PowerShell

PnP PowerShell is ready!

Step 2: Connect to SharePoint Online

Before managing lookup fields, connect to SharePoint Online:

# Connect to SharePoint Online
Connect-PnPOnline -Url "https://yourtenant.sharepoint.com/sites/yoursite" -Interactive

🔹 Replace "yourtenant" with your tenant name
🔹 Replace "yoursite" with the site name

Connection successful!

Step 3: Create a Lookup Field in a SharePoint List

To create a lookup field, you need:
1️⃣ A source list (e.g., Departments)
2️⃣ A target list where the lookup field will be added (e.g., Employees)

PowerShell Script to Add a Lookup Field

# Define list names
$sourceList = "Departments"
$targetList = "Employees"
$fieldName = "Department"

# Get the source list ID
$sourceListId = (Get-PnPList -Identity $sourceList).Id

# Add lookup field
Add-PnPField -List $targetList -DisplayName $fieldName -InternalName "DepartmentLookup" -Type Lookup -LookupList $sourceListId -LookupField "Title"

🔹 This script adds a lookup field in the Employees list, linking to the Title field of the Departments list.

Lookup field created successfully!

Step 4: Retrieve Lookup Field Values

You can fetch lookup values from the target list using:

# Get all employees with their department names
Get-PnPListItem -List "Employees" | Select-Object Title, @{Name="Department";Expression={$_["DepartmentLookup"].LookupValue}}

This script retrieves employee names and their associated department names from the lookup field.

Lookup values retrieved!

Step 5: Update Lookup Field Values

To update a lookup field, use the ID of the source list item.

# Update an employee's department
Set-PnPListItem -List "Employees" -Identity 3 -Values @{"DepartmentLookup"=5}

This script updates Employee ID 3 with Department ID 5.

Lookup field updated!

Step 6: Delete a Lookup Field

To remove a lookup field from a list, use:

# Remove lookup field from Employees list
Remove-PnPField -List "Employees" -Identity "DepartmentLookup" -Force

🔹 This deletes the DepartmentLookup field from the Employees list.

Lookup field deleted!

Common Errors & Solutions

Error	Cause	Solution
`Access Denied`	Insufficient permissions	Ensure you have Full Control on both lists
`Cannot find list`	Wrong list name	Use `Get-PnPList` to verify the list name
`Cannot find field`	Lookup field doesn’t exist	Use `Get-PnPField -List "Employees"` to check field names