List query column lookup threshold error

New Contributor

Hi all,

(This is for SP Online, not on-site SP)

 

I keep getting this error:

 

"The query cannot be completed because the number of lookup columns it contains exceeds the column lookup threshold."

 

I believe the threshold is 12 for SP Online, and it can't be configured.

 

I execute the query using the GetListItems method.

 

StackOverflow hasn't been much help (I haven't explicitly asked the question on there, though, just from previous questions that have been answered), the only advice to current questions are to reduce the amount of fields in the query (which I can't do), or to make multiple API calls to retrieve the field:value key:value pairs and then merge the data from the payloads - which certainly isn't ideal and I'd like to avoid it if at all possible (unless it's the only way?)

 

Anyone ever had this issue before, and know some way I can edit the query to still return all the necessary field values?

 

Please let me know if you need me to post the query.

 

Thanks!

3 Replies

Hi @SunVolt ,

 

i am afraid that the lookup value threshold of a view is a hard limit that you can not get arround. It was configurable in the on premise Version of SharePoint, but in SharePoint Online you are stuck with maximal 12 lookup columns. 

You can only have 12 lookup columns per view and lookup columns also include all "Managed Metadata Fields" and "Person Fields" (even "CreatedBy" and "ModifiedBy"). There fields are internally also lookups to some hidden lists.

You have three options to deal with more than 12 lookup columns in a view:

1) Remove unnecessary  lookup fields from your view (If you don't explicitly specify a given view, you get the fields from the default view in a query)


2) Create different views with less than 12 lookup columns and merge the results in code.


3) Duplicate some lookup column fields on that list as text columns.

Then on each update of the SharePoint list item have a flow update the value of the text field to the content of the lookup field. 
Then use that field in the view


Best Regards,
Sven

Hi @SvenSieverding ,

Thank you for your timely reply, apologies for not stating it earlier, but the issue isn't to do with the SharePoint UI with Views, but rather within the code itself when programmatically attempting to load all of the columns for the list in a custom app, whereby it's not possible to trim the columns.

Please let me know if you need more clarification,

Thanks again.

Hi @SunVolt,

 

You're welcome.

 

"View" does not necessarily mean UI here but is more like the "select" statement of an SQL query.

 

If you load SharePoint data programmatically you get the fields from the default view per default in your result. But you can change the view fields in your query programmatically if you want to select different fields.

For example you can select the columns  "Title, MyField2 and MyField3" like this,  if you are using a REST API Odata call 

https://<tenant>.sharepoint.com/sites/<site>/_api/web/lists/GetByTitle('List')/items?$Select=Title,MyField2,MyField3


The "GetListItem" Endpoint is a SOAP Endpoint. Here you can define the ViewFields inside of the XML call:

<ViewFields>
   <FieldRef Name='Title' />
   <FieldRef Name='MyField1' />
   <FieldRef Name='MyField2' />
</ViewFields>


If you are using the Client Side Model (CSOM) libraries

CamlQuery camlQuery = new CamlQuery();
camlQuery.ViewXml = "<View><ViewFields><FieldRef Name='Title'/>" + 
                    "<FieldRef Name='MyField1'/><FieldRef Name='MyField2'/>" + 
                    "</ViewFields></View>";

ListItemCollection collListItem = oList.GetItems(camlQuery);

clientContext.Load(collListItem);

 

 

How exactly are you trying to access the list items? Which programming language are you using? Or are you building a PowerApp?


Best Regards,
Sven