5

Users & 'Full Control' site collection admin are unable to EDIT items.

Error message reads:

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

I understand how this issue is normally triggered - if more than 8 lookup columns (I've also read of a max of 12) are used, it creates an error. I'm using SP2013.

However, in my scenario, the list in question is long-established and is stable (in terms of development - no new lookup columns are in use). Hence, I'm somewhat puzzled as to what has triggered this error.

I'm using content types, one of these only has 6 lookups in it, I tried to edit one of these but I get the same error.

I did have some Windows Updates install yesterday, I've no idea if that would be the cause. Windows Updates are regular, so normally I'd exclude it as being the cause of anything, but given the otherwise stable nature of the list, I've been trying to think up other possible causes.

Product content type pic:

8 lookups/'person or group' columns

enter image description here

Other EXP content type pic:

Only 6 lookups/'person or group' columns

enter image description here

23/09/2019 - additional info

  • a different farm administrator to me could still edit items, while I could not (despite us having identical permissions). Suggesting he does not have the latest Windows Updates installed yet(?)

  • Following Peter's recent comment regarding workarounds. In my 'OTHER EXP' content type, which has 6 'Lookup' or 'Person or Group' fields, I went in CT settings and hid a lookup field, so only 5 of these field types remain in the edit form; the edit form worked correctly. However, for the 8 field Product content type, this workaround did not help

  • On the server, in Sharepoint Central Administration, I observed that the default number of lookup fields is set to 8. Therefore this setting is somehow being overriden, since the List will not display the edit item form.

  • Not all lists are affected; in a different site collection a list with 7 lookups is still functioning normally.

24/09/2019 - in response to Slaven's comment about database version:

Database version is 15.0.5023.1000:

Sharepoint Farm - 2013, database version 15.0.5023.1000

Further testing

Saved the list as a template with contents, with the intention of trying to recreate it. When I tried to create a new list (including contents) based on this template, I got this (Windows?) error.

Error message - Windows

This website lists lots of error codes for Microsoft tech, wow what a find, specifically it included my error message too. I'm using Windows 7, my colleagues are using newer versions of Windows.

Newer version of Windows required?

|improve this question
  • Is there any People and groups column? SharePoint counts "People and Groups column" as lookup column. – Hitendra Solanki Sep 20 at 10:40
  • Hi Hitendra, I've just added some pictures - I'm pretty sure the number lookup columns is not the issue - even if the person/group columns are included in the count. – Tally Sep 20 at 10:49
  • 1
    We ran into the same problem today where several flows suddenly started generating this error (on query of a list) even though no changes in the flow nor the list definition for an extended period of time. We were able to work around it with limiting the columns by a view, but still disturbing. – Peter Bowers Sep 20 at 15:39
  • 1
    I dont know the sparqube Lookup you are using, but those fields may be different to normal lookup. Try recreating the list with 8 lookups and see does it work, and then with the 8 sparqube lookups as you have above. If with the sparqube it doesn't work for 8 fields you might need to contact vendor for support. – Marek Sarad Sep 23 at 10:58
  • 1
    Can it be the case that someone has reduced the lookup threshold value for the farm to 8 (From larger number before?) If some inexperienced admin would run health test that might have been flagged up; he corrected it not knowing it will affect some of your sites? – Marek Sarad Sep 24 at 13:30
1

I changed the List View Lookup Threshold to 12. The list now works again for everyone. I'm puzzled why it didn't work before, I've read some conflicting info on this - some things I've read explain that any value over 12 consumes more 'system resource' (SQL has to perform a join query - more info here). My item content type only had 9 lookup/person or group columns, so it should have been ok.

List view lookup threshold

On Sharepoint Central Admin>Manage Web Applications>MySharepointWebApplication>General Settings>Resource Throttling. I've changed this value from 9 to 12.

Resource Throttling

Other observations

  • I don't understand the Windows error message (possible red herring).

  • Or why my farm admin counterpart could still edit things that I could not. Sharepoint does sometimes offer deceptive error messages.

|improve this answer
1
+100

Can it be the case that someone has reduced the lookup threshold value for the farm to 8 (From larger number before?) If some inexperienced admin would run health test that might have been flagged up; he corrected it not knowing it will affect some of your sites?

Re: "a different farm administrator to me could still edit items, while I could not (despite us having identical permissions). Suggesting he does not have the latest Windows Updates installed yet(?)" - This might be because he is a member of administrators (BuildIn\Administrators) group on the front end server (the 20K admin threshold limit takes those users in this group into account not farm admin"

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.