SharePoint Online: CAML Query for Lookup Field in PowerShell
Requirement: CAML Query for Lookup field in SharePoint Online using PowerShell.
CAML for SharePoint Lookup Field Value:
Lookup fields in SharePoint allow users to select a value from another list, which is then linked to the current list item. Lookup fields are used to create a relationship between two lists. CAML (Collaborative Application Markup Language) is an XML-based query language used in SharePoint to query data from lists and libraries. In this article, we will see how to create a CAML query for a lookup field in PowerShell.
Let’s filter projects by parent project lookup field value “Address Book”. Here is the SharePoint CAML for a lookup field.
#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"
#Set parameter values
$SiteURL="https://crescent.sharepoint.com/"
$ListName="Projects"
$LookupValue="Address Book"
#Get Credentials to connect
$Cred= Get-Credential
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
#Get the List Object
$List = $Ctx.Web.lists.GetByTitle($ListName)
#Define the CAML Query
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$Query.ViewXml = "@
<View Scope='RecursiveAll'>
<Query>
<Where>
<Eq>
<FieldRef Name='ParentProject'/><Value Type='Lookup'>$LookupValue</Value>
</Eq>
</Where>
</Query>
</View>"
#Get All List Items matching the query
$ListItems = $List.GetItems($Query)
$Ctx.Load($ListItems)
$Ctx.ExecuteQuery()
Write-host "Total Number of Items:"$ListItems.count
#Loop through each List Item
ForEach($Item in $ListItems)
{
#Do something
Write-host $Item.id
Write-host $Item["Title"]
}
SharePoint CAML Query to Filter Lookup Field by ID:
Since the lookup text defined in the parent list may change at any time, let’s use the lookup field ID instead of text. Here is the PowerShell CAML query for the lookup column.
#Set parameter values
$SiteURL="https://crescent.sharepoint.com/"
$ListName="Projects"
$LookupID="25" #ID of the Parent Lookup Item
#Get Credentials to connect
$Cred= Get-Credential
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
#Get the List Object
$List = $Ctx.Web.lists.GetByTitle($ListName)
#Define the CAML Query
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$Query.ViewXml = "@
<View Scope='RecursiveAll'>
<Query>
<Where>
<Eq>
<FieldRef Name='ParentProject' LookupId='TRUE'/><Value Type='Lookup'>$LookupID</Value>
</Eq>
</Where>
</Query>
</View>"
#Get All List Items matching the query
$ListItems = $List.GetItems($Query)
$Ctx.Load($ListItems)
$Ctx.ExecuteQuery()
Write-host "Total Number of Items:"$ListItems.count
#Loop through each List Item
ForEach($Item in $ListItems)
{
#Do something
Write-host $Item["Title"]
}
SharePoint Online CAML for Multi-Lookup Field:
Here is the SharePoint CAML query for a multi-lookup column, which filters where the “Parent Project” lookup field value contains both “Address Book” and “Announcements”.
#Set parameter values
$SiteURL="https://crescent.sharepoint.com/"
$ListName="Projects"
#Get Credentials to connect
$Cred= Get-Credential
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
#Get the List Object
$List = $Ctx.Web.lists.GetByTitle($ListName)
#Define the CAML Query
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$Query.ViewXml = "@
<View Scope='RecursiveAll'>
<Query>
<Where>
<And>
<Includes>
<FieldRef Name='ParentProject'/>
<Value Type='LookupMulti'>Address Book</Value>
</Includes>
<Includes>
<FieldRef Name='ParentProject'/>
<Value Type='LookupMulti'>Announcements</Value>
</Includes>
</And>
</Where>
</Query>
</View>"
#Get All List Items matching the query
$ListItems = $List.GetItems($Query)
$Ctx.Load($ListItems)
$Ctx.ExecuteQuery()
Write-host "Total Number of Items:"$ListItems.count
#Loop through each List Item
ForEach($Item in $ListItems)
{
#Do something
Write-host $Item["Title"]
}
I have a small issue. I have a list that contains people picker fields requested by the customer. There are multiple people for each line item in the column. I am trying to use JavaScript to Extract email addresses to populate columns I use for emailing approval notices with custom forms. I am using a simple CAML query and the people picker are objects. How do I get the true email addresses to simplify my work?
here’s the while I am using:
while (listItemEnumerator1.moveNext())
{
var oListItem = listItemEnumerator1.get_current();
var Group1 = oListItem.get_item(‘Group’);
var Group2 = oListItem.get_item(‘Squad’);
var Group3 = oListItem.get_item(‘XONames’);
var Group4 = oListItem.get_item(‘GroupSafety’);
I need to extract the info in Group3 and Group4. I think I am supposed to do a lookup but I have no idea what the syntax for that is. Can you help?