1

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.

0

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.
  • Hi Lars, Thanks for your answer. An archiving system is already in place on 'closed' orders, but not effective for now, since no orders are yet closed as completed. Also I have several indexed columns within the library already. I dont understand how using indexed columns or indexed views solves the issue with the Lookup fields returning the threshold error and that is a very important part of the system's functionality. – Jurgen Cuschieri Aug 25 '17 at 13:29
  • Also, note that the total number of items in the library has exceeded the 5000 limit but not the total number of document sets (orders are represented by document sets). I think this is the reason why it in fact lets me add indexed columns yet; because there is more than 5000 items in the library but less than 5000 items in the root (folder level). If I could find a way to go around the problem just by using indexed columns or views, then it's surely something I would go for! – Jurgen Cuschieri Aug 25 '17 at 13:32
  • Ahh yes - lookups into a large list will not work! You're indeed bound by the 5000 limit here. I think the problem is populating the lookup drop-down with all orders. I'm still puzzled why SharePoint cannot load this in batches to stay below the limit. – Lars Fastrup Aug 27 '17 at 11:07
  • I also have another problem. 2013 Re-usable workflows seem to need to be assigned to specific content type to work properly. Otherwise, if set to run on all content types, only the Title field seems to be usable from within the designer. Also when trying to associate the workflow, it also seems to require a content type to be associated – Jurgen Cuschieri Aug 28 '17 at 9:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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