Managing/Storing the relation info between 2 LARGE sharePoint lists, should we use lookup fields or

Valued Contributor

I have 2 SharePoint lists; Parent list & Child list. Now inside the Child list i need to store the Parent ID. and seems i have 2 approaches:-

1) Use a Single Line of text to store the Parent ID
2) Use a Lookup field to store the Parent ID

Now i found the following:-

1) Using a Single line of text will allow us to have more freedom on managing the relation specially for large lists, but with the cost that we need to manage the relation by ourselves. For example we need to make sure that if the user manually enters the Parent ID inside the Child list, that this parent ID actually exists inside the Parent list.

2) Using the lookup field, will manage the relation out of the box, but comes with the cost that this lookup field will not work on large SharePoint lists in these 2 scenarios:-

  • In my case the Parent list has 2 million items >> where the Lookup field inside the Child list was not able to retrieve the Parent ID inside the SharePoint modern create/edit form, where the Lookup will keep loading forever, as follow:-

 

 

  • Also in case we are doing the CRUD operations for the Child list using Power Apps, then Power Apps will raise this error on the Lookup field since it is referencing the Parent list which have more than 5,000 items:-

 

 

So now my question is, since i will have large SharePoint lists for the Child and Parent + i am going to use Power Apps to manage the CRUD operations for both the Parent & the Child lists.. so is there any harm if i am going to store the Parent ID inside the Child list using a single line of text instead of using the Lookup field? and is there any other approaches for managing the relation between 2 SharePoint list when these 2 list are considered large lists (each list contain 2 million records)?

Thanks

Hint. i already defined the fields as indexed fields inside the Parent list.. but this did not solve the issue of using Lookup fields in Large lists

0 Replies