vLookup query error: 5000 items limit

[Sarah]

Hi there,

I have noticed an error in my Forms where I’m using vLookup. This vLookup gets items from another List which have just passed the 5000 item limit threshold. Now every time I’m going to the tab which shows the vLookup field, there is an error coming up:

Error in query for the field “vLookupEmails”: The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.

I found that in 2015 you had updated the spjs-utility.js to handle large lists (SPJS-Utility updated with support for querying large lists) but our spjs-utility.js version is 1.328 and the latest I saw on the download repository was 1.257

Any suggestions?

Thanks

[Alexander Bautz]

Hi,
To fix this you must index the column you use in the query in your vLookup configuration. Go to list setting in the child list and add it to the Indexed columns.

SPJS-utlity.js version is not relevant, but it is now part of the DFFS package and not downloadable separately.

Alexander

[Sarah]

Great! Adding the query field to the indexed columns did the trick.

Thanks Alex!

Cheer,
Sarah

[Sarah]

Hi Alex,

The same issue came up just now again. I have already added the columns I use in the vLookup query as indexes to the list but still get the error.
We are still on version 2018-11-10 (DFFS v4.4.3.55) so I’m not sure if we should update to latest etc or if there is another fix?

Cheers,
Sarah

[Alexander Bautz]

How does your query part of the vLookup config look like? – if you use “or” it will not always play well with > 5000 items.

You can try adding “&vLookupDebug=1” to your URL like shown below and post a screenshot of the output (over the form) when loading your vLookup:

.../YourList/DispForm.aspx?ID=123&vLookupDebug=1

Alexander

[Sarah]

Yes the query has a “or” in it :/
I have temporarily fixed it by deleting some old test items from that list and it is working again (of course) – but it is critical to the business hence i had to fix it temporary so they can use it for now. I have attached the “&vLookupDebug=1” output anyways tho – and will do again after work when I can add some items to make it over 5000 again once noone is working on it.

Attachments:

1. 

   vLookupDebug.png
   

[Sarah]

Ok – its the same screenshot when its over 5000 items (just tested). Please advise. Thanks

[Alexander Bautz]

Hi,
You can try changing the order of the fields in the query – put RelatedWorkorderID first and Related_x0020_Case second.

Alexander

[Sarah]

Hmm still same issue 🙁

[Alexander Bautz]

The problem is how SharePoint processes this query and I think the problem is the lookup column Related_x0020_Case. When your Case list exceeds 5000 items the built in SharePoint lookup column stops working.

I’m not sure if you have used the _vLookupID / _vLookupParentID fields to set up the parent – child relationship before, but it would most likely fix your problem.

It will of course not work for old items where you don’t have the parent _vLookupID in the child items _vLookupParentID field.

Look at this setup example: https://spjsblog.com/vlookup-for-sharepoint/vlookup-setup-example-for-sp-2010-and-2013/

Alexander

[Sarah]

You are right about the “OR” being the problem. When I change it to “AND” it works fine.
The lists are setup with _vLookupID / vLookupParentID fields as per the example.
What I might try is to use powershell to fill in RelatedWorkorderID for every item in the case list so that I can remove the Related_x0200_Case lookup from the query (as it is only for older items – not used in new items).

[Alexander Bautz]

Yes, that would fix it for you.

Alexander

[Author]

Posts