PowerApps: Getting More Than 2000 Items (Using Sequence)

Hello Friends,

Welcome back with another post on PowerApps. As all we know that PowerApps fetches only 500 items from data source at a time. This limit can be enhanced up to 2000 through File >> Settings >> General >> Data row limit. In real scenario, the total number of items in any data source can be more than 2000 usually. Then how can we get all items in PowerApps?

To resolve the above issue, we can do some manipulation to get all the data from data source as we have discussed in our previous article.

PowerApps: Getting More Than 2000 Items From SharePoint List

Above code logic works on static mode where we implement logic on a pre assumption of number of items. But the logic will get fail once the number of items increases beyond that limit. We have to change the logic again and again and republish the app in above logic model to overcome this situation. So, in order to overcome this situation, we have derived a dynamic model to get all items from data source. Let's see, how-

1. Create a blank canvas app.
2. Add 2 Gallery controls and name them-
1. GalleryDailyTasks_1 - It will be used to display all items from data source. Bind "AllTasksCollection" collection to the items property.
2. GalleryTotalIterations_1 - It will be used to display the Iterations of min-max created dynamically. Bind "TotalIterations" collection to the items property.
3. 
   
   
   
4. Now, we will add code on "OnVisible" action of Screen "ScreenAllRecWithSequence_1"
5. First of all, we will create a blank collection which will be used to store all items from data source-

1. ClearCollect(
       AllTasksCollection,
       []
   );
   

6. Then we will add code to get the first item from data source. It will be used to get the ID of first record. Then we will store its ID in a local variable. (Don't get confused from RecordID column used here. It is nothing but replica of ID column filled using Power Automate on ItemCreate. An index is created on this column.)-

1. Set(
       FirstRecord,
       First(
           Sort(
               DailyTaskSheetForPowerApps,
               RecordID,
               Ascending
           )
       )
   );
   UpdateContext({FirstRecordID: FirstRecord.RecordID});
   

7. Now, we will get the last record of data source. This will be used to get the last ID of item in data source so that we can calculate total number of records in data source and will iterate accordingly-

1. Set(
       LastRecord,
       First(
           Sort(
               DailyTaskSheetForPowerApps,
               RecordID,
               Descending
           )
       )
   );
   

8. Now we will find out how many iterations, we have to do in order to get all records from data source. For this, we are assuming that in one iteration, we will fetch 500 items.

1. Set(
       MaxIteration,
       RoundUp(
           (LastRecord.RecordID - FirstRecord.RecordID) / 500,
           0
       )
   );
   

9. We have rounded up the result to next value as in case, if there is any reminder of "(LastRecord.RecordID - FirstRecord.RecordID) / 500", then it will be left from fetching in case if we do not round up.
10. Now, we will create a collection which will contain 2 column "min" and "max". This collection will have total number of items equal to the total number of iterations we have calculated in previous step "MaxIteration". This "min" and "max" value of each item will be used to filter items from data source and in order to make calculations simple, we have added the FirstRecordID here already-

1. ClearCollect(
       TotalIterations,
       AddColumns(
           AddColumns(
               ForAll(
                   Sequence(MaxIteration),
                   Value
               ),
               "min",
               (FirstRecordID + (Value - 1) * 500)
           ),
           "max",
           (FirstRecordID + Value * 500)
       )
   );
   

11. The last part of the code logic where we will use above collection to fetch items from data source. Here we are using TotalIterations collection to perform Filter action on data source and storing the result in AllTasksCollection-

1. ForAll(
       TotalIterations,
       Collect(
           AllTasksCollection,
           Filter(
               DailyTaskSheetForPowerApps,
               RecordID >= min && RecordID < max
           )
       );
       
   );
   

12. This way, we have to not define any static ID or any static logic to get all items from data source. By increasing number of items in data source, this logic will handle those things automatically.
13. You may refresh the data source on App OnStart.
14. There will be some warnings of Delegation. This is because, we have used First function which does not support delegation. But we have no other option to get the first record. You may ignore it.
15. 
    
    
    
    

1. Below is the complete code-

2. ClearCollect(
       AllTasksCollection,
       []
   );
   Set(
       FirstRecord,
       First(
           Sort(
               DailyTaskSheetForPowerApps,
               RecordID,
               Ascending
           )
       )
   );
   UpdateContext({FirstRecordID: FirstRecord.RecordID});
   Set(
       LastRecord,
       First(
           Sort(
               DailyTaskSheetForPowerApps,
               RecordID,
               Descending
           )
       )
   );
   Set(
       MaxIteration,
       RoundUp(
           (LastRecord.RecordID - FirstRecord.RecordID) / 500,
           0
       )
   );
   ClearCollect(
       TotalIterations,
       AddColumns(
           AddColumns(
               ForAll(
                   Sequence(MaxIteration),
                   Value
               ),
               "min",
               (FirstRecordID + (Value - 1) * 500)
           ),
           "max",
           (FirstRecordID + Value * 500)
       )
   );
   ForAll(
       TotalIterations,
       Collect(
           AllTasksCollection,
           Filter(
               DailyTaskSheetForPowerApps,
               RecordID >= min && RecordID < max
           )
       );
       
   );
   

3. Below is the screenshot to show the entire data fetched from data source and the iterations collections data-
4. 
   
   
   
5. Please remember that- 
1. Getting all records from SharePoint in PowerApps may lead to performance degrade (if number of records are quite large).
2. Every call to SharePoint takes some time so as the number of calls gets increase, it will take more time to get the data loaded and hence will put your action in hang mode.
6. So, please try to avoid as much as possible to get all records in PowerApps. Review your process to avoid such workarounds.
7. That's all for this topic.

With this, I am concluding this post.

Happy Coding !!!

Will see you again with some new topics.

Stay Safe !

Stay Healthy !