0

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.

  • 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 '13 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 '13 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 '14 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 '14 at 19:23
2

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.

  • 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 '13 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 '13 at 21:54
  • Not sure REST has the same limitations - let me know how it goes. – Dave Paylor Jul 1 '13 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 '13 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 '13 at 10:16

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.