limitations powerApps

[Kervin]

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.

[Anonymous]

I asked the same here:

https://powerusers.microsoft.com/t5/General-Discussion/Working-With-Collections-ultrapassing-the-lim...

[Kervin]

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]

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.

[Drrickryp]

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:

• Understand delegation in a canvas app at https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview  
• Delegatable data sources in canvas apps at https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-list 
• Where do I store my data at https://baizini-it.com/blog/index.php/2017/08/29/powerapps-101-data-storage/

 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.  

[wyotim]

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.

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]

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

[Drrickryp]

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

[OsmandFernando]

@wyotim Thank you for much for this code. I tried it but this gives me only the last 2000 records. In my data set with SharePoint I have 2010 lines but it gives me last the last 2000 items. From items 1 to 10 missing, is that how this works?

Osmand

[wyotim]

Hi @OsmandFernando, are there any delegation warnings in the code you are using (the blue squiggles under an equals sign)? I am wondering if there is a non-delegable field in there. This is assuming you have the app set to the maximum delegation limit of 2000.

Otherwise, how many of those code chunks are you using? You would need one for every group of the delegation limit size that you want to pull in. For instance, if you want to pull in 2500 rows of data and your delegation limit is set at 500, you would need 5 of those code chunks. The cut off is by design in the sense that it should only pull in the minimum amount of data needed.

Feel free to post what you have and I would be happy to try and sort through it with you!