cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kervin
Level: Powered On

limitations powerApps

I am new to using PowerApps,
I have a problem with an Apps with which I have to interact with a Sharepoint list of about 30.000 records.
I am limited to 2,000.
So I thought, at the start, to create a collection, with this instriction;
'ClearCollect (Collection3; {name: List_short_name.Name}; {FirstName: List_short_people.Prenom})'
but at first sight that I make a count on this collection, it also takes only 2,000 records!

What should I do to be able to exploit my 30,000 lines ???

I already posted about it but I did not get an answer. is this an insoluble problem ??

Thank you in advance.

7 REPLIES 7
Anonymous
Not applicable

Re: limitations powerApps

Kervin
Level: Powered On

Re: limitations powerApps

So PowerApps is only able to work on 2000 reccords ???
If this is the case the tool only allows to develop a very limited type of application ...

Anonymous
Not applicable

Re: limitations powerApps

yes....

unfortunately we have a limitation here... I tried to break this putting the table on a collection but only allowed me to go to 4000...

No idea if it is possible to do a work around in this case.

Super User
Super User

Re: limitations powerApps

Hi @Kervin and @Anonymous,

 

Forgive the preaching but before you decide whether Powerapps fits your needs, it is critical that you understand Delegation and why it is necessary.  Powerapps is designed to be used on phones and tablets that have limited memory and storage as well as on more robust systems like a desktop computer.  This could not be done without delegating certain functions to run at the server side, like Filter and Sort. Other functions are meant to be used at the local level on the device hosting the app. To get the full picture and the implications I would refer you to two documents and a blog post by @Meneghino:

 Many of us have used Sharepoint for very large lists, some of mine exceed 30,000 items to create apps that run businesses.  When working with large datasets, it is all about designing around the limitations imposed by the platform (ie. Excel, Sharepoint, Sql server, etc) and which Powerapps functions support delegation so that you can bring down a manageable set of data to a local device. I hope this helps.  There are many of us here who will be happy to help you solve issues that arise should you choose to design Powerapps.  

Highlighted
Super User
Super User

Re: limitations powerApps

Hey all, in addition to what @Drrickryp so awesomely posted, I thought I could chime in with the thought that considering how much data you need is pretty important.

 

Can you write a filter that will pull in enough data to do what you need or do you need all that data? If you are developing a purely online solution, you may be able to pull in a filtered set that does the trick. If you are developing an offline solution or in certain situations, you may need more. There are ways to get around the limit but there are costs to consider, like storage and loading times, as well as general complexity issues you may encounter later on. 

 

I have a method that I use to pull in 10,000 rows of maintenance data to a phone-based app. The reason for this is that the app is designed for offline use and needs to be pretty much self-contained. Plus, some of the maintenance tasks are annual, so ensuring that enough data is on-hand to keep the last maintenance record isn't guaranteed in 2000 records. Offline development is a tenuous task in PowerApps as I don't think it was really meant for this kind of use and it's real-world ability to do so isn't exactly solid yet. Robot Frustrated

 

That said, here is some code to show how it can be done as well as illustrate the complexity involved. This is production code from an app that has been in use for over a year. I am in the process of overhauling the app due to some changes in our maintenance program, but this bit will be reused. Also, this idea was not mine; I borrowed and adapted it from someone else, though sadly I don't remember who or where from. (If you read this and it was you, please comment so I can thank you!)

 

// I refresh the data source to ensure I am getting the most recent data
        Refresh('[dbo].[MaintenanceRecord]');

// Then I start a clean collection, sorting by ID in my case
        ClearCollect(MaintenanceRecordCollection, Sort('[dbo].[MaintenanceRecord]', ID, Descending));

// This variable tracks the ID where the collection ended so it knows where to start the 
// next time around.
        UpdateContext({MinID: Min(MaintenanceRecordCollection, ID)});

// I then check if the first collection is at a full 2000 records. If so, I filter out everything above 
// that last ID and collect another round of data to the collection. If the first collection has
// less than 2000 records, there wasn't anything more to collect and the If statement ends. I then update the 
// variable that stores the minimum ID that I have collected.
        If(CountRows(MaintenanceRecordCollection)=2000,
            Collect(MaintenanceRecordCollection, 
                Filter(
                    Sort('[dbo].[MaintenanceRecord]', 
                        ID, 
                        Descending
                    ), 
                    ID < MinID
                )
            )
        );
        UpdateContext({MinID: Min(MaintenanceRecordCollection, ID)});

// I do the same as before, this time checking to see if there are 4000 items. If not, it is 
// done. If so, it repeats the process to collect up to 2000 more items. It again collects 
// the minimum ID.
        If(CountRows(MaintenanceRecordCollection)=4000,
            Collect(MaintenanceRecordCollection, 
                Filter(
                    Sort('[dbo].[MaintenanceRecord]', 
                        ID, 
                        Descending
                    ), 
                    ID < MinID
                )
            )
        );
        UpdateContext({MinID: Min(MaintenanceRecordCollection, ID)});

// Lather, rinse, repeat, adding 2000 items each time.
        If(CountRows(MaintenanceRecordCollection)=6000,
            Collect(MaintenanceRecordCollection, 
                Filter(
                    Sort('[dbo].[MaintenanceRecord]', 
                        ID, 
                        Descending
                    ), 
                    ID < MinID
                )
            )
        );
        UpdateContext({MinID: Min(MaintenanceRecordCollection, ID)});

// Pretty exciting stuff, right? Lots of repeating essentially the same thing, just changing the measuring point. If(CountRows(MaintenanceRecordCollection)=8000, Collect(MaintenanceRecordCollection, Filter( Sort('[dbo].[MaintenanceRecord]', ID, Descending ), ID < MinID ) ) );

// Lastly, I save the collection locally. SaveData(MaintenanceRecordCollection, "LocalMaintenanceRecord");

 

Now, in my case I use the ID but there may be a better field to use. At the very least, this is a proof of concept and could spurn some ideas for what your solution may look like. Also, while this is possible, it may not be beneficial for your use case. Considering your needs and the hardware your users will be using is important, as well as how much complexity is manageable for you and, if you are lucky enough to have one, your team. If you don't have a team (I fall into this category) you could stand to think through the implications this may have on your successor and the organization at large. Documentation is a difficult aspect in PowerApps and while you can comment the code now, knowing where to look to find the right comments can be a challenge.

 

PowerApps is a great tool and capable of many creative solutions. And again as @Drrickryp stated, there are many people on these forums who are willing to help others solve issues they encounter. Best of luck to you in your endeavors!

Kervin
Level: Powered On

Re: limitations powerApps

Hello everydody, (and espacely  Wyotim)

I will try the solution Monday at my office and I tell you if it's ok for me.

Anyway a big thank you in advance to all

Super User
Super User

Re: limitations powerApps

@wyotim's solution for creating large collections has two other great values.  Collections are immune to delegation limits.  This allows you to use the full array of Powerapps functions on your data.  The second is that collections are cached so the operations performed with them are lightning fast.  If you are running powerapps on a desktop computer with minimal limits on memory and storage they are often the way to go.  With handheld devices, you may reach limitations imposed by the hardware and have to work with delegatable functions to limit the data brought down from the web.  

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 189 members 6,023 guests
Please welcome our newest community members: