cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NETGUY
Helper I
Helper I

Using Power Automate within Power Apps Executing Stored Procedure with Variables

Ok So far the research suggests that in order to pass input fields from Power Apps into a stored procedure one must first create a Power Automate flow. Here is what I have So far. I have not found a tutorial that allows me to pass in TWO (2) parameters from Power Apps into Power Automate for the stored procedure.

 

Power Automate:

I selected my stored procedure that requires 2 input values - Start Date and End Date. I selected the appropriate fields from the PowerApp Options for these two fields. I then created a Response which I am ASSUMING I will need this to then display the results in the Power App. (Not Sure how but I saw this from another post online)

 

Then hit save.

 

Screen Shot 2020-08-04 at 8.48.13 AM.png

Next, I created a PowerApp, added two date picker fields, and a button. I then clicked on Action - PowerAutomate.

From there, I selected the Power Automate that I created in the first step.

As you can see from below Its now asking me to complete the Flow Parameters in the formula bars. I have NO IDEA what to create as the formula. Any help would be greatly appreciated.

Screen Shot 2020-08-03 at 7.32.11 PM.png

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @NETGUY ,

Based on the issue that you mentioned, I think you have some misunderstanding on firing a flow from a canvas app.

 

Firstly, within your flow, the 'Ask in PowerApps' dynamics content from the PowerApps Trigger button is used to define the parameters which you want to pass from your canvas app. You could use the 'Ask in PowerApps' dynamics content to define any number of parameters which you want to pass from your canvas app.

 

In addition, the 'FlowName'.Run() formula is a action function, which could only be executed in behavior property of control. You could not bind the 'FlowName'.Run() formula to a Gallery directly. On your side, you should bind this formula to a button, set the OnSelect property of the "Search" button to following:

ClearCollect(                   // save the returned results from your flow into a collection
             RecordsCollection,
             'PowerApp->Executes....'.Run(EndDatePicker.SelectedDate, StartDatePicker.SelectedDate)
)

Then set the Items property of the Gallery to following:

RecordsCollection

then within the Gallery, add several Labels to display the column value within the RecordsCollection, set the Text property of Label to following:

ThisItem.partnerID
ThisItem.partnerBillingInterval

...

...

Please check and see if the following blog would help in your scenario:

https://powerapps.microsoft.com/en-us/blog/return-an-array-from-flow-to-powerapps-response-method/

 

Please try above solution, hope it could solve your problem.

 

Regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6

If you want to pass multiple parameters from power apps to flow, add a compose step for each parameter and for the value set it to the prompt in power apps option. Then you will have multiple parameters in your flow and you can use the output of the compose steps in your stored procedure 

Interesting. Not sure where in the process to place the compose steps. I would Assume just prior to the EXEC Stored Procedure task so this way each of the Compose input parameters can then be used in the EXEC SP input parameters. 

 

The question is why wouldn't I skip including the Compose step and just use the same "Prompt in Power App" option within the Execute Stored Procedure step?  (see screen shot)

Seams like adding the Compose piece is only adding another layer to what could be done in one step.

LOL - Now I am more confused. Sorry

 

Screen Shot 2020-08-04 at 9.50.36 AM.png

Compose is the swiss army knife of variables. The difference is that you can pass any values from the power app and use them in your subsequent steps. You would put the compose steps at the beginning of the flow after the trigger

https://crmtipoftheday.com/1257/learn-compose-action-in-flow/

Gotcha. Ok looks like I got the Button in PowerApps to execute the Stored Procedure in PowerAutomate and the values are also being passed into the SP. 

 

For the next step, I was not sure how to display the date in Power Apps. Here is what I have, I used the following link as a guide to try and get this to work:

https://www.c-sharpcorner.com/article/how-to-get-data-from-on-premise-sql-server-database-in-powerap...

 

Apparently a few people have commented to the post indicating that they are unable to display the results in Power Apps.

 

I created a Vertical Gallery on my App however the results data results from Flow will not Populate if I select the Date Source Power Flow like in the below:

 

Screen Shot 2020-08-04 at 12.15.42 PM.png

 

Again Here is my Power Automate process. What more must be done to display the results?

Screen Shot 2020-08-04 at 12.16.59 PM.png

Hi @NETGUY ,

Based on the issue that you mentioned, I think you have some misunderstanding on firing a flow from a canvas app.

 

Firstly, within your flow, the 'Ask in PowerApps' dynamics content from the PowerApps Trigger button is used to define the parameters which you want to pass from your canvas app. You could use the 'Ask in PowerApps' dynamics content to define any number of parameters which you want to pass from your canvas app.

 

In addition, the 'FlowName'.Run() formula is a action function, which could only be executed in behavior property of control. You could not bind the 'FlowName'.Run() formula to a Gallery directly. On your side, you should bind this formula to a button, set the OnSelect property of the "Search" button to following:

ClearCollect(                   // save the returned results from your flow into a collection
             RecordsCollection,
             'PowerApp->Executes....'.Run(EndDatePicker.SelectedDate, StartDatePicker.SelectedDate)
)

Then set the Items property of the Gallery to following:

RecordsCollection

then within the Gallery, add several Labels to display the column value within the RecordsCollection, set the Text property of Label to following:

ThisItem.partnerID
ThisItem.partnerBillingInterval

...

...

Please check and see if the following blog would help in your scenario:

https://powerapps.microsoft.com/en-us/blog/return-an-array-from-flow-to-powerapps-response-method/

 

Please try above solution, hope it could solve your problem.

 

Regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Here is the solution completed:

 

Power App -

Screen Shot 2020-08-07 at 2.12.17 PM.png

The key to making this work was to ensure the changes were made to the OnSelect. Once this was done then the queryResults displayed as a Data Source for using with the Gallery. 

 

Power Automate Flow -

Screen Shot 2020-08-07 at 2.25.40 PM.png

The key here was to ensure including the expression: 

"body('Execute_stored_procedure').ResultSets.Table1"
Which by the way every time I go back to the flow, the expression changes to the following. It still works regardless but it's strange on it changes it. 
 
Screen Shot 2020-08-07 at 2.29.52 PM.png

 

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (2,793)