1

Can a Lookup field work with a list of 5000+ items? I have a list of 15000 items and I want to create a lookup column that accesses this list. Normally a lookup column will filter empty rows and return those with data (in this case 100 items - so the dataset is well within the 5000 SharePoint imposed limit). However, I'm receiving the classic "you have over 5000 items" warning message and the lookup fails because I believe it is processing the entire list. Now I've been told that I could write some code to filter the lookup (in order to work with lists over 5000 items), but I'm worried that this will not work on the 15000 item list and that I will be forced to split the list up into smaller lists (<5000 items). Does anyone have any advice?

Any help would be appreciated.

Thanks,

Alex

P.S.

One more thing, we are SharePoint Online site so we cannot change any throttling parameters. The way we have achieved a list of more than 15,000 items is by creating sub-folders to store the items (Microsoft suggestion) within the main list.

Improve this question
4
  • I mean no disrespect but do you honestly expect users to be able pick from a drop down menu that contains this number of items? That's a usability nightmare...
    – Robert Kaucher
    Jun 30, 2013 at 1:47
  • 1
    There are 20 - 400 items in the drop down per customer, sorted in alphanumerical product number order – it’s very fast to select the customer and the associated product. Problem is that there are now 100 customers+ (50 large customers added recently). And the out-of-the-box lookup fails – as it processes the entire list of 15000 items (each customer has their own column with mostly empty rows). BTW, I have seen a dropdown menu with 3000 items running on a SharePoint online site and it worked extremely well - the usability issue is resolved by adopting the correct list naming/sorting strategy.
    – Alex
    Jun 30, 2013 at 22:16
  • @RobertKaucher a large number of items in a selection is not uncommon, and usability can be addressed with cascading selects (e.g. country/state/city) or autocomplete.
    – Christophe
    Mar 26, 2014 at 19:11
  • Christophe, I am aware of that. Based on the question I was imagining a select box with 15000 items in it. Please see my comment to the accepted answer.
    – Robert Kaucher
    Mar 26, 2014 at 19:23

1 Answer 1

Reset to default
3

Add some jQuery to your list form which applies your own lookup using a REST call or similar? This should allow you to filter as needed and can all be done with client site code through SPD so OK in O365. No idea how it will perform with 15,000 rows though.

Improve this answer
8
  • Good idea! Multiple cascading filters and/or some sort of type ahead prompt is going to be the only way to make this work and not just from the perspective of the internals of SharePoint.
    – Robert Kaucher
    Jun 30, 2013 at 1:50
  • Thanks for the suggestion - really interested if anyone has attempted to get a lookup to work with a list with 15,000 items. One other thing - I assume one must make sure the lookup returns less than 5,000 records. Correct? Alex
    – Alex
    Jun 30, 2013 at 21:54
  • Not sure REST has the same limitations - let me know how it goes.
    – Dave Paylor
    Jul 1, 2013 at 8:52
  • Hi Dave, sorry for the delay. Microsoft is now involved and is trying to create a filtered lookup for us. If they succeed I will let you know. This 5000 item issue is a real pain as it impacts everything - including the creation and modification of indexes once you get past this fixed online limit. I would advise people to clearly think out their scalability strategy before deploying SharePoint solutions.
    – Alex
    Jul 16, 2013 at 21:54
  • Alex, thanks for the update. I have since found that REST is also restricted by the throttling limits so that would be no help. The Object Model is probably the only option.
    – Dave Paylor
    Jul 17, 2013 at 10:16

Your Answer

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

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