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

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

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".

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".

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