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 12 replies, has 3 voices, and was last updated by  Alexander Bautz 4 months, 1 week 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 1 year, 10 months 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

    • #21595

      chgrnet
      Participant

      This CAML does work, and it was in place when the list went over 5,000 again. I have verified that the item being filtered is indexed, and I even create a view in the list to make sure this is the case:

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

      Do you have any other ideas on what to try? I was not there in time to trap any errors, I will try again next time.

    • #21619

      Alexander Bautz
      Keymaster

      Is the value in your [currentItem:ProjectNumber1] field a list item id, or a text value (for example the Title of the list item it links to)?

      Try hard coding the value instead of using [currentItem:ProjectNumber1] to ensure it isn’t an error in the vLookup code that gets the wrong value.

      You may be able to get some more details for debugging if you write this in the URL
      …/Lists/YourListName/DispForm.aspx?ID=123&vLookupDebug=1

      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.