Handle the limit of 50,000 unique items per list in SharePoint online

Question

As per the SharePoint online limitation, it is recommended to stay below 5,000 unique items per list https://learn.microsoft.com/en-us/office365/servicedescriptions/sharepoint-online-service-description/sharepoint-online-limits , but as i know we can have up to 50,000 unique items per list.

Now I want to start a new project for a company which have 800 employees, to manage the employees Leave & Vacation requests. as follow:-

1. Employee submit a leave or vacation request to his/her manager.
2. After that, the request can only be edited/Approved by the manager & read-only by the employee who submitted the request
3. After approving it >> both the manager and employee will have read-only permission + the HR Team.

Now we need to secure the SharePoint lists >> by defining unique permissions for the items as the process move (as per the above 3 steps). now i am afraid that the limitation of 50,000 unique items can be reached within less than 2-3 years. because we have 800 employees , so each employee can only have 60 leave & vacation requests. so how we can handle this? i read some articles which mention to use multiple SharePoint lists, but this will not be a practical appraoch because we will have to manage many lists, and we are planning to build the UI using Power apps which connect to the SharePoint lists. any advice please?

Answer 1

Hello @john john !

If you are concerned about hitting the limit of 50,000 unique items per list in SharePoint online, you may want to consider using a combination of multiple lists and folders within those lists. For example, you could create a new list for each year and then use folders within each list to organize the requests by employee. This would allow you to stay within the limit of 50,000 unique items per list while still keeping all of the requests for a particular employee together in one place.

Another option would be to use a database instead of SharePoint lists to store the leave and vacation requests. This would give you more flexibility in terms of the amount of data you can store and how you can organize and manage it. You could still use Power Apps to build the UI and connect to the database using the appropriate connectors.

I hope this helps!

Kindly mark the answer as Accepted and Upvote in case it helped!

Regards

Answer 2

Hi @john john ,

Thanks for posting in the community.

Actually, we can store up to 30 million items or files in a list or library. However, when a list view shows more than 5000 items, you may run into a list view threshold error.

First, you can add indexes and filters to manage your SharePoint Lists, here is the reference about How to add indexes and filters in lists: Manage large lists and libraries - Microsoft Support

Second, you can export the items in the list before a certain period of time into an excel file, and there is no limit to the number of lists in the excel file, and the excel file is also convenient for you to check the previous items. Here is the reference about how to export items form lists:

https://www.sharepointdiary.com/2016/03/export-list-items-to-csv-in-sharepoint-online-using-powershell.html

Note: Microsoft is providing this information as a convenience to you. The sites are not controlled by Microsoft. Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. Please make sure that you completely understand the risk before retrieving any suggestions from the above link. 

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 3

Hi @john john ,

I apologize for misunderstanding your question, I checked this documentation:

https://learn.microsoft.com/en-us/sharepoint/troubleshoot/lists-and-libraries/error-share-break-inheritance

When a folder, repository or list contains more than 100,000 items, you can neither break permission inheritance nor re-inherit permissions for the folder, repository or list. This is by design of SharePoint and cannot be modified.

My suggestion is that you can use folders in lists to keep your item with different permissions.

For example:

• folder A: employee has edit permission level.
• folder B: manager has edit/approve access, employee has view-only permission level.
• folder C: employee, manager and HR team have view-only permission level.

folder A saves the leave application submitted by the employee, when the user submits it, the item will be moved to folder B, when the manager approves it, the item will be moved to folder C.

You can use power automate to create flow to move the item.

I hope my suggestion can be helpful to you. Thank you for your tolerance and understanding. Hope your problem can be solved successfully!

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 4

Hello @john john !

I am returning to the original idea

Use an external DB or Tool

Essentially SharePoint is designed for a specific purpose (content/documents) and trying to get it to do something out of the ordinary means you have to fight the application tooth and nail.

Fortunately SharePoint has several means of integrating transactional data into it.

First off (if you have the more expensive Enterprise licence) you have the Business Data Catalog that allows you to import database values that will appear similar to list items.

If you do not have the Enterprise licence, I can recommend either custom controls/webparts or the Data View Web Part to allow that data to be "shown" on the relevant pages within SharePoint.

In summary: You will be setting yourself up for a lot of uneccesary work by storing transactional data within SharePoint compared to other application designs hosting the data in traditional database applications and integrating to SharePoint.

Source : https://stackoverflow.com/questions/184653/sharepoint-lists-vs-database-tables-performance

I hope this helps!

Kindly mark the answer as Accepted and Upvote in case it helped!

Regards