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
Highlighted
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
Highlighted
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
Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

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

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (7,514)