Inside our Power App customized form, the SharePoint Lookup field which referecne a list that contain 2 million records is not working

Question

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

Answer 1

Power Apps is not SharePoint. It's a separate service. It can connect to SharePoint as a datasource though. As a result, when creating SharePoint forms using Power Apps, we end up with a lot more restricted than using SharePoint alone.

• All SharePoint restrictions apply
• Power Apps have a lot of restrictions of their own. For example, You can't easily work with arrays containing more than 2000 items.

As a result, I would not even try forcing your Power Apps to somehow work with a list containing over a million items.

However, feel free to try these workarounds. Spoiler alert: then won't work very well.

Workaround #1: Use Starts with for searching

• Index a SharePoint search field that you will use for searching.
• In Power App create a new text box field that you will use for searching.
• Create a gallery that will display filtered items.
• The gallery will connect to the SharePoint Lookup list and filter items using Starts with condition. Or equals condition. None other filtering methods like contains will work.

So, users will search for an item they want to select, then click on it in the gallery, then it's your responsibility to grab the value from the gallery and somehow save it later.

Workaround #2. Load SharePoint List into memory

This method will not work with such a large list like yours. But perhaps, you can filter it down so that you are left with no more than 10K items.

• Read TOMASZ POSZYTEK's "OVERCOME 2000 ITEMS LIMIT USING POWER APPS COLLECT FUNCTION" article
• Create custom ID field. You have to do it because the OOB ID field has restrictions in Power Apps.
• Calculate number of iterations like so:

Set(
    firstRecord,
    First('Large List')
);
Set(
    lastRecord,
    First(
        Sort(
            'Large List',
            ID,
            Descending
        )
    )
);
Set(
    iterationsNo,
    RoundUp(
        (lastRecord.ID - firstRecord.ID) / 500,
        0
    )
);
Collect(iterations, Sequence(iterationsNo,0));

• Create collection, where each item is a number of an iteration:

ForAll(
    iterations,
    With(
        {
            prevThreshold: Value(Value) * 500,
            nextThreshold: (Value(Value) + 1) * 500
        },
        If(
            lastRecord.ID > Value,
            Collect(
                LargeListSP,
                Filter(
                    'Large List',
                    ID_val > prevThreshold && ID_val <= nextThreshold
                )
            )
        )
    )
);

I tried this approach on a list with 100K items and it was super slow. I think, I had to wait for at least 1-2 minutes before I downloaded that many items in memory. Then I tried opening this app from a mobile device and the speed was 10 times slower. Loading even 10K items from Samsung S9 took ages.

Summary

Power Apps + SharePoint is not a good combinations when dealing with large lists. Find a way to never load more than 500-2000 items in your Power app. If you do need large volumes of data then you probably need different technologies:

• SQL Azure
• Azure Cosmos DB
• Azure Services
• SPFx
• etc.