vLookup to a child list with over 5,000 items

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

Viewing 4 reply threads
  • 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 7 years, 2 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!

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