ReDesigning Highly Customised SharePoint System due to 5000 Threshold Limit

Question

We have a highly customised library in a SharePoint Online environment. The client company uses this system for day-to-day operation. CRM integrated with SharePoint as a repository would have made much more sense for this client's requirements, however, they did not want to go for CRM licenses (for cost reasons) and we ended up developing an over-customised system in SharePoint. We tried to plan forward and minimise the possibilities of anything going wrong, which in fact kinda worked... to a certain extent. At the end of the day, once they exceeded the 5000 items, lookup fields looking into the main library, now stopped working and instead show the typical and annoying 5000 item threshold limit message.

Initially I suggested trying to fix/change the lookup field by using Javascript/Jquery or tools of the sort. This would have been a temporary workaround but it would not have solved the real problem. The library would still have continued to grow exponentially and going forward, more issues would inevitably occur.

We came up with a plan to re-design the library.

High Level Explanation of the SharePoint

An Order Library (the main problematic library) looking into Clients / Suppliers library. Then other libraries or lists looking into the Order Library. The Lookup fields looking into the Orders library are the ones which stopped working.

Plan

The Suppliers library consists of around 30 suppliers. The numbers of orders within the Orders library would definitely go down a lot if we could re-design the system to have 30 different order libraries: one for every supplier. We would then migrate the current orders library into the separate supplier libraries and change configuration within CSOM or so to handle the separate libraries instead of the usual same one.

But there are some very important things that I am unsure about.

1) We'd have to create the Orders library as a template and re-create it 30 times. We have to make sure that this is also done easily, going forward, as suppliers can be added. Have you ever tried to create libraries as templates and restore them? Anything that does not work? Any suggestions?

2) There are around 50 (both 2013 and 2010) list workflows associated to the orders list. Will they work if we recreate them into reusable workflows and use them with all the libraries instead of one?

Naturally I will test prior to starting any re-designing implementation, but whilst still in the planning stage, I would appreciate some external advice. I need to be sure that second time round, the approach we take from now is the correct one. Thanks in advance.

Answer 1

The list view threshold limit is indeed a challenge and Microsoft is thinking about what to do. See the suggestion Remove the list view threshold on UserVoice.

I think your redesign idea is flawed in the sense that you will still not be able to have more than 5000 orders for any one supplier. You will buy yourself some time but that is it. Also, it adds unnecessary work and complexity.

Instead, employ indexed columns to avoid the threshold limit. SharePoint can with an indexed column on a view or filter, work with more than 5000 items. Just index your supplier column and other relevant columns. Then create filtered views or enable metadata navigation in the library. You might also consider the use of folders for scaling your library. For example, by grouping orders into folders by year, quarter or month. Whatever unit that you think will never surpass 5000 orders.

Now, once you have passed the 5000 limit you can no longer add a new column index! It will also trigger a threshold error. Consequently, you'll need to delete some orders first. Alternatively, create and configure and new site collection and then migrate your existing orders and related content there.

Anyway, let me also try and answer your specific questions:

1. Yes, use the SharePoint PnP Provisioning Framework to provision a library by template.
2. Yes, define site content types and associate your workflows. Then use these content types in all libraries.