Caml Query Sharepoint List

This post is about directly applying aggregations on a SharePoint List, without specifying any grouping condition. If you want to group SharePoint List data before applying aggregation then, refer to this post, SharePoint Group By.

• Camlquery Viewxml Examples
• Caml Query Sharepoint List View
• Caml Query For List
• Write Caml Query Sharepoint List

I must admit that this turned out to be little tricky than what I had imagined. The situation was fairly simple. I had a SharePoint List. I had applied couple of Totals functions, Standard Deviation & Variance, on one of its Number field in two separate custom views. I just wanted to retrieve those values from client side. That's it! My initial assessment was there must be some function/property that I can easily query from the client. As it seemed initially, there's no direct way of doing that. Fine, just another day at the office for a SharePoint developer 😉

What irked me was when I googled the issue, almost all the responses were like it's not possible! The value is never stored. SharePoint calculates it on the fly, and then it displays the result on the View Page using its XSLT. The only possible solution was to retrieve all the items first from the SP, and then perform your calculation like, Sum, Avg, etc. on the client side. Though technically, the approach was correct but, somehow it didn't appeal to me for the following reasons:

• Doing the same calculation on the client-side which SP was already doing seemed pretty weird.
• It could result in huge performance overhead considering your List has large number of items or it could grow exponentially in the foreseeable future. Downloading all the items in that case and then looping through all of them to calculate the Sum, Avg, etc., would only make things slower and slower.

1. Use the getlistItemCollectionPosition method of a list item collection to retrieve the current position, then use that value as the parameter in an SP.CamlQuery object's setlistItemCollectionPosition method to retrieve the next batch of results.
2. Available in SharePoint Online To return items from a list, use the GetItemById method to return a single item, or the GetItems (CamlQuery) method to return multiple items. You then use the Load (T, ) method to attain list item objects that represent the items. Retrieving items from a list.
3. How to Execute CAML Query in Large SharePoint Lists using CAML To avoid the Exception – The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator – SharePoint Office 365.
4. Stack Exchange network consists of 176 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Get SharePoint Items By CAML Query action ¶ Get SharePoint Items By CAML Query is the action from Plumsail SharePoint connector. You can use it to get various information about items or documents by using CAML query.

I was somehow, still not convinced that it's not possible. If SharePoint is calculating it, why should I have to re-calculate the same? Couldn't I just get the value from the SharePoint itself? I then looked into the schema of one of the custom view.

Notice the little Aggregations tag. That was like, the first ray of hope. Aggregations seemed like the possible solution 'cause it was carrying the function definition, VAR, for VARIANCE.

Wrong Approach

So I ran the following code, with the Aggregations tag in the ViewXml, to get the Standard Deviation of the numeric field, OrderID:

I only received the first ListItem with its properties but no Standard Deviation! If I don't mention the RowLimit then, all the ListItems were being retrieved. I was definitely taking the wrong approach 'cause I only needed the aggregate function result and not the entire set of ListItems.

Correct Approach

I knew, that the approach had to be altered. CAML query is the answer but you can't get a single calculated result from GetItems. I then started looking at various other methods that the List offers and then, I came across the method, RenderListData. It returns a ClientResult (read JSON) and accepts viewXml (read CAML) as parameter. So I ran the following code:

And voila! It's done! 😎
The output for both the queries were:

Look at the values, OrderID.VAR, OrderID.STDEV, & OrderDate.COUNT. The count field reveals that the List had 1599 items. Although, some fields of the first item were retrieved due to the RowLimit being set to 1, still the response, I would say, was crisp and to the point only instead of the entire ListItemCollection which seemed to be the only option earlier. The VAR (Variance) & STDEV (Standard Deviation) can be verified from the screen-shots below

Std Deviation

Variance

Here's the list of all the Totals function along with their TYPE values. To fetch the result of any of the Totals function just, use the corresponding Type value in the Aggregations property.

Footnotes

• Using CSOM, I have fetched these values in a C# console application. Though, I haven't tried the JavaScript approach, I don't see why the same won't work there considering that the RenderListData method is available in both, REST API and sp.js.
• All these calculations, in this blog, has been carried out on the 'Sales Order Details' table of the AdventureWorks-2012 database.

There are some scenarios that our List exceeds the threshold. Actually, the threshold of 5000 items for Lists and 20000 items for OneDrive is very less now a day. Almost all the transaction lists are growing in Lakhs.

Let us see, as a developer, how are we going to handle this. CSOM Gives a property called ListItemCollectionPosition by which, we can overcome our problem.

Whenever we are executing a CAML Query, there is a property called ListItemCollectionPosition. That we can compare it to the last retrieved value position. Say for example, if there are total 200 items in the list and we are setting a row limit of 100 on our Query, then the ListItemCollectionPosition will become 100 and we can compare that with the null, for our next execution. i.e., we are going to implement a while loop, until the whole list items were retrieved and stored in a separate object.

In my test scenario, I have created a list and added a 1,00,000 items on it.

Now, I am going to iterate through the items and get all the 1 Lakhs items in to a Generic List.

So I ran the following code, with the Aggregations tag in the ViewXml, to get the Standard Deviation of the numeric field, OrderID:

I only received the first ListItem with its properties but no Standard Deviation! If I don't mention the RowLimit then, all the ListItems were being retrieved. I was definitely taking the wrong approach 'cause I only needed the aggregate function result and not the entire set of ListItems.

Correct Approach

I knew, that the approach had to be altered. CAML query is the answer but you can't get a single calculated result from GetItems. I then started looking at various other methods that the List offers and then, I came across the method, RenderListData. It returns a ClientResult (read JSON) and accepts viewXml (read CAML) as parameter. So I ran the following code:

And voila! It's done! 😎
The output for both the queries were:

Look at the values, OrderID.VAR, OrderID.STDEV, & OrderDate.COUNT. The count field reveals that the List had 1599 items. Although, some fields of the first item were retrieved due to the RowLimit being set to 1, still the response, I would say, was crisp and to the point only instead of the entire ListItemCollection which seemed to be the only option earlier. The VAR (Variance) & STDEV (Standard Deviation) can be verified from the screen-shots below

Std Deviation

Variance

Here's the list of all the Totals function along with their TYPE values. To fetch the result of any of the Totals function just, use the corresponding Type value in the Aggregations property.

Footnotes

• Using CSOM, I have fetched these values in a C# console application. Though, I haven't tried the JavaScript approach, I don't see why the same won't work there considering that the RenderListData method is available in both, REST API and sp.js.
• All these calculations, in this blog, has been carried out on the 'Sales Order Details' table of the AdventureWorks-2012 database.

There are some scenarios that our List exceeds the threshold. Actually, the threshold of 5000 items for Lists and 20000 items for OneDrive is very less now a day. Almost all the transaction lists are growing in Lakhs.

Let us see, as a developer, how are we going to handle this. CSOM Gives a property called ListItemCollectionPosition by which, we can overcome our problem.

Whenever we are executing a CAML Query, there is a property called ListItemCollectionPosition. That we can compare it to the last retrieved value position. Say for example, if there are total 200 items in the list and we are setting a row limit of 100 on our Query, then the ListItemCollectionPosition will become 100 and we can compare that with the null, for our next execution. i.e., we are going to implement a while loop, until the whole list items were retrieved and stored in a separate object.

In my test scenario, I have created a list and added a 1,00,000 items on it.

Now, I am going to iterate through the items and get all the 1 Lakhs items in to a Generic List.

Camlquery Viewxml Examples

Before doing that, let us see, what happens, when I execute the normal way of querying.

Caml Query Sharepoint List View

We will end up with the below exception.

Microsoft.SharePoint.Client.ServerException: ‘The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.'

Now, let us write the logic to retrieve the information.

Caml Query For List

Happy Coding,

Write Caml Query Sharepoint List

Sathish Nadarajan.