vLookup to a child list with over 5,000 items

Forums vLooup for SharePoint vLookup to a child list with over 5,000 items

This topic contains 10 replies, has 3 voices, and was last updated by  Alexander Bautz 5 months ago.

  • Author
    Posts
  • #15085

    chgrnet
    Participant

    Hello –

    Is there a trick to get a vLookup to work on a parent list where the child list has exceeded 5,000 items? I have created indexes out of the query items, but it is still not bringing back the data (giving an error). Please let me know if there is anything else I can try, or maybe if there is something I am doing that will keep it from working.

    Thanks for any assistance you can provide.

    Christine

  • #15087

    Alexander Bautz
    Keymaster

    What kind of column is it you use in the query to get the child items? – I need to know the field type in the child list, and also see a screenshot of the query in vLookup.

    Alexander

    • #15089

      chgrnet
      Participant

      The field type if a lookup, could that cause a problem? I will send you a screen shot if this is not the issue.

  • #15093

    Alexander Bautz
    Keymaster

    If you send me the screenshot, I might be able to rewrite the query to make it work (if you have indexed this column).

    Alexander

  • #15105

    Alexander Bautz
    Keymaster

    Thanks,
    Try using this in the “Write your own CAML-query” field:

    <Where>
    <And>
    <Eq><FieldRef Name="VideoProjectNumber" LookupId="TRUE" /><Value Type="Integer">[currentItem:ProjectNumber1]</Value></Eq>
    <Eq><FieldRef Name="StreamRequestType"/><Value Type="Text">VOL</Value></Eq>
    </And>
    </Where>

    This requires the “currentItem:ProjectNumber1” being a “list item id” from the source list. If the field “ProjectNumber1” is a lookup column, you can get the item id number from it and use it in the CAML.

    Alexander

    • This reply was modified 5 months, 1 week ago by  Alexander Bautz. Reason: Added a bit more details about the type of "number" used in the query
    • #15300

      chgrnet
      Participant

      I apologize for the delay in trying this code, and thank you for taking the time to write it.

      The “currentItem:ProjectNumber1” is a single line of text field, will that work? I did try the code, it did not product any errors, but it is not bringing back results either.

    • #15311

      Alexander Bautz
      Keymaster

      Yes, if “ProjectNumber1” is a single line of text column containing a “list item ID”, and “VideoProjectNumber” is a Lookup column where you can find this same “list item ID” it should work as long as the “VideoProjectNumber” lookup column has been indexed.

      If you bring up the developer tools (hit F12 > Console) – do you see any errors there? – also change to the Network tab – any red “lists.asmx” calls there?

      Alexander

  • #15107

    AdamP
    Participant

    Possibly not the answer you’re looking for, but a quick workaround in the short term may be to get (or request) the list view threshold increased – the default OOTB threshold is 5000 items, but that’s a threshold, not a hard boundary
    SharePoint software boundaries and limits
    We have increased ours to 50,000 items without any performance issues.

    As for querying the child list itself – this post might be of use; StackExchange

    • #15302

      chgrnet
      Participant

      This would be an ideal solution, but not one I have access to at this time. Thank you for sharing it with me!

You must be logged in to reply to this topic.