Our use of cookies

We use cookies to tailor your experience, gather analytics, and provide you with live assitance. By clicking "Accept" or continuing to browse our site you agree to the use of cookies. For more details please read our Cookie Policy.

Export lists with large number of lookup columns

SharePoint limits the number of lookup columns retrieved in a single query; by default, this value is 8 in most versions of SharePoint On-Prem, 12 in SharePoint OnLine (see this article for the latest values); this value is called the lookup column threshold.
 
A list with 15 lookup columns, therefore, cannot be displayed in its entirety in the SharePoint UI, nor queried via the APIs, but only via a view which does not exceed the number of lookups defined by the threshold.

The threshold can be increased in Central Admin for SharePoint On-Prem versions, but it is not recommended. It is not possible to increase this threshold in SharePoint online in Office365.

Up to version 5.2, SQList could not export lists that exceeded the lookup column threshold, and the only work around it was to increase it.

In this version, we have changed the way SQList queries lists, so that it is no longer affected by the lookup threshold, making it possible to export without the need to adjust the threshold in Central Admin. A new setting is now available in the "Site Configuration", where you can specify the value of the lookup threshold for the site, and SQList will adapt its queries to ensure it does not exceed that limit.

If you are unsure about the value to use, use the default values as described above; however, for optimal performance, you should ensure that the value you specify matches the value defined in Central Admin (your SharePoint administrator should be able to confirm the correct value).

It is important to stress that all lookup values will be exported to the SQL table, not just those that are within the threshold.