1

I am trying to fetch data from a view in SQL Server using PowerApps. However, the gallery is not showing more than 100 rows and my view has almost 100000 rows. I found out that the gallery uses lazy loading and so will load only a few items at a time. I need all these data in the gallery to write it to a csv file and store it in the Azure blob storage.

Does anyone know how to fetch large amounts of data into my gallery and write to a file? Thanks in advance.

antony.ouseph.k
  • 907
  • 2
  • 15
  • 28

2 Answers2

1

I would not use PowerApps as a proxy for this type of job. Power Automate is a better option. A PowerApps gallery is a wonderful UX tool, but not so good for large data display. Power Automate has a mobile app, so your users could still provide some kind of input (select the job to run, etc.).

If you insist on using PowerApps, you'll likely still need Power Automate to create the .csv file.

Pseudocode for a Flow to handle this:

  • Trigger: Button w/ options
    • Options: User-friendly keywords for your SQL stored procedures/queries such as "Print all users" or the like
  • Action1: Switch
    • If user selected option "X", If user selected option "Y", etc.
  • Switch1: SQL Get Rows
    • Enter query parameters
  • Action2: Select
    • Map response values from SQL Get Rows to individual Key/Value pairs
  • Action3: Create CSV Table
    • Use output from Select action above as input
    • Automatic headers
  • Action4: Create Azure Blob from .csv
  • Duplicate similar functionality for the other Switch case

enter image description here

SeaDude
  • 3,725
  • 6
  • 31
  • 68
  • Thanks for the answer. Let me give it a go. Unfortunately, we are stuck with PowerApps as of now. We are to create a CSV file containing all the data when the user clicks an export button. This export need not happen all the time. I was hoping if there was a way to get all the data from DB and store in a collection variable directly and use it to create a csv. New to PowerApps. – antony.ouseph.k May 08 '20 at 04:55
0

If you are bent on getting all data into PowerApps, do this. Note: Your user will not enjoy scrolling 100k records. Ideally omit the Gallery altogether or just give them a preview of the data.

OnSelect of the "Export" button or whatever you have:

Concurrent(
    ClearCollect(col1, Filter(dbo.sqlDB, And(ID >= 1, ID<=2000))),
    ClearCollect(col2, Filter(dbo.sqlDB, And(ID >=2001, ID<=4000))),
    ClearCollect(col3, Filter(dbo.sqlDB, And(ID >=4001, ID<=6000))),
    ClearCollect(col4, Filter(dbo.sqlDB, And(ID >=6001, ID<=8000)))
);
ClearCollect(colCombined, col1, col2, col3, col4)

Set the Gallery.Items property to colCombined.

There are other ways to get the last ID and the first ID, determine the number of iterations, then loop through in batches of 2k records at a time.

See this post from the old days where we discussed just such a use case.

SeaDude
  • 3,725
  • 6
  • 31
  • 68