cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

PowerApps Writing back to SQL?

I have an app which is up and running and writes back to Excel Online hosted on OneDrive. We do not find this a secure way as we have to give users of the Power App edit access to the Excel Online as well to successfully make the comment/write back.

 

We want to replace this with a On-Prem SQL Server Table. I am unsure of what rights are needed for the SQL Table besides reader and writer and how to go about replacing this Excel Online connection and how to go about making this happen. 

 

Any documentation/advice would be really helpful.

21 REPLIES 21
Highlighted

Hey John, quick Q for ya.

 

You mention having a PowerApps call a flow which then calls the SP.  From what I've read, Flow does not communicate back to PA when it completes, so you aren't able to verify if the flow was successful or if it got hung up.

 

Do you have any solutions on how to get the UI to update in these instances or do your apps just let the user know it was submitted and call it good?

Highlighted
Advocate III
Advocate III

You're right in as much as there's not a huge amount of communication back. So yes I've had one or two specific issues when there's been a failure. In terms of knowing that the procedure has completed you could do a couple of things, that are a bit hacky but if you were worried about the execution, you could stick a "last updated at" timestamp field in the record table, update this as the last point of the SP, and refresh the data source after the flow execution. If the timestamp doesn't update then the procedure likely fell over on the DB side. Would that work?
Highlighted

thanks Shane, will check this out tonight after I email all business users why the Power Apps custom visual in Power BI wont work until notice

Highlighted
Advocate III
Advocate III

My suggestion was a bit of a raw principle, but as I sat and thought there are a few ways you could do this i guess. So you could create a table that stores execution of SP'S. In your SP open with creating a record and flagging a Procedure started column. (Set the record ID as max(ID) +1 and store that value within the SP so it can be called at the end. Make sure you put this first part in a transaction and commit before executing the main part of the SP so that you definitely have a record of the proc starting even if it falls over later . At the end update the original record in a Procedure complete flag column. You theft have a full record of procedures failing to fully execute. Useful for debugging. You could even store all parameters passed to the proc in this execution table as this might also.help debugging.

As to how you get this info back to the PA user, it's never going to be as ideal as a Proc failed message immediately after execution of the flow, but it's a start.

View solution in original post

Highlighted

I have a PowerApp that, when I press a button, it executes a stored procedure via a Flow - the stored procedure inserts a number of records into a table. After this job is complete (takes 2-4 seconds), I would like the Powerapp to refresh the datasource (;Refresh([Datasource])). But for now, the Refresh command executes before the SP (and flow) has finished inserting the records.
Any idea as to how I can use this (or another) method somehow to make the Powerapp refresh the datasource only when the SP has completed?

Highlighted

Two options jump out to me

 

1. Use a timer in PowerApps to wait 5 seconds to refresh the page. PowerApps Timer Control

2. Return a variable from Flow to PowerApps when your update is done and then refresh your data source. PowerApps Flow Intro (Video isn't an exact match but does show you how to return a variable if that is new to you)

 

Shane

Shane - Microsoft MVP, YouTube, and PowerApps Consulting for when you are in a bind to get this fixed quickly. And finally we now have PowerApps Training
Highlighted

@Shanescows: Thanks for the tip. Great video by the way, really helpful.

But do you know if passing a variable back to Powerapps from Flow, will actually make Powerapps wait for the variable to get returned before executing the next part of the function (the refresh part)? - Pseudo: Set(VariableName,StoredProcedure.run).Return;Refresh(Datasource)

Start a timer after the flow starts. Then have the timer refresh as soon as the variable isn't blank anymore. 

Shane - Microsoft MVP, YouTube, and PowerApps Consulting for when you are in a bind to get this fixed quickly. And finally we now have PowerApps Training
Highlighted

Ahh, I have seen the light. Great tip - will try that out. Thanks.

Highlighted

Update:

What a truly great tip, I have now converted all my "Insert checklist" functions to use a MS SQL stored procedure instead and it seems to be working very well so far. It's many times faster and much more reliable.

 

Principle:

  1. Create stored procedure on SQL to do the job
  2. Create Flow to run the stored procedure - with "Respond to Powerapps" step
  3. Powerapps button OnSelect:  Set(varInsertChecklistItemsComplete,SP_InsertChecklistItems.Run([VariusParameters]).sp_result);
    UpdateContext({StartTimer: true})
  4. Timer with "Start" = StartTimer, Repeat = true, Duration = 1000
  5. OnTimerEnd = If(varInsertChecklistItemsComplete = "done";tmrSPFinished.Repeat=false;UpdateContext({StartTimer: false});Refresh('[Datasource]'),false)

I have been trying to find a way to implement this for quite some time, so a great day today indeed. Thanks again.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (3,259)