cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
StrangeUser
Frequent Visitor

Retrieve 20K record - Power Apps

Hi,

 

We need to load a 20k record to the collection. We are storing data in the Dataverse table. it's more than 20K records. I need to get and load data to the collection. Currently, I am getting 2K records. Any idea on this.

 

1 ACCEPTED SOLUTION

Accepted Solutions

Ok,  Then I guess we are back to Power Apps, but I still have some doubts whether you'll be able to send that much data to Power Automate.  Remember its going to transfer the data as a JSON array and that's going to be huge.  But here's what I think you will have to do.  To build the collection you'll need to build it in stages where each stage is below the max record count (I assume you've upped it to 2,000). The easiest way to do that will be to use a delegable function, with the view, to limit the number of records returned to less then 2,000.  Then do multiple calls pulling in up to 2,000 records at a time.  For example, one approach I've used is to filter the view using the FullName and StartsWith(). Then call that with a ClearCollect and StartsWith 'A'.  Follow that with 25 more Collect statements and startswith B-Z.  That should give you your full collection.  But it will take time and sending it to the Power Automate may timeout.  But its worth a try.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

11 REPLIES 11
Drrickryp
Super User
Super User

@StrangeUser 

What is the formula you are using to create the collection and add the desired record?

StrangeUser
Frequent Visitor

Hi @Drrickryp ,

 

I have created the View in Dataverse. So I am using that in Power Apps, it contains around 20K records.

 

StrangeUser_1-1653465661827.png

I am trying to load all those records in collection. So I can pass the data to Power Automate to create a excel.

 

Drrickryp
Super User
Super User

@StrangeUser 

I don't do anything with Power Automate so I can't help you there. @Pstork1 would more likely be helpful here.

C-Papa
Super User
Super User

Hi, you don't need to collect the data in PowerApps to send to PowerAutomate to then create the excel. Instead if you set you trigger as PowerApps and then query the Dataverse table within PowerAutomate using the List rows connector, for example, you can then create a .csv table / excel from the data returned from the query. 

First, passing all that data to Power Automate probably won't work.  Is there a particular reason why you don't just access the data directly in Power Automate rather than trying to pass it from Power Apps?

 

Second, even if you get all the data in Power Automate I don't think you'll be able to add it to an Excel Table.  Power Automate can't create Excel Tables that large.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Hi @Pstork1 @C-Papa ,

 

The reason I am using Power Apps is ,we need to get the Contacts table data which is in a different environment. We cannot connect to different environment in Power Automate, but it's possible in Power Apps.

If we load 20K record in collection , we can split and pass the data to Power Automate to generate a excel or CSV file.

If you use the Dataverse (legacy) connector you can attach to a table in a different environment from Power Automate. Type Dataverse in the flow actions search box and make sure you pick the one marked as legacy.

image.png



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Hi @Pstork1 ,

 

Thank you for your quick response, but I tried this too. but it's throwing an error. (Error : The action 'List_rows' has an aggregated page results size of more than '211766929' bytes. This exceeded the maximum size '209715200' bytes allowed.)

Note : Contact table has more than 100+ columns. But I need only 10 columns only. In this Legacy connector, we don't have column selection also. ($select)

Ok,  Then I guess we are back to Power Apps, but I still have some doubts whether you'll be able to send that much data to Power Automate.  Remember its going to transfer the data as a JSON array and that's going to be huge.  But here's what I think you will have to do.  To build the collection you'll need to build it in stages where each stage is below the max record count (I assume you've upped it to 2,000). The easiest way to do that will be to use a delegable function, with the view, to limit the number of records returned to less then 2,000.  Then do multiple calls pulling in up to 2,000 records at a time.  For example, one approach I've used is to filter the view using the FullName and StartsWith(). Then call that with a ClearCollect and StartsWith 'A'.  Follow that with 25 more Collect statements and startswith B-Z.  That should give you your full collection.  But it will take time and sending it to the Power Automate may timeout.  But its worth a try.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (5,318)