For managing/storing relation between 2 LARGE sharePoint lists, should we use lookup fields or Single line of text field

Question

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 manully 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 App, 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

Answer 1

Using a simple text field instead of a lookup field is the right decision when working with Power Powers. Even though it comes at a cost, it's a better alternative to not having your Power App work at all. Also, even if you didn't get a lookup-related error, I can't imagine users would be able to work with a dropdown list that contains 2 million items. Even 20.000 items is too large for any kind of dropdown list. 2 million is completely beyond its capacity. Even Google Chrome only supports up to 10.000 choices in a dropdown.

Options to manage parent/child relationships

• When saving your "child" form, include validation that prevents users from referencing a parent ID that does not exist. Users will have no search capability and they will have to know what ID to type. Then you will search the parent list using the equals condition and confirm the item exists.
• Provide basic search to your users so that they can see possible options. The only option available to you is to have a combo of a text box and a gallery. Users will have to type either the beginning of the field value or the exactly field value and the gallery will display a short list of filtered options. Unfortunately, it's a very narrow scenario that does not fit most cases. With 2 million items users would expect to be able to search by "contains" condition. But this is not supported by large SharePoint lists.
• Create a Flow that is triggered from a Power App. The app will pass search terms to the Flow and the flow will query SharePoint search API by means of an HTTP action. Then Flow will return the results back to the Power App. Power App will show the search results in a gallery. The user will have to select one of the available options from the gallery. This option is a little slow, but does not have the limitations of query delegation on of the "begins with" query restrictions of the OOB SharePoint lists. One big disadvantage of this approach is that you will have to wait for the SharePoint search to index your parent list.