PowerApps Tips: SQL Server Interaction

Turner Kunkel   •   09.11.2018

Overview

The purpose of this article is to briefly demonstrate interaction with end-users of a PowerApps application via the user interface and what is happening in the background of the application.

By the end of the article you will learn how to call a SQL Server stored procedure from PowerApps and return results to the end-user.

 

Background

PowerApps is a robust user interface tool that can be built by power users (or anyone who is knowledgeable regarding PowerApps development) within an organization.

It can interact with Flow by way of a trigger, and Flow can respond to PowerApps.

Here, I will demonstrate a simple build of an application that accepts some text from a user and returns whether an operation was successful or not.

 

Starting the Application

Using one of Microsoft’s templates, I have created a tablet-sized application that shows FAQ’s from “my” organization.  In this example, I am just showing some stock questions and answers about the Surface Pro 3 laptop.

Use Case

I would like to add a section for my user to input a new FAQ to be considered for answering and posting on this page in the future.

I already have a SQL Server stored procedure in an on-premises database to use that would insert a user’s comment into a holding table.

How can I accomplish accepting the user input, running the stored procedure, and letting the user know the comment was sent to our systems?

 

Build

PowerApps Input

To start, we are going to add a simple prompt and a text box in the application interface.

This can be accomplished by selecting [Insert] -> [Text] -> [Text Input]

I put this text box underneath the currently selected question, enlarged it a bit, removed the default text, and added some “hint” text to prompt the user.


The text box would be no good without a button.  We can add this by way of [Insert] -> [Button]

I then configured the button to display “Submit,” made it larger than its default, and placed it next to the text box for the user to click on.

We now have a space for the user to submit an FAQ for consideration.

Of course, this doesn’t do anything yet.  We have to configure it to run a stored procedure that accepts the user’s input.

Adding a Flow

You can either start a Flow by clicking on your newly created “Submit” button and clicking on [Action] -> [Flows], or create a Flow first and selecting it by way of [Action] -> [Flows].  I will create one using the former method.

This will take you to a new browser session to create a Flow under the same tenant as your user in the PowerApps editor.

It will automatically generate an “Action” step in the new Flow to accept a trigger from PowerApps.  I have named this Flow “FAQ Consume.”

This Flow is operational, but at this point will not do much of anything.  Let’s click [+ New Step] to add a call to our SQL Server to run a stored procedure.



(Connections)

At this point you may already have a connection set up for Flow, but you can also add a new one if you’d like, by clicking the ellipsis on the SQL Server stored procedure action.

NOTE: If you have an on-premises SQL Server, as this example uses, you will need to configure an On-Premises gateway.

For this article, I will be using an already-configured connection using my own gateway.

My connection is selected by the ellipsis for this action, and I can drop-down to select the procedure I would like to use for this Flow.

My stored procedure asks for two parameters: User and Comment.  I can set these to accept input from PowerApps by selecting “Ask in PowerApps” for each parameter.

(Branching)

The flow now will need to send some information back to the user.
We will set up the following decision branch:

  • Did the procedure complete successfully?
  • Did the procedure fail?

In order to do this, we can set up a parallel-branching action, setting one action to respond when the procedure call was successful, and one to respond differently when the procedure call failed.

We are going to want the ‘Respond to PowerApps’ action for each branch.


This action will ask for you to “Add an output.”  The best way I found to do this is to choose a Text output, to avoid any data conversion problems with PowerApps reading the output.


I then set up the response Name to be “text” to make things simple, and the actual response string to be “TRUE.”


In order to set up the converse result (an unsuccessful procedure response), we can add a parallel branching action coming out of the stored procedure.


This new action will be set up the same way as the first “Respond to PowerApps” action, above, but will send back the string “FALSE.”  It is important to name this action the same as the “TRUE” response for PowerApps’ IF() conditional.  I will be going over this shortly.
Clicking the ellipsis on this action, you can set it to run after the procedure fails, which is when we want this action to execute.


(Completed Flow)

We now have a completed flow that will:

  • Accept input from PowerApps
  • Run a stored procedure with parameters
  • Return a success or failure response from the stored procedure

Click “Save” to save your work.

Back to the Application

The newly created flow will now be available for use by the “Submit” button in PowerApps. (Under [Action] -> [Flows])

Clicking on it will prompt you to enter the values from PowerApps to pass to the flow’s parameters we set up earlier.
We can pass in the text from the text box and the user interacting with the application.


As it stands, this will execute the flow “FAQ Consume,” but it will not return anything to the user of the PowerApp.
I mentioned the IF() conditional previously.  We can use it here, along with the NOTIFY() function.
We can tell it to give a message to the user if the output “text” from the flow is “TRUE” or “FALSE”.

Let’s Test!

I would like to test my new FAQ submission feature, so I can click on the “Play” button in the PowerApps editor to do so.
Here is a successful test:


Here I am forcing a failure to show what that would look like to the user:

Checking History

Flow has a historical logging feature, which we can check for various reasons:

  • What was successful and what was not
  • What values were passed to the flow
  • General troubleshooting and analysis

Here we can see the last two attempts from my testing:

Clicking on the “Failed” entry, I can see what was passed into the stored procedure and why it failed:


(I forced this error with an attempt to divide by 0 in the procedure)

 

Conclusions

Above we walked through a simple example of how to allow PowerApps to call an on-premises SQL Server stored procedure and have its binary result (succeed or fail) reported back to the user using Microsoft Flow.

Further Use Cases

  • Data (or meta data) management for developers
  • Ticketing system for application errors
  • Communications of any sort to a department backlog or queue

Benefits

  • User experience improvement
  • Historical tracking of user input
  • Easy access to failures and troubleshooting
  • Security application interaction for an organization via Active Directory

All of this is done without web coding, just a bit of knowledge of PowerApps and Flow.

 

There are many, many more ways you can use PowerApps and Flow.  I strongly encourage you to take some time to see if they can provide an enterprise solution for your organization.

 

Thank you for reading.  Please feel free to email me at turner.kunkel@talavant.com with any questions or discussion points.


Turner Kunkel

Senior Consultant

Share This Article