PowerApps Writing back to SQL?

[nirvana_moksh]

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.

[bbrazeau]

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?

[johnmarlowe]

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?

[nirvana_moksh]

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

[johnmarlowe · Accepted solution]

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.

[Debug_Bug]

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?

[Shanescows]

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

[Debug_Bug]

@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)

[Shanescows]

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

[Debug_Bug]

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

[Debug_Bug]

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.