0

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:-

  • Employee submit a leave or vacation request to his/her manager.

  • After that, the request can only be edited/Approved by the manager & read-only by the employee who submitted the request

  • 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?

2
  • 1
    Do you need to keep an archive/history of all the leave requests? May 9, 2023 at 20:14
  • @DylanCristy well if i ask the custom they will say yes,, but i am sure SharePoint will not support this,, what is in your head? can you explore?thanks May 10, 2023 at 16:09

2 Answers 2

1

Why not use folder architecture to keep number of unique permissions lower:

1 Folder for each user. Inside that folder 1 Folder for each status of the approval process

1- User (Folder)
2-- Pending Approval (Folder) With unique permissions for User + Manager + HR Team
3--- ListItems Here
2-- Approved (Folder) With unique permissions for User + Manager + HR Team
3--- ListItems Here
2-- Rejected (Folder) With unique permissions for User + Manager + HR Team
3--- ListItems Here

This way instead of have unique permissions for each item, you will have total of: Users * (Number of approval steps).

800 + 3 = 2400 unique permissions.

Since each folder only counts for one unique permission.

After that, just keep moving the list items between each folder to manage the permissions for the item.

2
  • I thought of this idea, but creating folders for each user and each status should like wired architecture decision ,, also not talking about the headache of managing and creating those folders, to keep them sync with new/existing users.. am i correct? May 10, 2023 at 16:08
  • I guess you should already have somewhere the structure where you define who is the manager for each user (¿Another SharePoint List?). You could have a scheduled task (Each 24 hours ofr example) that iterates over this hierarchy list and makes sure that a folder with the adecuate permissions exists on your holiday list. I normally use PowerShell scripts as a task that keep this permissions structure updated. Takes at most 30 seconds a day to keep this in check, and around 8 minutes if the structure has to be created from 0.
    – Vertamin
    May 10, 2023 at 17:33
1

The way I would handle this situation is by trimming old items using a scheduled Power Automate Flow.

A leave request is going to have a start date and an end date. Once the end date arrives and the employee is back from their leave, what use is the leave request?

If there is no need to keep a record / archive of old leave requests, you could set up a scheduled Flow to run after business hours and just delete any leave requests who's end date is today or older. (Or yesterday or older, if you want to give an extra day buffer.)

If there is some need to keep the historical data of old leave requests, you could set up an "archive" list, and copy the old items from the main leave request list to the archive list, and delete the item from the main list, and set up permissions on the archive list at the list level to give permissions only to those who might need to access the historical data.

That would make it so that the list that grows and grows over time has permissions set at the list level and therefore won't ever run into the unique item limit, and the list that has permissions set at the item level is continually trimmed of items to stay below the unique item limit.

8
  • but in my case i have 7 SharePoint lists to manage the leave/vacation requests ,, so in this case i will need to create 7 archive lists? am i correcT? May 10, 2023 at 17:55
  • i have 7 lists because i am securing the backend sharepoint lists, so not everyone can edit everything at the back end!! May 10, 2023 at 17:55
  • i am affraid that i am start to feel that sharepoint is not the right place to develop such a system ! May 10, 2023 at 17:56
  • @Dyln Cristy what do you think May 10, 2023 at 18:11
  • 1
    why do you need 7 lists? as far as your second question, there are a couple things to consider - do the users really need to see their own previous requests? or can you make the case that just managers and HR need to see them? if users really really need to be able to see their own previous requests, you can kind of fake it if you are making the UI in Power Apps - just make it so that the Power App only pulls and displays previous requests from thh current user. Yes, if they go directly to the list they might be able to see other users items but why would they go to the list if they... May 10, 2023 at 21:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.