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 |