Using PowerApps to Read and Write to Azure SQL DB with Foreign Keys

Have you ever wanted to build an application and to communicate with things like Azure DB to store all your data? In this demo, I’ll show you how to do just that.

In this application example, we're going to start building a quick timecard application to manage projects using PowerApps and Azure SQL DB. This focus today is on the management side of the application, and my follow up post will jump into building a timecard app from scratch.

The goal here is to communicate with Azure DB to store all our data so we can better secure it, back it up and query it in better ways later. Let’s get started:

• First, I’ll start off in my Management Studio. I’ll be using a Project table and a Project Type table.
• In these tables we want to handle things like drop-down boxes and foreign keys. In the Project table I have a Project ID (our primary key) and a Project Type ID (our foreign key down to the Project Type table).
• Next, I go into PowerApps to create my app. I could sign into PowerBI.com and start with a blank application or I can start with some data and build it that way.
• For this demo, I’m going to create from a data source. The pro is that it builds the app quickly, but the con is it only builds a phone application; it does the basic CRUD (create, read, update and delete) for us very quickly.
• So, I’m going to create from a data source and when I click connections, I’ll see a list of connections. In this case, I’m using SQL database which I can do in Azure or on premises by setting up a data gateway.
• I select and connect to my Timecard DB data source and choose my Project table (conversely, when you start with data, you can only do one table at a time).
• When I go to connect, PowerApps is now going to create the app for me (all the basic CRUD I mentioned).
• Once complete, a negative to point out is that it uses default names on the screens that is has built, like Browse Screen 1. We need to go in and rename these so when we call out to them, we’ll know what each one is. I like to use a 3-letter qualifier like scr for screen, gal for gallery or tog for toggle switch and then add what it’s doing – for example, galBrowseProducts, to easily tell what that screen is doing.
• It’s important to note that when I do my rename, any code referencing that screen or gallery will also change.
• In this case, my gallery is a very important player. The gallery lists all the rows that we saw earlier in that Management Studio screen. Anything I do in the first row will be repeated in the rows below. I can also go in and change the way the gallery looks; I’m leaving it at title/subtitle for this example.
• In my gallery, I want to customize it by selecting the subtitle, which is an email and in my code bar I add the word “manager” (and “&”) so people know what that email means and so that I’m showing what that value is all about.
• Then I can hit the play button to see what I like or don’t like. I can change things like filtering and sorting or changing the sort order of the fields on my gallery screen.
• One thing I don’t like is the gallery field that says Project Type ID of 1; I’d like that to say something like ‘translation services’. This is where the foreign keys are going to be a bit of a challenge.
• To do something about that we need to key pair – we want to show the value of Project Type ID, but then keep the key that we store in the database, so we have a key pair. You can think of this as having the state name of Florida, but have the key of FL.
• I can edit by holding the Alt key and clicking on the pencil button to get into the app. I want to turn the Project Type ID field into a drop-down box to which I’ll be able to add values. In this case, I’ll have to add another connection, my Project Table Type, as a new data source. I can then refer to that table like it was my original table (Watch my video included here for a step by step demo on how to do this.)
• I’m going to finish this demo by covering how to use form mode; I can use this value to find out what state I’m in, whether I’m editing a state, using an insert, a view or an edit.

Be sure to watch my complete demo here with step by step instructions to learn how to build this app quickly and how to handle our foreign keys from Azure. In my next post, I’ll show you how to build a tablet-based application from scratch. This will be a timecard application that will have a nested gallery.

If you need to learn PowerApps, our deep dive PowerApps courses in our On-Demand Learning platform will teach you all you need to know to leverage PowerApps in your organization. All our 50+ courses on Azure, Power BI, Big Data and much more are taught by industry experts and are taken at your pace, with Learning Paths and Learning Coaches available to help with your learning goals.

Click the link below to learn more and to get your free trial today!