1

I need some support in how to target specific views within a SharePoint Online list using CAML.

Just last week our script started to fail because we've now hit the view thresholds of our list - it's now sitting at 7500 items.

We've gone through the columns and indexed what fields are being referenced but the error is still appearing. My thought is to now target a particular view instead of the default as we only need those items which are of a particular status retrieved.

I've tried to use the View element examples found here but no matter what I input I'm just getting the default back.

MSDN View element(list)

Here's the CAML query:

<View>
   <Query>
      <Where>
         <Or>
            <Eq>
                <FieldRef Name='Status' />
                <Value Type='Choice'>Provisioning Site</Value>
            </Eq>
            <Eq>
                <FieldRef Name='Status' />
                <Value Type='Choice'>Requested</Value>
            </Eq>
         </Or>
     </Where>
     <OrderBy><FieldRef Name='Status' Ascending='FALSE' /></OrderBy>
  </Query>
</View>

And here's the error we're currently getting.

System.Management.Automation.MethodInvocationException: Exception calling "ExecuteQuery" with "0" argument(s): "The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator." ---> Microsoft.SharePoint.Client.ServerException: The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator. at Microsoft.SharePoint.Client.ClientRequest.ProcessResponseStream(Stream responseStream) at Microsoft.SharePoint.Client.ClientRequest.ProcessResponse() at CallSite.Target(Closure , CallSite , ClientContext ) --- End of inner exception stack trace --- at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception) at GetAllListItems(Closure , FunctionContext ) at System.Management.Automation.Interpreter.LightLambda.RunVoid1[T0](T0 arg0) at System.Management.Automation.PSScriptCmdlet.RunClause(Action`1 clause, Object dollarUnderbar, Object inputToProcess) at System.Management.Automation.CommandProcessorBase.Complete()

  • 1
    On further investigation we've found that the error isn't coming from this query but one further into the process. Would I be better to raise a new question for this (assuming I can't find the answer) or edit this question? It's likely to make the answer given irrelevant if I edit. Thanks – James Buckingham Feb 20 '17 at 10:11
  • 1
    It's better to ask different question than editing this. – Bhaskar Dhone Feb 22 '17 at 11:17
1

You can query list with more than list view threshold as below : Index your status columns It will query 5000 number of items we give in CAML query in RowLimit tag. Next items will be queried with position. In this way it will query all items. Source

You can use below code for reference :

$list = $ctx.Web.Lists.GetByTitle($YourListName)
$ctx.Load($list)
$ctx.ExecuteQuery()

## View XML
$qCommand = @"
<View Scope="RecursiveAll">
    <Query>
        <Where>
        <Or>
        <Eq><FieldRef Name='Status' /><Value Type='Choice'>Provisioning Site</Value></Eq>
        <Eq><FieldRef Name='Status' /><Value Type='Choice'>Requested</Value></Eq>
        </Or>
        </Where>
        <OrderBy>
        <FieldRef Name='Status' Ascending='FALSE' />
        </OrderBy>
    </Query>
    <RowLimit Paged="TRUE">5000</RowLimit>
</View>
"@
## Page Position
$position = $null

## All Items
$allItems = @()
Do{
    $camlQuery = New-Object Microsoft.SharePoint.Client.CamlQuery
    $camlQuery.ListItemCollectionPosition = $position
    $camlQuery.ViewXml = $qCommand
 ## Executing the query
    $currentCollection = $list.GetItems($camlQuery)
    $ctx.Load($currentCollection)
    $ctx.ExecuteQuery()

 ## Getting the position of the previous page
    $position = $currentCollection.ListItemCollectionPosition

 # Adding current collection to the allItems collection
    $allItems += $currentCollection
}
# the position of the last page will be Null
Until($position -eq $null) 

Targetting specific View if you want to go with your approach by querying view if they contains less than threshold: You can go with below one

    $view = $list.Views.GetByTitle("Viewname")
    $context.Load($view)
    $context.ExecuteQuery()
    $camlQuery = New-Object Microsoft.SharePoint.Client.CamlQuery
    $camlQuery.ViewXml = $view.ViewQuery
    $items = list.GetItems(camlQuery);
    $context.Load(items);
    $context.ExecuteQuery();
  • Thanks very much for the detailed reply Bhaskar. That's great! We'll try these and let you know how we get on. – James Buckingham Feb 16 '17 at 12:12
  • See my comment in the original posting about how this issue has changed but I think this answer offers an alternative solution to try that I hadn't found elsewhere online (mainly the targeting of specific views). – James Buckingham Feb 22 '17 at 11:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.