vLookup to a child list with over 5,000 items

[chgrnet]

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

[Alexander Bautz]

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

[chgrnet]

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

[Alexander Bautz]

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

[chgrnet]

Files 1-4.

Attachments:

1. 

   vLookupIssue01Query01.png
   

2. 

   vLookupIssue01Query02.png
   

3. 

   vLookupIssue01Query03Totals01.png
   

4. 

   vLookupIssue02Misc01.png
   

[chgrnet]

Files 5 & 6.

Attachments:

1. 

   vLookupIssue03AddNewItem01.png
   

2. 

   vLookupIssue03AddNewItem02.png
   

[Alexander Bautz]

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, 11 months ago by Alexander Bautz. Reason: Added a bit more details about the type of "number" used in the query

[chgrnet]

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.

[Alexander Bautz]

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

[chgrnet]

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.

[Alexander Bautz]

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

[AdamP]

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

[chgrnet]

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

[Author]

Posts