cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tejaswini1234 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
Dual Super User

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

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
Dual Super User

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

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
MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Apps Community!

Power Platform 2020 release wave 1 plan

Power Platform 2020 release wave 1 plan

Features releasing from April 2020 through September 2020

Top Solution Authors
Top Kudoed Authors
Users online (6,894)