SharePoint Lookup field which referecne a list that contain 2 million records is not working

[johnjohn123]

I have a Parent SharePoint online list which contain over 2 million++ records, as follow:-

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

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

now when i customized the Child list's Create/Edit form using Power App, i will get these warning + the lookup field on runtime will be disabled:-

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

[johnjohn123]

I have a Parent SharePoint online list which contain over 2 million++ records, as follow:-

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

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

now when i customized the Child list's Create/Edit form using Power App, i will get these warning + the lookup field on runtime will be disabled:-

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

[WarrenBelz]

@johnjohn123 ,

This seems to be the same as this post. To save you time, this simply will not work as (among other reasons) you cannot index a Lookup column and they will not work over 5,000 records. Lookup columns are generally bad ideas anyway with Power Apps and Distinct() is not Delegable over 2,000 records - why would you want 2m column lookup anyway?

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[johnjohn123]

@WarrenBelz  i do not want the lookup of 2m records.. this is not my main concern.. my main concern is how i can maintain the relation between the Parent and the child lists if am not going to use Lookups ?where those 2 lists have over than 2M items on each of them?

In other words, i have a child list which reference a Parent list.. so where/how i need to store this info:-

That the Child item with this ID is related to the Parent item with this ID ?

For example:-Is storing the Parent ID as a free text instead of using Lookup a valid/good workaround?

thanks

[WarrenBelz]

@johnjohn123 ,

You post referenced a Lookup type column in SharePoint, not a Lookup in Power Apps (two completely different things). You certainly should be able to do a LookUp in Power Apps providing source and target matching columns are either Numeric or Single Line of Text and are indexed in SharePoint. Also Lookup type columns in SharePoint are generally unnecessary and will cause you many issues in Power Apps, when the same function can simply be done in Power Apps.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[johnjohn123]

@WarrenBelz 

You post referenced a Lookup type column in SharePoint, not a Lookup in Power Apps (two completely different things).

Yes i am talking about lookup field in SharePoint and how it will get mapped inside Power Apps... and i already provided a screen shot of the column.. so not sure why you are confused about what i am talking about.

You certainly should be able to do a LookUp in Power Apps providing source and target matching columns are either Numeric or Single Line of Text and are indexed in SharePoint.

I did not get your point, i already provided a screen shot of the error that i will get when i try to open a Power App form which contain a SharePoint lookup field and this lookup field reference a list which contain 2Million records..

[WarrenBelz]

@johnjohn123 ,

I think the confusion may be elsewhere - it was clear to me from your screenshot what you wanted to do and I posted that you simply cannot do a lookup column in SharePoint referencing 2 million records - I will reiterate that to avoid any further uncertainty. 

I also offered some other advice on the need for lookup columns in the first place as well as an alternative of doing a Power Apps lookup matching indexed text or numeric fields (the parent ID stored as a numeric field is what I do). Theoretically this should work, but I do not have 2m records to test it on. 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[johnjohn123]

@WarrenBelz  so you mean inside SharePoint we should store the Parent ID as a free text inside the Child list and never use Lookups?

[WarrenBelz]

@johnjohn123 ,

Assuming that you are referring to Lookup type fields in SharePoint, then yes that is my advice -  I have not used them for years and we operate a Sharepoint-based system with over 200 lists and apps and hundreds of thousands of records and files.

A LookUp() in Power Apps can be used on a matching field in the Child list - as I mentioned a good option is a numeric field in the Child list containing the ID of the Parent. If you have not read it already, I have a blog on this.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[johnjohn123]

@WarrenBelzi think there is a miss-understanding between us. I am talking about Lookup field type inside SharePoint, i have never mentioned any think about Lookup() inside Power App.. so again my question is, if we have 2 large SharePoint lists  and we want to manage their CRUD operations using Power Apps.. then inside SharePoint how we need to manage this relation, as managing this relation using Lookup field will not work if the Parent list exceeds 5000 items. thanks

[WarrenBelz]

@johnjohn123 ,

As I posted, you cannot do this - Lookup columns cannot be indexed in SharePoint, therefore cannot function with more than 5,000 items. Also SharePoint is not a Relational Database (it will not accept Delegation on relational queries), so when using with Power Apps, it is best to simply regard the Lists as separate items.