Connecting PowerApps To On-Premises Data With On-Premises Data Gateways

Scenario

Many of us know how fast everything moved into cloud. Office 365 products get updates on a weekly basis, even when SharePoint on-premises is still in the 2016 version. What about all the companies which still have on-premises data, SQL Server, for example? What modern Microsoft products can offer them to allow them to connect to on-premises data from PowerApps and Flows?

Here’s a typical example scenario.  Let’s say a company has a lot of SQL data and want to make it accessible on all range of mobile devices (Android\iOS\Windows).  They want to do it themselves and make sure it is a cost effective solution.

Let’s be more specific and imagine a small hospital with a local database where they have patients’ information about dates of visits, heart rate, and age.  The hospital wants to give doctors an option to see information about patients on their mobile devices or tablets.  For example, a doctor might want to see an older person, who didn’t visit clinic last month and who has a high heart rate (this data can be gathered from tracker devices). The source code of the app you can download here.

Solution Architecture

Microsoft PowerApps can solve this problem in a day or two.
What products will we use to implement this solution with Microsoft PowerApps?

• Microsoft on-premises data gateway
• PowerApps

Let me explain the architecture in one simple diagram:

The on-premises data gateway will communicate with the Azure Service Bus (ASB).  To enable this communication you need to configure outbound ports: TCP 443 (default), 5671, 5672, 9350 thru 9354 on your firewall.  The gateway does not require inbound ports.
For the gateway you can use any VM which can communicate with the SQL server. You may be thinking about using a DMZ server, I know that, but let’s keep our scenario simple for today. 😉
In our sample scenario, our hospital has 1 database named HealthcareDB and three tables in the database named Appointments, HospitalPatients, and Vitals.
Here is the hospitalPatients table schema.  This table stores Patient ids, names and e-mail addresses.

Here is the Appointments table schema.  This table stores the date of the last patient’s visit. In a real-world data schema, the dates of all visits are stored, but let’s keep it simple for now.

Here is Vitals table schema.  This table stores daily heart rate data. Hospitals can use smart watches and other devices to pull this information into the system.

Building the solution

To create a PowerApp and on-premises data gateway that connects to on-premises SQL server data, follow these steps.

1. 1. Login to the VM where we you install the gateway.

The VM should have access to Internet and do not forget to restrict Internet access to only white-listed domains when you are in production.

1. 1. Download and run the gateway installer.
   2. The installation process is very straightforward. Follow the steps in this article.
   3. Login to web.powerapps.com and create a connection to the gateway (to the ASB).
   4. Expand the Data menu and select Connections.

1. 1. Click New connection.

1. 1. Use search and find SQL Server

1. Click SQL Server and enter the following parameters:
   1. SQL server (do not mix it up with the gateway name J)
   2. Database the holds the patient information
   3. Username to connect to the database
   4. Password

   If you have problems accessing a gateway, check the account you used to sign into the gateway and what account do you use to log in web.powerapps.com. It should be the same.

2. Click Create.
3. You are now already 80-90% complete and are almost there! J
   Now, create the PowerApp.  If something doesn’t work in your PowerApp, do not worry about it, you can download the PowerApp here and use it for reference.

Creating the PowerApp

1. 1. Go to web.powerapps.com
   2. Click Apps , then click Create an app.

1. 1. Select the Blank phone layout
   2. Once the new PowerApp appears in the editor, click View, then click Data Sources.
   3. Click on Add data source and find SQL Server.

HealthcareDB is the name of the database used in this example.

1. 1. Select all tables except sysdiagrams. Here is how your connector list should look like after your added all required connectors

1. 1. Click connect
   2. Next, add a Gallery on a form and set the Items property to the name of the patient data table.

'[dbo].[hospitalPatients]'

1. 1. Add 3 labels to the gallery and set their Text properties with these values. This code displays the first name, last name and the last visit date for each patient displayed in the gallery.

ThisItem.firstName

ThisItem.lastName

 First(Filter('[dbo].[Appointments]',patientId= ThisItem.patientId)).lastVisit

Example:

1. 1. Select the date label and set the Color property to the following code. This code makes the text appear red if the last visit was more than 30 days ago.

If(DateDiff(First(Filter('[dbo].[Appointments]',patientId= ThisItem.patientId)).lastVisit,Now())>30,RGBA(254,0,0,1),RGBA(47,41,43,1))

Now you should be able to see list of all users from your local SQL server and the date of their last visit.

1. 1. Set the onSelect property of the gallery as follows. This code creates two variables for selected patient object and for day of his last visit

"Set(SelectedPatient,ThisItem);"

"Set(DaysSinceLastVisit,DateDiff(First(Filter('[dbo].[Appointments]',patientId= ThisItem.patientId)).lastVisit,Now()));"

"Navigate([@UserDetailsScreen],None)"

1. 1. Create a new Screen called UserDatailsScreen
   2. Place 7 labels on the new screen, then set the Text property for them with the following values. Each line below correlates to one Text property on one label control.  This code displays the data in the details screen for each individual patient.

"First Name:"

"Last Name:"

"Last visit"

SelectedPatient.firstName

SelectedPatient.lastName

DaysSinceLastVisit & " " & "days ago"

"Heart rate data"

See screenshot the below for reference:

1. 1. Add a new blank gallery on the UserDetailsScreen
   2. Set the Items property to the following code. This code fills the gallery with the information from the Vitals table for the patient on the details screen.

Filter('[dbo].[Vitals]',patientId = SelectedPatient.patientId)

1. 1. Add two labels to the gallery, then set the Text property for them with the following values. Each line below correlates to one Text property on one label control.  This code displays the date and heart rate from the Vitals table.

Text(ThisItem.Date,DateTimeFormat.ShortDate)

ThisItem.HeartRate

That’s it, you are done!  Now the PowerApp is able to connect to on-premises SQL Server and display a list of all patients. The PowerApp indicates when a patient has not visited the hospital for a while. You can click on a patient and see all their heart rate data and other useful information. In a production scenario, you would probably want to take this one step further and add a button with email notification functionality to schedule an appointment, and add other functionality as well.

As you can see Microsoft is trying really hard to make developer’s lives easier. With PowerApps you can now build useful apps in days with minimal development knowledge and integrate it well in your business.

I hope this article is helpful for you.  Please reach out to us if you need help developing PowerApps solutions for your company.