cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bbrazeau
Level: Powered On

Re: PowerApps Writing back to SQL?

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
Level: Powered On

Re: PowerApps Writing back to SQL?

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
nirvana_moksh
Level: Powered On

Re: PowerApps Writing back to SQL?

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
Level: Powered On

Re: PowerApps Writing back to SQL?

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

Debug_Bug
Level: Powered On

Re: PowerApps Writing back to SQL?

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?

Super User
Super User

Re: PowerApps Writing back to SQL?

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
Debug_Bug
Level: Powered On

Re: PowerApps Writing back to SQL?

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

Super User
Super User

Re: PowerApps Writing back to SQL?

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
Debug_Bug
Level: Powered On

Re: PowerApps Writing back to SQL?

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

Debug_Bug
Level: Powered On

Re: PowerApps Writing back to SQL?

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
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 322 members 5,822 guests
Please welcome our newest community members: