SharePoint Online List View Thresholds

Running CAML queries against List Views with a large number of items (> 5000) can fail with the following error.

The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.

To overcome this problem we have to ensure our query meets these requirements when running against SharePoint Online.

  • Run the query in a loop a number of times using the ListItemCollectionPosition to keep track of the looping process.
  • Ensure that the columns specified in any Query Where clause and the OrderBy are already indexed on the list view. If you include columns in either of these parts of the query that are not indexed then you will still get the same error.
  • Ensure the Query AllowIncrementalResults is set to true
  • If you want to restrict the Query to a particular folder in the List View set the FolderServerRelativeUrl property.

I used PowerShell to test out my CAML query.

#Load SharePoint CSOM Assemblies
$folder = "C:\Nuget\packages\Microsoft.SharePointOnline.CSOM.16.1.8412.1200\lib\net45"
Add-Type -Path "$folder\Microsoft.SharePoint.Client.dll"
Add-Type -Path "$folder\Microsoft.SharePoint.Client.Runtime.dll"
   
#Variables for Processing
$SiteUrl = "https://mysharepoint.sharepoint.com/sites/mysite"
$ListName="MyList"
 
$UserName="me@mysharepoint.onmicrosoft.com"
$Password ="nottellingyou"
  
#Setup Credentials to connect
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName,(ConvertTo-SecureString $Password -AsPlainText -Force))
  
#Set up the context
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl) 
$Context.Credentials = $credentials

try {
   
    #Get the List
    $List = $Context.web.Lists.GetByTitle($ListName)

    $Context.Load($List)
    $Context.ExecuteQuery()

    $position = $null

    $allItems = @()

    Do {
        $Query = New-Object Microsoft.SharePoint.Client.CamlQuery;
        # Define the starting position
        $Query.ListItemCollectionPosition = $position

        $Query.ViewXml = "<View Scope='RecursiveAll'>  
                            <Query> 
                                <Where>
                                    <And>
                                        <And>
                                            <And>
                                                <Eq><FieldRef Name='ReportName' /><Value Type='Text'>Monthly Report</Value></Eq>
                                                <Eq><FieldRef Name='Category' /><Value Type='TaxonomyFieldType'>Statement</Value></Eq>
                                            </And>
                                            <Eq><FieldRef Name='ProductLine' /><Value Type='TaxonomyFieldType'>Credits</Value></Eq>
                                        </And>
                                        <Eq><FieldRef Name='ContentType' /><Value Type='Computed'>Report</Value></Eq>
                                    </And>
                                </Where>
                            </Query> 
                        </View>"

        # Define a folder for the starting point of the recursive search
        $Query.FolderServerRelativeUrl = "/sites/MySite/MyList/Folder1"
        $Query.AllowIncrementalResults = $true

        $ListItems = $List.GetItems($Query) 
        $Context.Load($ListItems)

        $Context.ExecuteQuery()    
    
        # Increment the next position for the search
        $position = $ListItems.ListItemCollectionPosition
    
        $allItems += $ListItems
    }
    Until($position -eq $null)
}
Catch {
    Write-Error $_.Exception.Message
    Break
} 


write-host "Total Number of List Items found:"$allItems.Count
 
#Loop through each item
$allItems | ForEach-Object {
    #Get the Title field value

    $Context.Load( $_.File)
    $Context.Load( $_.File.ListItemAllFields)
    $Context.ExecuteQuery()
    write-host $_.File.Name
}  
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s