SharePoint Online：使用 PowerShell 将查找列添加到列表

要求：为 SharePoint Online 列表创建一个新的查找列。

如何在 SharePoint Online 列表中创建查找列？

SharePoint Online 中的查找列允许您根据同一网站上不同列表中的另一列的值填充列值。使用 PowerShell 添加查找列是更有效地自动化和管理 SharePoint 列表列的强大方法。通过利用 CSOM 或 PnP PowerShell 模块，我们可以创建具有所需属性的查找列。此方法为管理 SharePoint 列提供了有效且可编写脚本的解决方案，其中通过用户界面进行手动配置可能会很麻烦或耗时。

要添加查找字段，请按照下列步骤操作：

1. 浏览到 SharePoint Online 网站并导航到要添加查找列的目标列表。
2. 在“列表”选项卡下，单击功能区中的“创建列”按钮。

3. 为新列提供名称，将类型指定为“查找”

   

4. 选择要查找的源列表和字段，指定其他可选值，例如列描述、必填字段值等，然后单击“确定”在 SharePoint Online 列表中创建查找字段。

PowerShell 将查找列添加到 SharePoint Online 中的列表：

当您想要将一个列表与另一个列表关联或者想要从另一个列表查找值时，查找列非常有用。让我们看看如何使用 PowerShell 将查找列添加到 SharePoint Online 中的列表。

#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"

#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

PnP PowerShell 将查找列添加到 SharePoint Online 列表

查找列允许您通过将一个列表的项目链接到另一个列表的项目来在 SharePoint 中的列表之间创建关系。让我向您展示如何使用 PnP PowerShell 快速将查找列添加到 SharePoint 列表。

#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}

我们还可以使用字段架构 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