List query column lookup threshold error

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!

SvenSieverding
Aug 30, 2023
Hi SunVolt ,

in that case i would rewrite the mapping module to take in multiple lists with different properties and merge them together in a custom object and pass that to your API. The CSOM entities are lazily loaded and I don't think that you can instantiate them manually.

But perhaps you can get around all of that. You only seem to be loading two elements. If you only need a few items it might be easier to fetch them by ID

List myList = context.Web.Lists.GetByTitle("Listtitle"); ListItem listItem1 = myList.GetItemById(21); ListItem listItem2 = myList.GetItemById(25); context.ExecuteQuery();

Try that. You might get arround the view limitation like that. A view and fetching an item directly behave differently.

Best Regards,
Sven

7 Replies

olegkmail
Copper Contributor
Nov 26, 2024
If I have 10 lookup / person fields - NO issues.
After adding the 11th lookup / person field - there are issues with getting data in Power Apps.
With in the SharePoint list - NO issues, working fine having ALL the fields in any Views.
NOTE (as I understood): The limit is 12 like lookup / person fields (10 + 2 system ones - "Created by", "Modified by")
Found - "Another option is to use indexed columns and filtering to keep the view size for the list to 5,000 items or less."
If you have MORE then 12 like lookup / person fields (10 + 2 system ones - "Created by", "Modified by"), INDEXING doesn't help ! - verified.
SO, the only way to avoid errors in Power platform - to have LESS than 12 like lookup / person fields (10 + 2 system ones - "Created by", "Modified by").
SvenSieverding
Bronze Contributor
Dec 29, 2022
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
- SunVolt
  Copper Contributor
  Jan 03, 2023
  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.
  - SvenSieverding
    Bronze Contributor
    Jan 03, 2023
    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

Forum Discussion

List query column lookup threshold error

7 Replies

Resources