Sharepoint 5,000 record view limit and Lookup column

I understand the 5,000 item view limit and what do about it for generally "Viewing" purposes.

How does the 5,000 item limit apply to using a Lookup column to find and store a value from a list that has more than 5,000 items?

My data structure is an "Opportunity" which looks up and stores its  associated Contact from a Contact with more than 5,000 records.


Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Walter CurtisSharePoint AEDCommented:
I lookup column is part of a view and may not show more than the first 5000 items. I honestly imagine a lookup with that many items though. Is there any way to use a people column? Are the contacts all in Active Directory or some other repository that you can filter somewhat?

Good luck...
kflorianAuthor Commented:
The Contacts are currently in a third-party CRM

But surely my use case is common: the need to create a referential link between two lists and one or both of those lists having >5000 items each.

The user will know the List Item (in this case a "Contact") that they need from the Lookup.....start typing and find it. In no situation will the user be wandering a 10,000 item list uncertain what they are looking for.

 Anybody have custom code to share?
Walter CurtisSharePoint AEDCommented:
What you last comment describes is totally different than the first description, therefore things are different :-)

If you lookup column is coming from a third-party CRM the list view limit does not apply. That limit just limits the query results size of the SharePoint list, which also limits what is displayed (viewed).

As you mention, you will need custom code for the lookup column. You may consider using SharePoint Business Connectivity Service, which may have a different name depending upon your SharePoint version. That service would allow you to use a 3rd party CRM data source.

Good luck...
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

kflorianAuthor Commented:
My bad...precision in everything but especially asking questions like this.

The list that will have >5000 records will be exported from a soon-to-be retired CRM and imported into a SP list.

This means I'm going the custom-code route which I suspected might happen.

Thanks for taking the time to respond.
Walter CurtisSharePoint AEDCommented:
Good luck and if you have questions in the future don't hesitate to ask...

btw.. the list view limit can be increased if necessary.
kflorianAuthor Commented:
It's an 0365 subscription.
Keyboard CowboyCommented:
Two approaches I can think of.  
1) use Spservices cascading dropdowns - the first to filter on some field (like cateogry or office or something) and then the second to look up the real field - Or use 3 cascaded dropdowns.  YOu can do this via Javascript in a web part to control the field. Put in a CEWP and point it to the script in site assets - don't try to put the script directly in a CEWP and in a script editor webpart isn't a good idea since you need it for new/edit forms.

That might work - never tried it with > 5000 items
2) Use spEasyForms - which also allows for cascaded dropdowns - free, easy install, and incredibly powerful for doing OOB but custom new/edit/disp forms.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Walter CurtisSharePoint AEDCommented:
Information provided is accurate and correct.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SharePoint

From novice to tech pro — start learning today.