none
CamlQuery RRS feed

  • Question

  • I am trying to retrieve SharePoint Online list items using the following query:

    $spqQuery.ViewXml="<Where><And><And><Gt><FieldRef Name='ID' /><Value Type='Number'>1</Value>"+ 

            "</Gt><Lt><FieldRef Name='ID' /><Value Type='Number'>4700</Value></Lt></And><Eq><FieldRef Name='Author' /><Value Type='User'>de</Value></Eq></And></Where>"

    Every time I receive an error: "Cannot execute, because it exceeds administrative threshold".

    This is baffling, since the query is set to get only 4700 items, which is below 5000 items threshold.

    What is even more baffling is - if I run a query without specifying the author - my request is within the limit and I get all items returned. 

    Working query: 

    "<Where><And><Gt><FieldRef Name='ID' /><Value Type='Number'>1</Value>"+ 

            "</Gt><Lt><FieldRef Name='ID' /><Value Type='Number'>4700</Value></Lt></And></Where>"

    I thought specifying additional filters would make the query more efficient, and I could actually go through more than 5000 items (the user authored just a few files per thousand). How is it that it's exactly the opposite? 

    Thanks in advance for every explanation.

    Monday, September 23, 2019 10:20 PM

Answers

  • Hi DE 321-2,

    I tested with CAML Query "1<ID<4700" and a specific Author "Jerry zy" for example, there is no List View Thershold issue in my side:

    #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://tenant.sharepoint.com/sites/sitename/"
    $ListName="CamlList"
      
    #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
    $List = $Ctx.Web.lists.GetByTitle($ListName)
     
    #Define the CAML Query
    $Query = New-Object Microsoft.SharePoint.Client.CamlQuery
    $Query.ViewXml = "@
                            <View>  
                            <Query> 
                            <Where><And><And><Gt><FieldRef Name='ID' /><Value Type='Counter'>1</Value></Gt><Lt><FieldRef Name='ID' /><Value Type='Counter'>4700</Value></Lt></And><Eq><FieldRef Name='Author' /><Value Type='User'>Jerry zy</Value></Eq></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)
    {
        Write-host $Item.id
    	Write-host $Item["Title"]
    }
    

    In my List, there are 5300 items which exceeds the List View Threshold, but the CAML Query above is actually working in my side, please see the result after executing the CAML to filter Author:

    You can try to add the Author (Created By) as a indexed column to see if it can fix the issue:


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    • Marked as answer by DE 321-2 Tuesday, September 24, 2019 11:25 AM
    Tuesday, September 24, 2019 7:19 AM

All replies

  • Hi DE 321-2,

    I tested with CAML Query "1<ID<4700" and a specific Author "Jerry zy" for example, there is no List View Thershold issue in my side:

    #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://tenant.sharepoint.com/sites/sitename/"
    $ListName="CamlList"
      
    #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
    $List = $Ctx.Web.lists.GetByTitle($ListName)
     
    #Define the CAML Query
    $Query = New-Object Microsoft.SharePoint.Client.CamlQuery
    $Query.ViewXml = "@
                            <View>  
                            <Query> 
                            <Where><And><And><Gt><FieldRef Name='ID' /><Value Type='Counter'>1</Value></Gt><Lt><FieldRef Name='ID' /><Value Type='Counter'>4700</Value></Lt></And><Eq><FieldRef Name='Author' /><Value Type='User'>Jerry zy</Value></Eq></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)
    {
        Write-host $Item.id
    	Write-host $Item["Title"]
    }
    

    In my List, there are 5300 items which exceeds the List View Threshold, but the CAML Query above is actually working in my side, please see the result after executing the CAML to filter Author:

    You can try to add the Author (Created By) as a indexed column to see if it can fix the issue:


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    • Marked as answer by DE 321-2 Tuesday, September 24, 2019 11:25 AM
    Tuesday, September 24, 2019 7:19 AM
  • It worked. Thank you so much!
    Tuesday, September 24, 2019 11:25 AM