The number of lookup columns it contains exceeds the lookup column threshold

  • 26 September 2014
  • 4 replies
  • 57 views

Badge +1

Hello,

 

I have an issue in my process. I'm creating an item inside a sharepoint list. I can get it using the getbyID method.

The problem comes when I'm trying to update using the Update method the process crashes and return this error:

 The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold enforced by the administrator.

 

I know that you can update the lookup threshold in the Web application configuration but what I don't get is:

 

--> Why does the Get method work without any lookup threshold problem and the Update method not?

 

 Thanks

Ramon Torras

http://www.ramontorras.net

 


4 replies

Badge +7

Hello Ramon,

 

I was able to find a customer who had the same error message and issue as you. It appears to be a SharePoint issue, which may be resolved using the steps and advice from these articles:

 

  1. http://sharepointlk.wordpress.com/2012/04/09/the-query-cannot-be-completed-because-the-number-of-lookup-columns-it-contains-exceeds-the-lookup-column-threshold/
  2. http://stackoverflow.com/questions/14222890/sharepoint-the-view-cannot-be-displayed-because-the-number-of-lookup-and-workfl
  3. http://social.technet.microsoft.com/Forums/sharepoint/en-US/ba240e0c-2435-4215-bb70-41b374634172/the-query-cannot-be-completed-because-the-number-of-lookup-columns-it-contains-exceeds-the-lookup?forum=sharepointadminprevious

While I know this does not specifically answer your bottom question, hopefully it will help you out in at least getting rid of the error. 

 

Regards,

Alyssa

Badge +2

Hi Ramon,

 

I can provide some more details to the issue.

 

I came accross the same error when I tried to update a SharePoint list item using the CSOM. I was confused  by the error because the list has no pure lookup column and only seven person fields. After I took a closre look at the list structure I realized that the cusomt list consist of 24 lookup columns. I was aware of the seven person fields, including the standard Author and Editor field, but it eluded me that the two taxonomy fields are also lookup columns. Of course these are only nine column. These are the other lookup columns:

 

TaxCatchAll

TaxCatchAllLabel

FileRef

FileDirRef

Last_x0020_Modified

Created_x0020_Date

FSObjType

SortBehavior

UniqueId

SyncClientId

ProgId

ScopeId

MetaInfo

ItemChildCount

FolderChildCount

 

I marked the bold columns because they supprised me. I didn't know that the Last Modified and Create Date are treated as lookups and I didn't know that the taxonomy catch all fields even existed. They probably support the updates of the other taxonomy fields in the list.

IMO the error is caused when the number of updated lookup columns exceeds the threshold. As a workaround I implemented my own CSOM threshold value when I update a list item value. I count the number of lookup properties which are updated and force an list update if the threshold is exceeded. Unfortunaly some internal fields like taxonomy catch all are also updated which is the reason why my threshold is lower than the configured on. I my case it takes four partial updates to update the list item.

 

I hope I could provide some more details even so I'm not sure that you can make use of a similar workaround.

 

Regard,

Daniel Krüger

Badge

Hi ,

 

We are facing the same issue .I have just used send email notification action and when I try to manually start the workflow, we are facing the issue. We indexed the columns ,even then facing the same issue.

We do not have the access to change the lookup configuration in sharepoint admin section.We are using the SharePoint online.

 

Is this the issue with the k2 product?

 

Can any one help to fix this issue.


17197iBD722653FDC8761F.png
Badge +1

Hello Pradeep,

 

If you use SharePoint Online, the treshold limit is 8 and you can not modify this limit.

It's not a K2 issue, it's a SharePoint limitation. You will have to deal with that so what I would advice is:

- use 'person' columns as less as possible (it counts as a lookup for the treshold)

- use text fields or number fields for saving ID or references.

 

Cheers,

Ramon Torras

Reply