Power Automate SharePoint list 5000 items

It is often a problem for SharePoint users when the number of their items in their library hits the 5,000-mark. If you have experienced this and youre worried that you cant access or find your files anymore, you have come to the right place.

In this article, you will learn more about what exactly triggers this issue, how you can possibly prevent it in the future, and what you can do to fix it.

Lets get started!

The Truth About the SharePoint 5,000 Item Limit Threshold

A lot of people have the misconception that a SharePoint list or library cant have more than 5,000 files or items. This is wrong.

As outlined in Microsofts documentation on software boundaries and limits for servers, the list or library can hold up to 30 million documents or items.

If the limit is 30 million, then whats up with the 5,000 item limit threshold?

Well, thats where this gets more interesting. Item limit threshold isnt actually about the storage capabilities. The issue is in displaying the vast amount of information.

Microsoft introduced the 5,000 view limit on the document library with SharePoint 2010 for one purpose to maintain the performance of the document library and its servers. In the past, the classic library became unusable once the limit has been passed.

But since Microsoft introduced the modern experience, along with its new document library, SharePoint can now tolerate large volumes of data. However, even if you can now upload more than 20,000 documents without freezing the library, the viewing limit still applies.

What happens when you reach the item threshold?

Behind the scenes, there are a lot of things that happen whenever you access a list or document library. A search query happens and the library will return all the results.

Now, lets say you have around 10,000 files in a document library and theyre all in the same folder. When you access that library, you will not be able to see more than 5,000 items on the page as the query will run into the limit threshold.

In addition to not seeing all the items, you will face some problems with a large document library. Most processes like renaming a folder and moving files within the library will take a lot of time.

You will also face some errors like when a user tries to create a calculated column or create unique permissions for the folder.

On the technical aspect, every time you access your library and perform actions in it, the servers have to do a lot of processing and querying. Now, the burden on the servers increases dramatically the more items you have.

This is the reason why Microsoft placed the 5,000 limit threshold to maintain the performance of the server and enable users to access the library without waiting for hours.

How to Prevent the Item Limit Threshold Issue

If youre about to reach the threshold, there are a few things you can do to prevent the item limit issue. As early as now, you must know that its quite easier and quicker to prevent the issue rather than trying to fix it afterward.

With that, here are steps you can do to prevent the issue:

1. Avoid Putting All the Files in One Library

The easiest way to avoid hitting the 5,000 item limit threshold is to not put all your files in one library. Its also a good practice to spread the content among multiple sites and document libraries.

If youre not sure how to do it, organize your files and put them into separate document library based on business function like:

• Project files
• Department files
• Sales and marketing

Grouping your files and documents according to what department they belong to also works. The point here is to spread your files across different libraries and keep doing it as a regular practice so one of your document libraries will not get too bloated.

2. Set up Subfolders

Creating subfolders will allow you to break your content into manageable bits. Although folders also count in the total number of a library, organizing your files in that library into different subfolders can mitigate the risks.

Whats important to remember here is to make sure that any given subfolder in your library doesnt contain more than 5,000 items.

Unfortunately, there are a few downsides to using folders in SharePoint. For example, you cant see how many documents there are in a folder unless you open it yourself. There are also some data integrity issues you may run into in the future.

3. Create Metadata

A better solution to creating subfolders is setting up metadata. Metadata is simply data that provides information about a file or a document. That is, you can add certain information/details to a document or file to make it easier to find.

Implementing a good metadata practice will minimize the burden on the servers since the number of items shown will be limited.

For example, if you search for documents that are specific only to a certain client or project, the server doesnt have to collect all documents in the storage and show them to you all at once and see which one you want to open.

But with metadata, the server will have an idea which certain documents you want to see. In addition, you will be able to filter, sort, and group large lists. This way, you will be able to see all the items in a more organized manner.

After creating metadata for your items, dont forget to enable metadata navigation. This provides you with a settings page to configure the list to use metadata tree view hierarchies and filter controls that will improve navigation and filtering of the contained items.

4. Index the Columns

Creating metadata will allow you to save the server some burden by not having it collect all data. It will still query most, if not all, of your items, which is why it alone will not be enough to solve the item viewing threshold issue.

But if you were able to catalog certain types of columns, then its a different story. By creating indexes, the server will not have to search the whole library every time you do a query. This allows you to bypass the item limit threshold.

For example, if you catalog a certain column as clients, then whenever you need to retrieve items in relation to clients, the server will only have to look for that catalog instead of searching each and every item in the library.

5. Create Different Views

Creating different views may be easy, but it will surely take a lot of time on your end. This solution will require you to plan ahead and set up column indexing, filtering, and creating view item limits.

The cool thing here is that no single view you create can ever violate the 5,000 item limit threshold.

It also helps to adjust the default view. By default, it will display a flat list of files without filtering or sorting. Its what users see whenever they click on the document library, which can then trigger the threshold issue.

On the other hand, even if you configure the default view, you will still face issues if you dont add filtering/sorting on indexed columns. Furthermore, you will need to limit the number of records displayed.

How to fix the SharePoint 5000 Item Limit Threshold

The question here is what to do in case youre already way past the threshold and you werent able to implement any of the steps in the earlier section. Fortunately, there are still some things you can do.

On the downside, the fix will eat up your time.

1. Bring Down the Number of Items to Less Than 5,000

The first fix you can do is to bring the number of items that you have to less than the item viewing threshold. Once the number of your items reaches that limit, it locks you from creating trivial operations in your library.

You will not be able to:

• Manage column indexes
• Add columns
• Adjust the views
• Set permissions

This makes it impossible for you to perform the next fixes, which is why the first thing you need to do is to unlock your library so you can do more fixes.

2. Reorganize Your Whole Library

You can use some of the methods described in the steps in the earlier section on how to avoid the issue. You can move your items into different libraries and start organizing them according to business functions.

On the other hand, you can also delete junk items that are of no use anymore.

Without reorganizing your whole library, you will eventually run into this issue again in the near future. Thats why you must observe good organization practices and utilize as many libraries as possible.

3. Create Metadata Properties

After reorganizing your whole library, its time to create metadata properties and apply default column values to folders in your existing library. Provide as much information as you can on each item that you have.

Once you do that, you will now be able to index columns. As explained earlier, this will allow your server to run a query on the index catalogs instead of searching each and every item. This process is more efficient and will not burden the server that much.

On the other hand, you can also choose to hide some folders altogether from a view. Some users dont need to access certain folders anyway so you can set it up to hide it from them so they will only see a limited number of items.

Perhaps, a lesson here is that you must not underestimate the value of planing your information architecture. As you can see, avoiding the issue is so much easier than fixing it since the latter will take a lot of time and resources.

If you need any help or if you have any questions related to fixing the item limit threshold, feel free to leave a comment below or send me a direct message via the contact page.

About Ryan

As the Principal Solutions Architect at Mr. SharePoint, I help companies of all sizes better leverage the Modern Workplace and Digital Process Automation investments.

View all posts by Ryan | Website