There is a lot of buzz around PowerApps at the moment with Microsoft confirming at the recent SharePoint Virtual Summit that it is the strategic tool to replace the functionality that InfoPath used to offer. Unfortunately for those of you with a lot of InfoPath forms, there won’t be any direct migration path for old forms. So, it is a chance to evaluate any existing forms and look for alternatives.
However, there is more to PowerApps than InfoPath offered and with the Connectors available to many different services; it is a real opportunity to review simple legacy apps in your organisation for the potential to be replaced with PowerApps. If you have a web service, simple desktop application or Access GUI that connects to a simple database then there is a good chance that PowerApps could give you some great advantages with relatively little effort.
Credit: Shutterstock/Chantal de Bruijne
What is a PowerApp?
Part of Office 365 (although it can be licensed for users separately), PowerApps offers a no-code solution to create apps that can connect to multiple services. There are plenty of great tutorials, including on the main PowerApps site, so I won’t spend too long on what can be done apart from speaking about some of the advantages it can quickly add to legacy apps:
- Cross platform user interfaces meaning you can create one app to use on web, desktop, tablet and mobile with no change.
- Securely connect to your on premise data using the On-Premises Data Gateway.
- Use phone and tablet functionality such as cameras and location services with a simple drag and drop.
- Connect to many cloud services (around 70 at time of writing)
How do I get started with my legacy app?
To help understand it, I have a sample dataset from Kaggle around car data from 1985 – I did say this was about legacy apps. I loaded this to a SQL database running on a virtual machine on Azure and got to work.
First, install the On-Premises Data Gateway
PowerApps is a cloud service and so to talk to data on premises, there is a tool that can be installed to transmit data using standard ports to the Microsoft Cloud. At this stage, I would recommend reviewing with your security teams to ensure that they are comfortable with the access granted by this as you are allowing Microsoft to directly query certain databases in your own environment.
Once you are ready to install the gateway tool, browse to the PowerApps site, sign in with your Office 365 account, click on Settings and then pick Gateways.
Once installed, execute the application and run through the set-up wizard. You will need the following information to fill out during the installation:
- Account to use for accessing.
- Gateway Name (must be unique).
- Recovery Key – password for validating use of the gateway.
You can follow this short video for more details:
Create a blank app and connect to the data
Following the installation, you can now start to access your data in PowerApps. For this example, you can start with a blank template.
|Start by selecting “Add Data Source” from the right side of the screen.
|Select “Add a New Connection” and then choose SQL Server – there is no explicit connector for gateways but these become an option for any services which are allowed to be used.
|Select to use the on-premises data gateway and then fill out the other fields as if you were connecting to another server.
When filled out, select the Default dataset and pick the table that you need to connect to. A key point here is that the table MUST have a Primary Key defined to work with PowerApps.
And now you have your data to use. This gives full read and write capabilities.
You can see a run through of this process and a short overview of the app in the video below:
Working with the data
One of the “lovely” things about working with legacy apps is some of the strange data. There are often a lot of tweaks that have gone in to an application to get it appearing the way everyone would like but haven’t been reflected in the database. There a few examples of this in the sample data used in this app such as:
- Car makes are all in lower case
- The price and other numeric fields are held as strings
- Question marks instead of NULL values when data is not held
- Inconsistent spellings
You may have the luxury of being able to revise the data but that isn’t always possible and PowerApps offers a few functions that allow you to work with the data. You can see the full list on the PowerApps Documentation site. Before we tackle a few of the issues above, let’s take a look at the basic app.
Structure of a PowerApp
PowerApps are broken down in to a set of screens which you can set up navigation between. For this app, the home screen is a List Screen that shows a small selection of the data. While you could have a table view of this data, there is a growing user experience (UX) push to focus on the data needed rather than fitting as much as possible on to the screen.
Clicking on the arrow or edit icons takes you to the detail or edit screens respectively. Here, it uses the default form view currently:
If you want to add further fields, then you can pick these from the options bar:
Let’s get back to the data issues.
Car makes are all in lower case
There is a PowerApps function called Proper that converts the string to have a capital letter at the start and makes the rest lowercase.
The price and other numeric fields are held as strings
The Value function converts a text field to numeric so that you can work with it as a number. As this is a price, it is also useful to be able to process the returned string as a price using the Text function with formatting string to convert them.
Question marks instead of NULL values when data is not held
A simple Replace function will remove a question mark and show as an empty field instead.
…..ok, I caught myself out here. There’s not yet any fuzzy logic but you could utilize SQL Data Cleansing Services to review the data first.
Building display logic
Finally, you can add some more imagery by using a Switch case to show a different photo. The photos are uploaded as Media and given a unique name for the app which can then be used.
The application above took about an hour and a half to pull together from first installation of the gateway to publishing the application. Your own legacy applications will likely be more complex and require further work. One of the downsides to PowerApps is that you will likely hit something that can’t be done quite soon. For example, if you wanted to show a list of calendar items from Office 365, there is no way to show the recurring items.
The good news is that any connector can be extended with custom code using your own APIs. In the example above, you can write a custom web API to retrieve the recurring items and return them to PowerApps. Ballard Chalmers is ready to help take these apps beyond the basics and work with you to help PowerApps meet your legacy application needs.
By Kevin McDonnell, Senior Technical Architect
Kevin McDonnell is a respected Senior Technical Architect with a Master of Engineering (MEng), Engineering Science degree from the University of Oxford. Specialising in .NET, Azure and the Office 365 development suite as well as a broad understanding of the wider Microsoft stack, he listens to what clients are looking to achieve and helps identify the best platform and solution to deliver on that.