Unanswered

Collect() a table to SQL Server - Is it making calls for each record?

(0) Share

Report

Posted on by August

Hi,

I have data in a table on a MS SQL Server. I am collecting data in PowerApps and adding those records to the server table. The data i am collecting is pretty straightforward 4 colums and an integer in each. The number of records can vary between 2 and 300.

sqlTable: ID (autoincrement), col1, col2, col3, col4, Date (getdate())
collectionTable: col1, col2, col3, col4

For this i am using a simple:

Collect('[dbo].[Table]';collectionTable)

The time it takes to complete this collect() rises incrementaly with the number of records. Collecting more than 30 records and the wait is rather unpleasent. Looking at the network traffic, it looks like its making a call for each record of the table. I thought the whole idea of using Collect() was that it only made a single server call?

Is there anything in the SQL table that affects collect()s ability to do a single call? Konstraints on the table or such?
If not, is there a better way to this?

Thanks in advance,

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Meneghino 6,949 on at

Like (1)

Report

Hi @August
Unfortunately there is no easy fix for updating multiple records with one call, but please vote up this idea:
https://powerusers.microsoft.com/t5/PowerApps-Ideas/Patch-multiple-records-with-one-server-call-instead-of-multiple/idi-p/74261

Was this reply helpful? Yes No
Verified answer

Meneghino 6,949 on at

Like (3)

Report

Actually I have thought ot a work-around, but have not tested it. Basically, instead of using Collect to insert your data into SQL using the built-in functions (Collect or Patch, which both work poorly with multiple rows) then use Flow to parse JSON data instead, either in Flow itself or with a stored procedure in SQL Server.

For example:
1) In PowerApps, use the concatenation functions to create a JSON string with your multiple records to insert (hold that thought for later)
2) In SQL Server create a stored procedure to parse the JSON string to insert records into your DB table using the OPENJSON function
3) In Flow accept a string input from PowerApps and pass it as a parameter to the stored procedure called from SQL Database
4) In PowerApps, add the Flow and supply the string from step 1 as a parameter
5) In PowerApps, remember to Refresh your data source to see the changes to the table, as these will no longer be automatically reflected

As an added bonus, you can return the result of the operation from SQL to Flow via OUTPUT parameters, and from Flow to PowerApps via the Return Response action. See here for an example.

Please let me know if you need more guidance on any of the steps

Was this reply helpful? Yes No
August 6 on at

Like (0)

Report

Thanks Meneghino,

I was thinking about similar workarounds myself. Even something as simple as just converting it to a mail and letting Flow do the rest from there. I will look into your suggestions and try the JSON way. Thank you for your help.

Was this reply helpful? Yes No