4
  • I have a Parent list which contain over 2 million records:-

enter image description here

  • and inside another list (Child list) i have a lookup field which reference the above parent list, as follow:-

enter image description here

  • and inside the Parent list i added the Title as an index column, as follow:-

enter image description here

  • now when i access the Child list's create/edit modern list forms, i will get this warning on the lookup field (which is understandable) :-

enter image description here

  • but when i start typing in the Parent Title inside the lookup field, the field will keep loading as if it is retrieving data, but for more than 2 hours now i am still getting the loading image and never get the Parent record..

enter image description here

Any advice ? per my knowledge that defining the correct Index on the Parent list should allow us to reference the list using Lookup field, even if the list has more than 5,000 records... but seems this is not working in my case? am i missing something?

Thanks

EDIT So what we can do to manage the relation between large lists (lists which have more than 2 million items)? is storing the Parent ID inside a free text field instead of lookup.. and manage the relation by ourselves (by code) a way to go ?

Improve this question

2 Answers 2

Reset to default
3

Having that many items will make the lookup unusable.

In the Modern UI the dropdown will never finish loading. Not sure, but I am guessing 5000 items is the limit for Modern UI.

The classic UI might load the lookups in chunks of 100 items at a time (need to verify) and will either take very long time to finish or also fail.

Overall, lookup columns should not point to the list with more than 200-1000 Items (Not a hard limit). You can have more items, but the user experience will keep degrading with the large numbers even before you hit some hard limit.

Improve this answer
5
  • thanks for the helpful reply. I remember when the Parent list was having around 2,00,000 ++ items the lookup column on the child was able to reference the items without any issues. but last week we have extracted items from excel sheets and the Parent list size almost get 10x its size. and the lookup stop working (keeps loading forever). so my main question is that i have 16 lists which have relation between each other, and those 16 lists are expected to grow to around 1 million each sooner or later..
    – John John
    Commented Oct 26, 2021 at 17:11
  • . so what is the best approach to maintain this relation other than using lookup fields? thanks
    – John John
    Commented Oct 26, 2021 at 17:12
  • thanks for the reply.. so what is the best way to manage the relation between our large lists (lists which have more than 2 million items)? is storing the Parent ID inside a free text field instead of lookup.. and manage the relation by ourselves (inside the code) a way to go ?
    – John John
    Commented Oct 29, 2021 at 17:22
  • One of the best ways to do it is not use SharePoint at all. These amounts of data are better stored in a relational database even Excel or Microsoft Access is better than SharePoint lists. If there is a way to use a different system for this - it's better to do it in this case.
    – Denis Molodtsov
    Commented Oct 29, 2021 at 20:22
  • SharePoint lists are capable of storing 30 millions++ items.. but the issue is with the lookup rather than with SharePoint.. is storing the Parent ID inside a free text field instead of lookup.. and manage the relation by ourselves (inside the code) a way to go ?
    – John John
    Commented Oct 30, 2021 at 1:26
1

Similar points to Denis Molodtsov's answer.

Per my knowledge, by default, when parent list of a lookup column exceeds 5000 items, it will make the lookup column deliver an error or the dropdown of lookup column will never finish loading.

There are no solutions for this issue other than trying to remove records from your parent list.

Same post in the Microsoft Q&A.

Improve this answer
5
  • no the lookup was working well when the parent list had 250,000++ items .. also i previously open this question on Microsoft Q&A forum @ docs.microsoft.com/en-us/answers/questions/496656/… and i were told that adding index should solve this issue.. which is correct, because when the Parent list had 250,000++ items the lookup did not had any issue.. but until the list reaches 2 million items the lookup will keep loading forever when we try to search for items..
    – John John
    Commented Oct 27, 2021 at 10:59
  • second question , i have 16 lists which have relation between each other, and those 16 lists are expected to grow to around 1 million each sooner or later..so what is the best approach to maintain this relation other than using lookup fields? thanks
    – John John
    Commented Oct 27, 2021 at 10:59
  • 1
    Adding index cannot solve this issue. The feasible way is to divide the list into several lists with less than 5000 items.
    – Emily Du - MSFT
    Commented Oct 28, 2021 at 10:21
  • really.... of course this is not a feasible way .. you want us to have 400 lists to manage the 2 millions records.. what about storing the Parent ID in a free text and manage the relation by ourselves ?
    – John John
    Commented Oct 29, 2021 at 17:19
  • 1
    You could manually store the parent ID in a text column.
    – Emily Du - MSFT
    Commented Nov 1, 2021 at 9:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.