cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

How to solve the query can not be be completed because number of lookup column it contains exceeds

In sharepoint list  5 person column , 3 lookup column ,4 single lined column

2 ACCEPTED SOLUTIONS

Accepted Solutions
Dual Super User II
Dual Super User II

Re: How to solve the query can not be be completed because number of lookup column it contains exceeds

SharePoint has a hard coded lilmitation on how may Joins it can do when building the results of a Query.  The most common use of a Join would be a lookup column.  The only real way to solve your problem would be to decrease the number of Joins required for the query.  Each person and lookup column is at least one Join.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

Highlighted
Community Support
Community Support

Re: How to solve the query can not be be completed because number of lookup column it contains exceeds

Hi @Tejaswini1234 ,

Based on the error message that you provided, I think you have faced a Lookup threshold limits issue within SP List.

 

The user @Rfla has faced similar issue with you, please check the response within the following thread:

https://powerusers.microsoft.com/t5/General-Discussion/SharePoint-too-many-lookup-columns-cause-quer...

 

Since SharePoint 2013 Post June 2013 CU update, the Default List view Lookup Threshold has been revised to 12. The Person column is actually a LookUp type column.

Each lookup column in a list view causes a join with another table. Each additional lookup column in a view increases the complexity of metadata navigation and list view queries. By number of joins, I mean the number of Lookup, Person/Group, or Workflow Status fields that are included in the query. So for example, if you have a view that displays 6 lookup columns, and filters on another 3 distinct lookup columns then by default that view won't work, since the List View Lookup Threshold is 8, and the view is attempting to use 9 lookups.

Please check the following article for more details about the Lookup threshold limits issue within SP List:

https://blogs.msdn.microsoft.com/spses/2013/12/02/sharepoint-20102013-list-view-lookup-threshold-unc...

 

I agree with @Pstork1 's thuoght almost. Currently, the only way to resolve this issue is that decrease the number of Joins required in the query.

Note: Each LookUp column would cause one Join at least. Nested LookUp column query would cause multiple Joins. 

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Dual Super User II
Dual Super User II

Re: How to solve the query can not be be completed because number of lookup column it contains exceeds

SharePoint has a hard coded lilmitation on how may Joins it can do when building the results of a Query.  The most common use of a Join would be a lookup column.  The only real way to solve your problem would be to decrease the number of Joins required for the query.  Each person and lookup column is at least one Join.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

Highlighted
Community Support
Community Support

Re: How to solve the query can not be be completed because number of lookup column it contains exceeds

Hi @Tejaswini1234 ,

Based on the error message that you provided, I think you have faced a Lookup threshold limits issue within SP List.

 

The user @Rfla has faced similar issue with you, please check the response within the following thread:

https://powerusers.microsoft.com/t5/General-Discussion/SharePoint-too-many-lookup-columns-cause-quer...

 

Since SharePoint 2013 Post June 2013 CU update, the Default List view Lookup Threshold has been revised to 12. The Person column is actually a LookUp type column.

Each lookup column in a list view causes a join with another table. Each additional lookup column in a view increases the complexity of metadata navigation and list view queries. By number of joins, I mean the number of Lookup, Person/Group, or Workflow Status fields that are included in the query. So for example, if you have a view that displays 6 lookup columns, and filters on another 3 distinct lookup columns then by default that view won't work, since the List View Lookup Threshold is 8, and the view is attempting to use 9 lookups.

Please check the following article for more details about the Lookup threshold limits issue within SP List:

https://blogs.msdn.microsoft.com/spses/2013/12/02/sharepoint-20102013-list-view-lookup-threshold-unc...

 

I agree with @Pstork1 's thuoght almost. Currently, the only way to resolve this issue is that decrease the number of Joins required in the query.

Note: Each LookUp column would cause one Join at least. Nested LookUp column query would cause multiple Joins. 

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Users online (4,463)