cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tejaswini1234
Level: Powered On

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 Team
Community Support Team

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 Team
Community Support Team

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
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (5,448)