First part of this post:
In last week's post, we talked about how you can use PowerApps to edit data directly from your Power BI report. But what if you want that data to be verified before it is actually written back to your data source?
Well, in that case, you probably need to think about how you can build an approval workflow, so that your data is only written back to your data source after someone approves it.
Having this, today we are going to add an extra step to our solution, building an approval workflow using Power Automate. Here is a very simple architecture of our solution:
So, as you can see from the image above, our Power BI report gets it's data from an Azure SQL database. On the other end, our PowerApp will read the data coming from Power BI, but also from SQL, as we want to be able to write back to our data source.
Once the PowerApp form is submitted, a Power Automate Flow will be triggered. This flow will send an approval request email, which the user needs to reject or approve. Once this request is approved (and only when it's approved!), your new value will be written back to the database.
Curious? Let's get to work!!
Reminder from last post: the data we are using is from a SQL Server table called Auto Trader, that has data about used cars (Brand, Model, Price etc).
1) Go to Power Automate and create a new Instant Flow:
> Click on Create
> Choose Instant Flow
> Name your flow
> Select the PowerApps trigger
> Click on Create
2) Now we are going to add a new step that will allows us to retrieve the data from our original data source in Azure SQL:
> Click on New Step
> Search for the action Get Row (V2) for SQL Server - you can retrieve multiple rows at a time, in this case I just want to retrieve one
> Make sure you're connected to the correct SQL Server Instance
> Select your Server Name, Database Name and Table Name
Now, to the last field "Row ID"... you must be asking yourself - "Ask in PowerApps?? What is that??".
For you to be able to use values from your PowerApp, you need to pass them as variables to your Power Automate Flow. When you click on "Ask in PowerApps" you're basically defining a new variable that will be later used in a function in PowerApps. Don't worry, it sounds confusing and it is confusing! But hopefully in the end you will get it :)
You just need to understand for now that whenever you click in "Ask in PowerApps" you will be defining a new variable, you can have as many variables as you want!
In my case, I needed to know which row to change in my database, so I needed to pass the context of whatever I had selected in my PowerApp. To do this, I created this new variable in Power Automate, so that I can later get the CarID (my primary key) to retrieve the correct row.
3) In this step, we are going to build our Approval Email. To do this, we are going to use the action Send Email with Options:
> Click on New Step
> Select the action Send Email with Options
> List the two options to generate an "approval" (in my case I wrote Approve/Reject). These are the options the user will see when he/she receives the approval email
> Write the body of your email. As the user will have the option to approve or reject the request to change the price of a car, I added both the current price (coming from SQL Server) and the new price requested. For the new price I used the same method as in the previous step. Because I need to know what was the user input in the PowerApp form, I added a new variable using the "Ask in PowerApps".
As you can see, because we got our row from SQL Server in the previous step, we can use all the fields in the subsequent steps.
As a reminder, the two variables we already defined using the "Ask in PowerApps" are:
Getrow(V2)_Rowid - CardID variable
Sendemailwithoptions_Body - New price variable
And if you're asking yourself why these strange names, well, Power Automate generates the names automatically whenever you select "Ask in PowerApps".
4) Finally, for the last step of our flow, we are going to add a condition:
> Click on New step
> Choose Control and then Condition
> Choose the variable SelectedOption. SelectedOption is the output of the approval email.
> Leave the condition as "is equal to" and write Approve
> In the If yes branch, click on Add an action and select the Update row (V2) action for SQL Server
> Select your Server name, Database name and Table name
> On the Row id, we are going to use the first variable we defined using the Ask in PowerApps - Getrow(V2)_Rowid - this will correspond to the Car ID, which is the primary key in our SQL Table
> Lastly, we are going to get the new price for the car, which is the second variable we defined with Ask in PowerApps - Sendemailwithoptions_Body - this will be the value that will be written back to our database
> As we don't want our flow to do anything if the request is rejected, we can leave the If no branch blank
> Save your new Flow!
Tip: if you want your flow to be more user friendly, you can add a step to both the If yes and If no branches to send an email to the requester with the outcome of the approval flow!
5) Now we just need to tie everything together! Let's go back to our PowerApp and make some magic happen!
> Select the OnSelect property of your Submit button
> Select the Action tab and then click on Power Automate. A list with your flows will show up on the right
> Select the flow you built for your app (in my case it was the My Approval Workflow)
After you've done the previous steps, you will notice a new function will show up in the formula bar. This function will be in the format YourFlow.Run() - in my case, as my flow as named My Approval Flow, the function generated is MyApprovalFlow.Run().
Now, if you remember, we defined to variables using the "Ask in PowerApps":
Getrow(V2)_Rowid - CardID variable
Sendemailwithoptions_Body - New price variable
So my function has two variables and it looks like this:
MyApprovalFlow.Run(Getrow(V2)_Rowid, Sendemailwithoptions_Body)In other words:
MyApprovalFlow.Run(CarID, NewPrice)Now, we need to pass the correct values to our function. In my app, the value for the CarID is on the DataCardValue5, and the value for the new price is on the DataCardValue4.
My final Power Automate function will be:
MyApprovalFlow.Run(DataCardValue5, DataCardValue4)Note:
You need to use the Data Card Value and not the Data Card Key or any of the others.
Save your app and test your flow!
The approval email the user will receive will look like this:
And yes, the email could obviously look nicer :)