Post
Ask Question

Various List Operations With SharePoint Online List View Threshold Limit

In this article, we will learn about how we can work with SharePoint Online List view threshold. A myth with SharePoint List view threshold is the storage limit with the number of records, but it is not correct. List view threshold displays the number of record limits in SharePoint views.

According to Microsoft, the number of records storage limit is 30 million in SharePoint List & Library and the size of an individual file item or item attachment 10 gigabytes.

Let’s see this issue and its workaround in details,

Issue

When we have more than 5000 records in list, we are not able to access/create custom views, lookup, do records search, filter & sort with list and we will get this type of warning message in SharePoint online:

Warning Message

The number of items in this list exceeds the list view threshold, which is 5000 items. Tasks that cause excessive server load (such as those involving all list items) are currently prohibited.



Workaround

By default, the list view threshold is configured at 5,000 items.

If SharePoint is an on-premise server, we can increase items of the list view threshold.

But it is not possible to increase items of List view threshold with SharePoint online because it uses the Large List Resource Throttling feature.

So, how we can display records of that list with SharePoint Views? Here, List column indexing comes into place.

Suppose that we have a ‘Product’ list which has around 5700+ records in the list as below,

Column NameDatatype
NameSingle line of text
MfgYearSingle line of text
PriceCurrency
IsDiscountYes/No

If we want to create a view base on some condition like ‘MfgYear’ equal to 2017 which is querying record of Product list, it will display a List view threshold warning message instead of records because we will be querying 5000+ records.

To display records in view, we will add index on ‘MfgYear’ Column and if filtering record criteria of this ‘MfgYear’ column is fewer than 5000 records it will be displayed records.

Note
If we exceeded the List View Threshold and have been blocked, we can normally still add indexes to columns when we have fewer than 20,000 items in our list or library.

Now, we will perform some use case operation with list as following,

  • Update List column data based on another column,
  • Delete records based on condition.

Let’s see in detail,

Update List column data based on another column

If we want to add a new column ‘DiscountPrice’ in that list and update that column data based on ‘Price’ which record have ‘IsDiscount’ is true and ‘MfgYear’ is 2017. But If we will use COSM or REST then, we will not be able to directly fetch those records due to List Threshold Warning.

Hence, we will iterate each record using ‘ListItemCollectionPosition’ property of list to avoid Threshold issue and, we will be taking 200 rows view batch using CAML query.

Please refer to the below code snippet to see how we can update list records,

  1. using (ClientContext context = new ClientContext(siteUrl))  
  2.             {  
  3.                 var onlineCredentials = new SharePointOnlineCredentials(login, securePassword);  
  4.                 context.Credentials = onlineCredentials;  
  5.                 SP.List myList = context.Web.Lists.GetByTitle("Product");  
  6.   
  7.                 CamlQuery camlQuery = new CamlQuery();  
  8.                 camlQuery.ViewXml = string.Format("<View><RowLimit>200</RowLimit></View>");  
  9.   
  10.                 do  
  11.                 {  
  12.                     ListItemCollection collListItem = myList.GetItems(camlQuery);  
  13.                     try  
  14.                     {  
  15.                         context.Load(collListItem);  
  16.                         context.ExecuteQuery();  
  17.                         if (collListItem.Any())  
  18.                         {  
  19.   
  20.                             foreach (var item in collListItem)  
  21.                             {  
  22.   
  23.                                 if (dt != null && dt.Rows.Count > 0)  
  24.                                 {  
  25.                                     foreach (DataRow dtRow in dt.Rows)  
  26.                                     {  
  27.                                         if (Convert.ToBoolean(item["IsDiscount"]) == true && Convert.ToInt32(item["MfgYear"]) == 2017 )  
  28.                                         {  
  29.                                                 item["DiscountPrice"] = Convert.ToDouble(dtRow["Price"]) * 0.10;  
  30.                                                 item.Update();  
  31.                                                 context.ExecuteQuery();  
  32.                                         }  
  33.                                     }  
  34.                                 }  
  35.                             }  
  36.                         }  
  37.                     }  
  38.                     catch (Exception e)  
  39.                     {  
  40.                           
  41.                     }  
  42.   
  43.                     // It is important to update ListItemCollectionPosition of objectcamlQuery with current position  
  44.   
  45.                     camlQuery.ListItemCollectionPosition = collListItem.ListItemCollectionPosition;  
  46.                      
  47.                // ListItemCollectionPosition is null if there is no other page  
  48.                 } while (camlQuery.ListItemCollectionPosition != null);  
  49.             }  

Delete records based on condition

Suppose we want to delete records in which Price column will have zero value from ‘Product’ list, but we will not be directly filtering records due to the list threshold limit warning. And if we will delete each record one by one it will be very time consuming.

So, we will be again taking 200 rows view batch using CAML query and deleting records in batch.

If we want to delete all records from ‘Product’ list, then we will not need to filter records.

In such cases we will not use ‘ListItemCollectionPosition’ property because List Item Position will be moved automatically after deleting each record.

Please refer to the below code snippet to see how we can update list records,

  1. using (ClientContext context = new ClientContext(siteUrl))  
  2.             {  
  3.                 var onlineCredentials = new SharePointOnlineCredentials(login, securePassword);  
  4.                 context.Credentials = onlineCredentials;  
  5.                 SP.List myList = context.Web.Lists.GetByTitle("Product");  
  6.   
  7.                 CamlQuery camlQuery = new CamlQuery();  
  8.                 camlQuery.ViewXml = string.Format("<View><RowLimit>200</RowLimit></View>");  
  9.   
  10.                 do  
  11.                 {  
  12.                     ListItemCollection collListItem = myList.GetItems(camlQuery);  
  13.                     try  
  14.                     {  
  15.                         context.Load(collListItem);  
  16.                         context.ExecuteQuery();  
  17.                         if (collListItem.Any())  
  18.                         {  
  19.   
  20.                             foreach (var item in collListItem)  
  21.                             {  
  22.   
  23.                                 if (dt != null && dt.Rows.Count > 0)  
  24.                                 {  
  25.                                     foreach (DataRow dtRow in dt.Rows)  
  26.                                     {  
  27.                                         if (Convert ToDouble(item["Price"]) == 0)  
  28.                                         {  
  29.                                                 item.DeleteObject();  
  30.                                         }  
  31.                                     }  
  32.                                     context.ExecuteQuery();  
  33.   
  34.                                 }  
  35.                             }  
  36.                         }  
  37.                     }  
  38.                     catch (Exception e)  
  39.                     {  
  40.                           
  41.                     }  
  42.                    camlQuery.ListItemCollectionPosition = collListItem.ListItemCollectionPosition;  
  43.   
  44.                 } while (camlQuery.ListItemCollectionPosition != null);  
  45.             }  

Conclusion

This is how we can work with SharePoint Online List View Threshold.

For more detail, please refer to this article from Microsoft.

Hope you love this article…!!!

Happy SharePointing...! 😊


Similar Articles