SharePoint List View Threshold

Question

I have a list of customers that has 13,000 unique entries and three columns, one for "Customer Number", "Customer Name" & "Region". I need to use this list for various resources in my SharePoint site such as, a Document Library that has a Lookup column for selecting the Customer Name, a List for Customer Complaints that has a Lookup column for selecting the Customer Name etc.

These lookup columns are looking-up against the Customer Name column in my Customer List. However, I have run into a List View Threshold issue as the number of customers exceeds the 5,000 LVT limit.

My site is hosted on Office 365, SharePoint Online so there is no way to increase the LVT.

If I try to upload a document to the doc lib or try to create a new item in the Customer Complaints list, the drop-down list for the Customer Name is not available, just a message saying the list exceeds the LVT. This works fine when there is 5,000 items or less in the list, I click to upload a file or create a new item on a list and I can select one of my customers from a drop-down list. However as soon as the list goes to 5,001 items or greater, the LVT error message appears.

As I have over 5,000 unique customers in this list and need to be able to select from all, I am stuck on how to get this working. Short of splitting the list up into different lists, each below 5,000 items, then adding multiple "Customer Name" drop-down/lookup columns on the required library or list, which I'm sure all will agree is not really a good way to achieve this, I don't know what else I can do to get this working.

Any advice on how to get this working would be very much appreciated.

Cheers...

Glenn

Answer 1

I have also recently run into the same problem: lookup fields looking into a library containing more than 5000 items. It seems that the list view threshold is most likely there to stay and as you correctly suggest, SharePoint Online does not offer the ability to increase the threshold (unlike On-Premise).

The bad news is that there does not seem to be a way to fix Lookup fields, once the 5000 limit is reached (at least to my knowledge and I've done a lot of research).

You may want to look at this link and this link. Both links essentially discuss workarounds for lookup fields by Javascript and/or JQuery and possibly other 3rd party tools.

I have not tried them personally yet, mostly because in my case, it made more sense to redesign the exponentially growing library and ensure better performance in the future. The 5000 limit was created for a reason: SharePoint does not like working with huge lists/libraries! Depending on how essential performance is for your system, and on whether your list is bound to keep increasing in size, you may want to consider splitting your list into separate lists for e.g. by year or by quarter.