question

johnjohn-0472 avatar image
0 Votes"
johnjohn-0472 asked johnjohn-0472 commented

Managing/Storing the relation info between 2 LARGE sharePoint lists, should we use lookup fields or Single line of text field

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:-

145322-loading.png

  • 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:-

145295-child.png

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

not-supported
child.png (53.6 KiB)
loading.png (14.3 KiB)
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Power Apps is currently not supported in the Q&A forum.

Please start a new discussion via the Power Apps Community so that you can get dedicated support on this issue.

Thank you for your understanding.


If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



0 Votes 0 ·

@EmilyDu-MSFT Thanks for the note but if you read my question in full, you will see that i am asking about using Single Line of text field Vs using Lookup field for managing the relation between 2 SharePoint lists .. so i think my question is pure SharePoint question.. i just gave Power Apps as an example.
Thanks

0 Votes 0 ·

In my opinion, you could manually store the parent ID in a text column. But text field is not suitable to manage the relation between two SharePoint lists.

0 Votes 0 ·
Show more comments

0 Answers