vLookup query error: 5000 items limit

Forums SPJS-Lookup vLookup query error: 5000 items limit

Viewing 11 reply threads
  • Author
    Posts
    • #23623
      Sarah
      Participant

      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

    • #23625
      Alexander Bautz
      Keymaster

      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

    • #23627
      Sarah
      Participant

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

      Thanks Alex!

      Cheer,
      Sarah

    • #29659
      Sarah
      Participant

      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

    • #29672
      Alexander Bautz
      Keymaster

      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

    • #29689
      Sarah
      Participant

      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:
    • #29697
      Sarah
      Participant

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

    • #29699
      Alexander Bautz
      Keymaster

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

      Alexander

    • #29701
      Sarah
      Participant

      Hmm still same issue 🙁

    • #29703
      Alexander Bautz
      Keymaster

      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

    • #29705
      Sarah
      Participant

      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).

    • #29707
      Alexander Bautz
      Keymaster

      Yes, that would fix it for you.

      Alexander

Viewing 11 reply threads
  • You must be logged in to reply to this topic.