Forum Discussion

SunVolt's avatar
SunVolt
Copper Contributor
Dec 29, 2022
Solved

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 thresho...
  • SvenSieverding's avatar
    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

SvenSieverding's avatar
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