I have recently came across the error – “5566 – Lookup columns exceeds the threshold enforced by the administrator” while browsing the InfoPath forms on the SharePoint Site using the users with the “Domain Users” active directory permission. My curiosity of this problem got increased as I was able to browse these InfoPath forms using the users logged in with the “Domain Admins” active directory privileges.
I had a SharePoint list with 1 lookup field and 10 person/group fields. As many of us are aware that by default, SharePoint installation configures each web application list view threshold to 8. You can access this setting from Central Administration -> Application Management -> Manage Web Applications. Select the web application hosting your site collection or site and select the General Settings -> Resource Throttling. From this page, you can maintain the list view thresholds and it specifies the maximum number of Lookup, Person/Group, or workflow status fields that a database query can involve at one time. Please note that this setting doesn’t apply to the SharePoint users with the “Domain Admins” active directory privileges. It applies to only general end-users with the “Domain Users” active directory privileges.
What I really didn’t know is what columns were considered as lookup columns. All I knew until this moment is only Lookup fields were considered as a Lookup columns in the list view threshold limit. Based on the error message, it was obvious that if I increase the lookup column threshold for the SharePoint web application through central admin site, users with “Domain Users” won’t have any issues accessing the InfoPath forms on the browser. The real issue here is should I increase the threshold or find the alternative solution.
As you create the SharePoint Lists, the list view threshold limits number of lookup, person/group, or workflow status columns can have in specific views. Please note that I have mentioned here views. SharePoint list doesn’t limit you to have 8 columns in the list. You can create more than 8 lookup and person/group columns as long as your view doesn’t show more than 8 columns. But, if you have more than 8 columns, list throttling takes in place behind the screen causing performance degradation.
The real Problem starts when you are consuming SharePoint list in the client applications like SharePoint Workspace, Microsoft Access, or InfoPath including InfoPath Form Services. Let’s say, If you have customized the InfoPath forms based on the SharePoint list and created a InfoPath Views based on the SharePoint fields and later on use in the InfoPath Web Part, InfoPath will throw an error if users with “Domain Users” AD permission accesses the InfoPath form views. The reason for this is even though InfoPath form view might won’t show all the fields, InfoPath form itself trying to synchronize all the InfoPath form fields with the underlying SharePoint list. it means in my scenario where I have 1 lookup field and 10 person/group fields, it tries to query more than 8 fields with lookup or people/person group columns. In this case, the above error of the “Lookup columns exceeds the threshold enforced by the administrator” shows up. For the office synchronization, SharePoint list threshold limits the number of lookup and person/group fields. Please note that workflow status columns used only in the SharePoint Views. Since workflow status columns are not considered as a List Column for the specific list, it gets excluded from the threshold limits for the offline clients or office apps synchronization.
To resolve this issue, basically there are really only two options. If you can find more, let me know.
1 – Increase the threshold on the Central Admin Site for the given web application. Downside of this approach is since you are changing these settings at the web application level. For performance reasons, SharePoint limits the number of lookups allowed when offline clients try to sync. If you change these settings, it will apply to all the site collections and sub sites underneath it causing potential performance issues.
2 – Try to limit the total number of workflows, lookup, and person/group fields less than or equal to number of threshold settings. Please remember that every list or document library has two in-built people/group fields called created by and modified by. So, in reality, if you would like to use the lookup, person/group columns in the SharePoint Views and Client applications like SharePoint workspace, InfoPath, and Microsoft access and if your threshold settings are 8, you can’t have more than 6 fields with lookup or person/group fields. Now, that’s what you call harsh reality.
Hopefully this post will give you balance views of two sides of 8 lookup column list threshold limit and you will able to make educated decision.