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