Jun 16

Written by: Thomas Capps
6/16/2019 5:17 AM 

This is Part Two of my app for maintaining data on vehicles found in the video game GTA Online using Microsoft PowerApps. Please see Part One for info on the SQL Server database design and how the screens below were created.

As mentioned at the end of my last post, I have renamed the screens and gallery/forms to more meaningful titles, by adding the function of each to the beginning of the title and removing the trailing “1”. You should still be able to follow along if you choose to update these to something more meaningful for your own app, as I have kept most of the default name intact. You can rename them within the Tree view by double-clicking, or by hovering over each object and choosing Rename from the ellipsis (…)

 

Finishing the first screen from the last post, the gallery now shows all vehicles with their model names and vehicle classes. Note that we adjusted this ourselves by editing the gallery’s properties to get the correct fields, then by choosing the Subtitle1 control in the Tree view to fix the ClassID numerical field to an actual class name by using a look-up. The gallery is actually made up of a group of Data Cards, and if you edit the top one your changes are copied down to all the others. By default, when your user clicks the right arrow of a particular item in the gallery, it remembers which item is selected in the gallery for reference later, and the navigation to the next screen it already provided for you. But I want to make a slight change…

 

Click on NextArrow1 in the Tree view, or click the right-facing arrow in the Design view. (if you don’t already have some data in your tables as I do, your design view may be blank. We will add data using our app by the end of this post.) If the OnSelect property isn’t the default property shown, choose it from the dropdown. Note the navigiation PowerApps has already provided; it will navigate to the next screen, to view the details of a single entry.

 

Rather than rely on PowerApps to remember which item the user chooses, I’m going to save the ID to a global variable called SelectedVehicleID using the Set function. The OnSelect property of the NextArrow1 control for each item in the gallery should now read:

 

Set(SelectedVehicleID,ID);Navigate(ViewVehicleDetailScreen, ScreenTransition.None)

 

Set takes two arguments, the name of your global variable, and the value you want it set to. “ID” in this case is the data field related to my GTAVehicles table, which is the data source for the gallery. After setting this value to the variable name, the app will switch to the next screen using the Navigate function, which also takes two arguments: the screen to navigate to, and the ScreenTransition to use. (I will use None, but you could change this to a Fade, or various versions of Cover that turn the page like a book.

 

Now it’s time to look at the next screen. Either hold down ALT and click the NextArrow1 control in the Design view to test the navigation, or choose the ViewVehicleDetailScreen from the Tree view. Each field in the chosen record is shown within Data Cards on a form, but they are in alphabetical order. I want a different order, so with ViewVehicleDetailForm selected, I’ll click the Edit fields option in the form’s Properties tab on the right.

 

The little pop-out window is back, now listing the fields already showing up on the screen. You can click and drag each field to change the order within this window, which updates the design. I’m moving VehicleModel all the way up, and moving TrackSpeed above DragSpeed. If there are any fields (like “ID”) that didn’t get added automatically, you can place them in yourself by clicking + Add field.

 

Order looks good, but once again ClassID is some number. Luckily I still have access to the Collection I made in the previous post, so all I have to do is fix it on this screen too. First note that the actual field value is tied to the Data Card; by selecting it we can see that the Default is ThisItem.ClassID, ThisItem being a pointer to the current record.

 

DataCardValue1 has the value we want to fix, BUT the Data Card is locked. On the Advanced tab (or at the top of the screen) you can see that the Text field is grayed out and locked. We have to unlock the Data Card in order to change anything but the most basic display features, creating a Custom card.

 

Note how the ClassID field in the pop-out now shows as “(custom”). By clicking Unlock to change properties on the Advanced tab, we have allowed the modification of fields within that card, specifically DataCardValue1. Change the Text field of this control by expanding the Advanced tab, or from the choices at the top of the screen, to the following:

 

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

 

This is searching the VehicleClasses Collection I made last time for the record with the “ID” that matches ThisItem.ClassID, then returns the corresponding ClassName. Remember that I created this collection to speed up the look-up of the vehicle class name, rather than hitting the GTAVehicleClass table directly, which caused a lag.

 

Also note that PowerApps has given us a very detailed Delete button, which removes the selected item from the database, checks for errors, then navigates to the previous screen using the Back() function. I, on the other hand, don’t want any random user to remove a vehicle from the master list, so I’m going to clear out the code under OnSelect (also gonna remove “Delete this Item” under ToolTip). In a later blog post, I will replace this button with something much more useful.

 

Last thing to note before heading to the final screen, is the button that will take us there. IconEdit1 sets the VehicleAddEditForm of the next screen to EditForm mode, the navigates to the screen itself (with ScreenTransition still set to None). Everything is fine by default, so let’s just hold down ALT and click this button to follow the navigation.

 

Now instead of just viewing a single vehicle’s record, we have textboxes to edit the fields. Unfortunately everything’s in the wrong order again, so just like in the last screen I’ll click Edit Fields in the Properties tab of the VehicleAddEditForm, and click-and-drag them to the order I want.

 

Aaaand once again ClassID is a number, so once again I need to unlock the Data Card that textbox is under in order to fix it. This time, though, I don’t just want to display the text, since it needs to be changeable by the user, and the database will update based on what it input in the box (forcing me to do a reverse-lookup to put a numeric value back into the Vehicles table). Instead we will add a dropdown control.

 

IMPORTANT: Make sure the ClassID_DataCard2 control is highlighted in the Tree view first. Otherwise the control will be added to the screen, but not in the right area. If it still doesn’t attach to the ClassID DataCard, make certain you unlocked it first.

With ClassID_DataCard2 highlighted, from the Insert menu, choose Controls > Drop down.

 

Dropdown1 is added under ClassID_DataCard2, but above the other controls on that card. Note that the order of controls in this hierarchy determines which controls are placed in front of others. I have not yet deleted the original textbox “DataCardValue5”, but have moved the dropdown to cover it… for now.

With Dropdown1 highlighted, change the Items property (either at the top of the screen, or under the Advanced tab of the Properties view) to look at our Collection, VehicleClasses. Since the collection has a list of ClassNames with their “ID” values, it’s a simple matter to choose which field we want to display to the user, and which we want to write to the database.

 

There’s one small problem, which you might not realize if you don’t play a lot of GTA Online. The Alpha-Z1 is not a “Boat”, it’s a “Plane”. When we are editing an existing record like this, we want the dropdown to default to the name already stored for that record.

Again with Dropdown1 highlighted, edit its Default property to the following:

 

LookUp('[dbo].[GTAVehicleClass]',ID = ThisItem.ClassID,ClassName)

 

Before we were using our collection for a quick lookup, but this time we have to check the actual GTAVehicleClass database to see what it shows for this record.

 

Let’s check the default value for the Cancel button, which resets the form to its default state, then returns to the previous screen using Back(). Looks good to me, but the Submit button will be a different story…

 

The OnSelect property for the IconAccept1 control defaults to SubmitForm(VehicleAddEditForm), but I have been having trouble with it myself. Unfortunately this means I needed to rewrite SubmitForm using the Patch function, embedded in an If statement so that we can use the same screen for editing an existing record or creating a new one. But FIRST, since the code will be unwieldy enough already, let’s rename the controls to better-looking titles, so that our code isn’t filled with “Dropdown1” and “DataCardValue__”.

 

Change the OnSelect property of the IconAccept1 control to:

 

If(

    SelectedVehicleID = 0,

    Patch(

        '[dbo].[GTAVehicles]',

        Defaults('[dbo].[GTAVehicles]'),

        {

            VehicleModel: txtVehicleModel.Text,

            ClassID: ClassDropdown.Selected.ID,

            TrackSpeed: txtTrackSpeed.Text,

            DragSpeed: Value(txtDragSpeed.Text)

        }

    ),

    Patch(

        '[dbo].[GTAVehicles]',

        First(

            Filter(

                '[dbo].[GTAVehicles]',

                ID = SelectedVehicleID

            )

        ),

        {

            VehicleModel: txtVehicleModel.Text,

            ClassID: ClassDropdown.Selected.ID,

            TrackSpeed: txtTrackSpeed.Text,

            DragSpeed: Value(txtDragSpeed.Text)

        }

    )

);

ResetForm(VehicleAddEditForm);

Refresh('[dbo].[GTAVehicles]');

Navigate(

    ViewVehicleDetailScreen,

    ScreenTransition.None

)

 

Patch takes three variables: the database table to edit, a lookup to find a particular record (or default to a new record), and a collection of the fields to patch. I have two Patch functions within my If statement: if the variable SelectedVehicleID is set to zero and NOT to a specific Vehicle ID (more on that later), I will use the Defaults of the table to create a new record. Otherwise, the second Patch function uses Filter to find the record based on SelectedVehicleID, then uses First to get the “first” item of the “collection” (really a single record, but Patch doesn’t know that) created by Filter.

Now whether we are defaulting to a new record or we have found the existing one, we insert a group of fields (surrounded by curly braces, separated by commas, and setting the values with colons) into the table. If it’s a simple string we need, we just access the Text property of the control. If it’s a dropdown item, we get the “ID” numerical field of the Selected property for that dropdown. If the value stored in the database is configured to a decimal-type, we have to wrap the control’s Text inside a Value function.

 

One last thing left to do today. Since the AddEditScreen can be used to ADD as well as EDIT, we need a way to signal that code we just wrote that it’s a new record we are creating. Since the New button is back on the original AllVehiclesBorwseScreen, we go back there in the Tree view and select the IconNewItem1 control. Its OnSelect property is set to:

 

NewForm(VehicleAddEditForm);Navigate(VehicleAddEditScreen, [ScreenTransition.]None)

 

In front of this, we want to Set(SelectedVehicleID,0); Rather than have a separate Boolean flag, we can just check the global variable for a zero value within our code that Patches the database, to add a record instead of searching for an existing record to modify.

So far all we have done is modify some default screens. In upcoming blog posts I will add additional screens to increase the functionality of my app. However, in my next post I will show how to add a simple pop-up to a screen rather than creating a whole new one.