SharePoint Online：PowerShell 更新查找字段值

要求：使用 SharePoint Online 中的 PowerShell 更新查找字段。

我们有一个名为“父项目”的父列表和一个名为“项目里程碑”的子列表。子列表中的“父项目名称”字段是从父列表的“项目名称”字段中查找的。下面是我的 PowerShell 脚本，用于在 SharePoint Online 列表中设置查找字段值。

SharePoint Online PowerShell 更新查找字段

要更新 SharePoint Online 列表中的查找列值，请使用以下 PowerShell 示例：

#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

#Function to get the ID from Parent Lookup List -  Based on the Provided value
Function Get-ParentLookupID($ParentListName, $ParentListLookupField, $ParentListLookupValue)
{
    $ParentList = $Ctx.Web.lists.GetByTitle($ParentListName)

    #Get the Parent List Item Filtered by given Lookup Value
    $Query = New-Object Microsoft.SharePoint.Client.CamlQuery
    $Query.ViewXml="<View Scope='RecursiveAll'><Query><Where><Eq><FieldRef Name='$($ParentListLookupField)'/><Value Type='Text'>$($ParentListLookupValue)</Value></Eq></Where></Query></View>"
    $ListItems = $ParentList.GetItems($Query)
    $Ctx.Load($ListItems)
    $Ctx.ExecuteQuery()

    #Get the ID of the List Item
    If($ListItems.count -gt 0)
    {
        Return $ListItems[0].ID #Get the first item - If there are more than One
    }
    else
    {
        Return $Null
    }
}

#Set parameter values
$SiteURL="https://crescent.sharepoint.com/sites/pmo"
$ListItemID="25"
$ParentListName= "Parent Projects" #Lookup Parent List
$ChildListName="Project Milestones" #List to add new lookup value
$ParentListLookupField= "ProjectName" #Internal Name
$ChildListLookupField ="ParentProject" #Internal Name
$ParentListLookupValue="Cloud Development" #Parent Project value

#Get Credentials to connect
$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 Child List & Item to update
$ChildList = $Ctx.Web.lists.GetByTitle($ChildListName) 
$ListItem = $ChildList.GetItemById($ListItemID)
$Ctx.Load($ListItem)
$Ctx.ExecuteQuery()

#Get the Lookup ID from the Parent List
$LookupID = Get-ParentLookupID $ParentListName $ParentListLookupField $ParentListLookupValue

#Check if the given Lookup Item found in the Parent List
If($LookupID -ne $Null)
{
    #Update Lookup Field using Parent Lookup Item ID
    $ListItem[$ChildListLookupField] = $LookupID
    $ListItem.Update()
    $Ctx.ExecuteQuery()

    Write-host -f Green "Lookup Field value has been updated!"
}
else
{
    write-host -f Yellow "Lookup Item '$($ParentListLookupValue)' doesn't exist in the Parent List!"
} 

PowerShell 更新多值查找列值

以下是在 SharePoint Online 中设置多值查找列的 PowerShell 示例：

#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

#Function to get the ID from Parent Lookup List -  Based on the Provided value
Function Get-ParentLookupID($ParentListName, $ParentListLookupField, $ParentListLookupValue)
{
    $ParentList = $Ctx.Web.lists.GetByTitle($ParentListName)

    #Get the Parent List Item Filtered by given Lookup Value
    $Query = New-Object Microsoft.SharePoint.Client.CamlQuery
    $Query.ViewXml="<View Scope='RecursiveAll'><Query><Where><Eq><FieldRef Name='$($ParentListLookupField)'/><Value Type='Text'>$($ParentListLookupValue)</Value></Eq></Where></Query></View>"
    $ListItems = $ParentList.GetItems($Query)
    $Ctx.Load($ListItems)
    $Ctx.ExecuteQuery()

    #Get the ID of the List Item
    If($ListItems.count -gt 0)
    {
        Return $ListItems[0].ID #Get the first item - If there are more than One
    }
    else
    {
        Return $Null
    }
}

#Set parameter values
$SiteURL="https://crescent.sharepoint.com/sites/pmo"
$ListItemID="25"
$ParentListName= "Parent Projects" #Lookup Parent List
$ChildListName="Project Milestones" #List to add new lookup value
$ParentListLookupField= "ProjectName"
$ChildListLookupField ="ParentProject"
$ParentListLookupValues=@("Address Books", "Holidays", "Invoices") #Parent Project value

#Get Credentials to connect
$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 Child List & Item to update
$ChildList = $Ctx.Web.lists.GetByTitle($ChildListName) 
$ListItem = $ChildList.GetItemById($ListItemID)
$Ctx.Load($ListItem)
$Ctx.ExecuteQuery()

$LookupValueCollection = @()
ForEach($ParentListLookupValue in $ParentListLookupValues)
{
    #Get the Lookup ID from the Parent List
    $LookupID = Get-ParentLookupID $ParentListName $ParentListLookupField $ParentListLookupValue

    #Check if the given Lookup Item found in the Parent List
    If($LookupID -ne $Null)
    {
        $lookupValue = New-Object Microsoft.SharePoint.Client.FieldLookupValue
        $lookupValue.LookupId = $LookupID
        $LookupValueCollection += $LookupValue
    }
    else
    {
        write-host -f Yellow "Lookup Item '$($ParentListLookupValue)' doesn't exist in the Parent List!"
    }
}

#sharepoint online PowerShell update lookup field
If($LookupValueCollection.length -gt 0)
{
    #Convert the array to Lookup value collection
    $LookupValueColl = [Microsoft.SharePoint.Client.FieldLookupValue[]]$LookupValueCollection 

    #update lookup field using powershell in sharepoint online
    $ListItem[$ChildListLookupField] = $LookupValueColl
    $ListItem.Update()
    $Ctx.ExecuteQuery()
    Write-host -f Green "Multi-Lookup Field value has been updated!"
}

PnP PowerShell 在 SharePoint Online 中设置查找字段值：

查找字段内部存储父查找项的 ID。要更新 SharePoint Online 中的查找字段值，请使用 Values @{“Lookup”=“1”}，其中“1”是父查找项的 ID。

#Config Variables
$SiteURL = "https://Crescent.sharepoint.com/sites/marketing"
$ListName = "ProjectMilestones"
$FieldName = "Parent_x0020_Project"
$ItemID = 1
 
#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Interactive
 
#Update lookup field value using PnP PowerShell
Set-PnPListItem -List $ListName -Identity $ItemID -Values @{$FieldName = 3}

这里“3”是父查找项的ID。同样，要更新多值查找字段，只需指定以逗号分隔的父查找项的 ID。

SharePoint Online：使用 PnP PowerShell 更新查找字段值

我们可能并不总是知道父查找项的 ID，并且可能希望通过文本值设置查找字段值。因此，以下是我们如何使用查找文本值更新 SharePoint Online 中的查找字段值。

#Config Variables
$SiteURL = "https://crescent.sharepoint.com/sites/pmo"
$ListName = "Project Milestones"
$LookupFieldName = "Parent_x0020_Project"
$LookupValueText = "VMware Upgrade"
$ItemID = 1

#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Interactive

#Get Parent Lookup List and Field from Child Lookup Field's Schema XML
$LookupField =  Get-PnPField -List $ListName -Identity $LookupFieldName
[Xml]$Schema = $LookupField.SchemaXml
$ParentListID = $Schema.Field.Attributes["List"].'#text'
$ParentField  = $Schema.field.Attributes["ShowField"].'#text'
$ParentLookupItem  = Get-PnPListItem -List $ParentListID -Fields $ParentField | Where {$_[$ParentField] -eq $LookupValueText} | Select -First 1

If($ParentLookupItem -ne $Null)
{
    #Update lookup field value using PnP PowerShell
    Set-PnPListItem -List $ListName -Identity $ItemID -Values @{$LookupFieldName = $ParentLookupItem["ID"]}
    Write-host "Lookup Column Value Updated Successfully!" -f Green
}
Else
{
    Write-host "Lookup Column Value '$LookupValueText' Not found in the Parent Lookup List!" -f Yellow
}

要使用 PowerShell 在 SharePoint Online 中检索查找字段列值，请使用：在 SharePoint Online 中使用 PowerShell 获取查找字段