Written by: Thomas Capps
6/9/2019 5:12 PM 

PowerApps is a rapid-development platform for creating applications primarily for internal use by businesses, and is part of Microsoft’s Power Platform, which also includes Flow and Power BI. I have done some development as part of a team at my day-to-day job, but also wanted a personal project for additional practice. I realized I already had a plethora of data that I could use to create a demo app.

Much of my free-time for the past 5 years has been spent playing Grand Theft Auto Online, the multiplayer mode of the crime-simulation game GTA V created by Rockstar Games, a division of Take Two Interactive. (Rockstar and Take Two own the rights to GTA V, GTA Online, and all the names of vehicles used in this sample app & database). As I have accumulated a lot of virtual wealth and vehicles in this game over the years, at one point I created an Excel document to help me manage all the cars, motorcycles, planes, helis, boats, etc. purchased in-game. However, as I play on console, it’s a pain either having my PC on at the same time to update the database while playing, or trying to update a spreadsheet using my phone’s small screen.

The purpose of creating this app is to have a way to manage all the vehicles owned by two different avatars, stored in multiple garages, as well as add new vehicles and stats to the database as they are added to the game. In addition, I need a way to automatically rank the cars and update these ranks as new cars are added, based on data compiled by Youtuber Broughy1322, who tests every vehicle in the game and provides their best times around a particular track (to test cornering, acceleration, braking, etc.) and on a straight-away (for top speed).

Step one was creating an SQL database, with lookup tables for all my data. Although PowerApps could use Excel in a shared drive as a data source, I feel the SQL database with its relationships would be much more manageable, plus it’s what we use at work. This database will likely grow as I expand the app with additional features, but it’s good for the initial structure. To begin, I’ll use PowerApps default screens for scrolling through a list of all vehicles in-game, viewing a particular vehicle, and updating its stats. In later blog posts I will expand the app by adding additional screens and forms for managing data in additional tables.

In your web browser, create an account or sign-in to web.powerapps.com and select Start from data. (You could also click the link for All templates, or the Create option in the navigation menu for sample templates to start with.)

In the pop-up, verify that we are creating a Canvas app for the phone, and click Create. (If Canvas App and Phone are not chosen by default, you can change this in the next screen.)

Click Common Data Source with Phone layout.

Near the bottom of the list of Connections, choose SQL Server, then enter the correct log-in and password info from your data and click Create.

Next choose a Table. I’m starting with my list of all vehicles in-game, so I’ll choose GTAVehicles, then click Connect. It will take a few minutes to build the app, and then you can skip the pop-up that asks if you want to preview the app first. We have a few changes to make before the default app looks decent.

This is the PowerApps Studio, where you can design your app. The left side of the screen is the Tree view, with all the screens and controls currently within your app. Note the three screens created for us (which I circled in red): BrowseScreen1, for viewing a list of all vehicles, DetailScreen1, for viewing a single vehicle’s stats, and EditScreen1, for editing the stats. Under BrowseScreen1, I have selected BrowseGallery1, which highlights the major portion of the screen in the Design View in the center (also circled in red). On the far right is the Properties view for the selected screen or control. (If yours has different options, make sure you have BrowseGallery1 selected in the left-hand Tree view.)

Looking at my data, PowerApps has chosen a couple of database fields from my table to provide a Title and Subtitle view for each entry. However, I’m not happy with the selections, as I’d rather have the name of the vehicle as the title, with the type of vehicle underneath that. So on the Properties menu for BrowseGallery1, I’ll choose Fields > Edit to change this.

An extra view pops out to the side of the Properties menu, to show my options for labels Title1 and Subtitle1 (in reverse order because they are shown alphabetically). I’ll choose VehicleModel for Title and ClassID for subtitle, then close the extra Data window.

The VehicleModel name looks better now, but ClassID is just a number. That’s because the vehicle Class name is in a separate look-up table, which this table uses as a foreign key. To get to the data that has more meaning to the user, we need to change the Text property for the Subtitle1 label control to look at this other table’s data.

However, each time this screen is accessed, there will be a slight delay in looking up each value, so I have found it works better to import the table into a Collection within PowerApps first.

To get the data to add to a Collection, we first have to add the second table as an additional Data Source. Choose Data sources under the View menu, then click + Add data source from the pop-out Data window.

The Data window updates, and now you can choose SQL Server. (If your lookup is in a different program such as Excel or SharePoint, note you could also choose Import from Excel or +New connection to get your data in different ways.)

The Data window now shows the tables in my database, so I’ll select my lookup table for GTAVehicleClass, then click Connect. Then close the Data window.

If we’re adding a Collection that we want to use throughout the entire app, we want it to be created when the app is started. At thetop of the left-hand Tree view, you can select the App object, then change the OnStart property to add the following:

ClearCollect(VehicleClasses,'[dbo].[GTAVehicleClass]');

The ClearCollect function both clears out any previous data in the variable name (in my case “VehicleClasses”), then adds the data specified in the second parameter (my new table name). I’m simply using this Collection to put the VehicleClass data into memory for faster lookup, but you could also use this function to filter out specific data from a data source, or even hard-code a list. See the link above for more details.

To create the Collection, you would have to restart the app. Instead, we can click the … (ellipsis) next to App on the Tree view and then Run OnStart. Once that’s done, you can now use the Collection, which we will do by selecting BrowseScreen1’s Subtitle1 label control, the update the Text property to the following:

LookUp(VehicleClasses, ID = ThisItem.ClassID, ClassName)

The LookUp function takes three arguments, the database or Collection where we are looking for the data, an operation that looks for a specific record, and the field value that we want returned. In my example, I want to look in the VehicleClass collection for the record whose ID matches the ClassID number of ThisItem (the current item within the BrowseGallery1 list of vehicles), and instead display the ClassName. Now I have a text-based class name instead of some numerical value.

Between now and my next blog post, I will be renaming all the controls in the Tree view to more-meaningful descriptions, then we can start looking at the other screens PowerApps has made and see how we can improve them as well. Meanwhile, don’t forget to save your app under File > Save!

Trackback Print

Tags: PowerApps

Categories:

Location: Blogs Thomas Capps

Your name:
Comment:
Security Code Enter the code shown above in the box below
Add Comment   Cancel