1

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

enter image description here

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

enter image description here

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

enter image description here

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

enter image description here

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

0

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.
8
  • i do not agree with you.. Power app gallery are able to load any number of SharePoint items, even the 2 Million items. but it will load them in patches as you scroll down or left you can reach all items.. so i am can use SharePoint and Power Apps on large lists,, the only issue i am facing is with the lookup fields. now i found a workaround of storing the Parent ID as a Single Line of text instead of using lookups.... but i am asking to see other ideas for managing the lookup between large lists.. but for me SharePoint and Power Apps are working perfectly on these large lists.
    – john Gu
    Oct 31 '21 at 21:29
  • why you want to show 2Million items on the screen. even when i write pure .NET web application which connect to SQL server.. i do the paginating on the server.. so if you click on Next the next batch on items will be shown.. and the Power app galleries do have a pagination... when you the end of a scrolling the gallery will automatically fetch the next patch of 100 items till you get all the 2 Millions items.. try it at your end...
    – john Gu
    Oct 31 '21 at 21:49
  • so i do not see that SharePoint & PowerApps do not suites large lists,, but the idea is to have enough experience in both technologies to be able to work with large lists
    – john Gu
    Oct 31 '21 at 21:50
  • You are right about the galleries. I forgot they support paginations. But then again, how does it help you work with a list of 2m items? How are you going to force the gallery to show you one millionth's item, for example? Are you going to scroll the gallery for 6 hours to get there? Oct 31 '21 at 21:53
  • "why you want to show 2Million items on the screen". Sorry, but I never said I want 2 million items on the screen. In my answer I explain that you have to use Begins with and Equals conditions to filter a small portion of your datasource. Oct 31 '21 at 21:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.