Is there some limit if I have a list with 5 lookup fields to the same list? I cannot create a new item!

Question

I am using SharePoint 2013. Is there some limit when you have a list with 5 lookup fields to the Assets Library? My Assets Library has very much files like doc, pdf, jpg, gif, jpeg, wmv etc. There are 10000 files. When I try to create a new item it gives me an error. When I remove one of the five lookup fields it works great. When I remove one of the five lookup fields and add a new lookup field to some other dummy list with 10 items, it also works! So it looks like SharePoint dont like it when I have 5 lookupfields to the Assets Library with 10000 files.

Is this some known problem? And how can I fix this problem because I need 5 lookups to this Assets List.

ps. In SharePoint 2010 it is working OK!

Answer 1

You may be dealing with 2 issues here, both related to throttling:

There is a threshhold limit on lookup columns. by default it is 8. note, people picker columns are lookup columns. You can change this from Central Administration

Central Admin > Manage Web Applications.

Select Web Application > General Settings & Resource throttling

List View Lookup Threshold property and increase.

It's not all roses and sunshine after you do this. Microsoft sets the default at 8 based on performance impact. You can review the limits and impacts here: http://technet.microsoft.com/en-us/library/cc262813.aspx#Throttling

You also have the threshold on items, the default is 2000 for a normal user, and 5000 for an admin. These can also be changed in the same Resource throttling area above, but again will have a potential negative impact to server performance.

Other Potential Workaround(s):

1. Create a custom view in SharePoint designer that can use custom lookup field, you can then filter the lookup so you don't bring back all 10000 items.
2. Use a custom code approach with something like SPServices and JQeury (or a VS solutions) to do the lookups instead of SharePoint. One of the threshold limits allows you to increase the limit returned for developers, and the default is 10000. You could increase this if needed (and you would if you have 10000 items in your list already) but keep the normal values controlled.

HTH