QTrack
Internal Tool to streamline tracking of imaging and provisioning new technology purchases.
Initial build created over 5 days and approximately 30 hours
Over the course of a week I learned the basics to take advantage of Microsoft's Power platform. I decided to leverage the tool to build an application that can digitize the computer imaging process at work. The following breakdown will be the basic details of how I put it together, though it may not reflect the final app that is used.
The first major part of development is fleshing out the UI. I find it easier to build logic when I defined everything the user will be able to see first. In this screen, I've simply added a button, label, and text box.
The added components (a label, drop down menu, and button) will be used to implement a searching feature to get a list of all the computers for a given department, though this may change. The logic for that feature will not be implemented as of this development sprint.
We don't want the track button to be able to be pressed if the input is empty, so we need to add some simple logic to disable to button if the text box is empty.
We also need to build a modal pop up in case a record is ever searched that doesn't exist. A background, two buttons, and a text box are used here. They are also grouped together to handle the modal logic a bit easier, which will be handled in a later step. The textbox has a white fill color, and the background is a medium-dark grey with it's opacity set to 25%.
The specific process this app is being designed for breaks down our imaging process into 4 distinct steps. There are also 2 additional steps for creating our records. The form template is used to create these screens.
The screen is duplicated a total of six times and named accordingly. Some of these names are changed in later screenshots to reflect some feedback during the development sprint. You may note the modal is in its own screen currently. This was changed later in the sprint and should just be a group in the intro screen.
The basics of the UI are fleshed out at this point. The entire application is based on the process we use these paper sheets for. Before we can continue adding more functionality we will need a data source to store the information. The Power platform supports many different sources, but I chose to go with excel for ease of use. The three sections highlighted in yellow will need to be formatted as lists in the new sheet we will make
Each box in the sheet is a step, and will need to be made into a column within excel. We will use these as the headers for a table to keep track of things. As stated above, the 3 highlighted yellow boxes need data validation enabled. This will look different for different processes. Add the following columns to the end of what was added:
"BasicComp, InfoComp, IsPrepare, IsImage, IsConfig, IsDeploy". These will be used later when we write the logic.
A quick refresher on Data Validation - Select the entire column in question. In the Data tab, click "Data Validation" (highlighted in blue). You should get the pop-up shown. Set the "Allow" Option to "List", the "Source" option to fit your needs, and be sure the "In-cell dropdown" option is checked. Repeat this for the other columns necessary.
The other columns should have their number format set as general. Excel doesn't directly allow us to deal binary data, and while we could just use the whole numbers 0 and 1 this caused some problems that I could only seem to solve by using the text "TRUE" and "FALSE".
To finish preparing the data simply select all the columns we typed in and drag the selection to include one blank row. Click on the "Format as Table" button in "Home" tab of your ribbon. The actual style doesn't matter but it will give us a table with headers. Excel Online doesn't allow us to set the name of the table, so you'll need to open the sheet in the desktop version to set the table name. While this isn't necessary it does make things much easier later down the line.
Lastly, go ahead and upload the sheet to OneDrive for Business (or regular OneDrive). Sharepoint would be a more ideal environment but unfortunately only Sharepoint lists are supported in the Power platform.
Now that we our data source we actually need to connect it back to our app. This is going to let us look up and patch records in to the table we made. It is important we actually made the table as that is the only way the Power platform will interact with the Excel sheet.
Click on the "Data" tab in Power Apps (highlighted in yellow on the left side of your screen) and choose the "OneDrive for Business" connector. That should open a toolbar on the right side of your screen to choose a file. Click on the appropriate excel file.
With the data now connected to the app we need to specify to our forms which table in the excel sheet they should be using. Click into the properties for the form in each screen that has one, and add the data source. You may see multiple entries depending on how you chose to format your excel file.
Each form is going to have different fields. This part is specific to the needs my organization has for this application. Once you have added the data source you should see an option labeled "Edit fields". Click here and select "Add field" to get a list of all the fields PowerApps will recognize from excel. This should show every header in the table we made, assuming it was formatted correctly.
PowerApps is quite intuitive and will do alot for us automatically. This can be a bit of a detriment as we may have named our fields in "developer-friendly" way but not one that is intuitive to anyone using the app. PowerApps also tends to protect us from ourselves and lock components it automatically created. We want to go ahead and unlock these so we can customize the properties to our liking.
In our case, pretty much every field needs to be turned into a checkbox. Go ahead and delete the textbox in every card. This will generate a few errors for every card that was created for us, which is fine. We will resolve those in a minute. In my case, I am doing this for every field in the "Preparation", "Imaging", "Configuration", and "Deployment" screens. This is a very tedious step as we are going in and deleting 30 or so text boxes, adding 30 or so checkboxes, and updating 60 or so errors.
With regards to the errors, if you move your cursor over the red x and click it, you will be able to navigate to the exact property that is giving the error. You may see fields referencing the textboxes deleted. The specific properties used exist with our checkboxes (though the .text property needs to be changed to .value) so we can simply replace the name of the old deleted textbox with one of the appropriate checkbox. Pay careful attention to the names inside of the "Tree view" to avoid confusion.
With the base of the forms filled out we need to add some additional UI elements. For convenience, I chose to add a button to select all the checkboxes and two navigation buttons to take us forward and backwards in the process.
The check icon should take us back to the main screen for a given entry (I haven't created that yet at this point) and the X icon should take us back to the main intro screen. The navigation buttons and the check icon should both save our progress, whereas the X icon will not. Again, this logic won't be implemented quite yet but that is the design intention.
Now we will make the section select screen that I mentioned above. It has a viewer form to show some basic helpful info and four buttons for navigation to a specific section.
We now need to move on to building the logic. First, we need to initialize our global variables. It is good practice to have variables you plan to use be in a known state at the start of running the app. Earlier we made several columns in our excel sheet that we aren't using in any of the forms ("BasicComp", "InfoComp", "IsPrepare", "IsImage", "IsConfig", "IsDeploy"). Additionally, add "WorkingSerial" as a global variable and assign it to be blank. These variable (with the exception of "WorkingSerial") will act as state variables to remember what is and is not finished. PowerApps doesn't have data persistence, so if we don't save the state of where we are, we won't know what is and is not finished at the start of the app. This will come in handy later.
For the track button, we want to use the statement above in the OnSelect property. We need to first determine if the text entered is actually in the table or not. What we are really doing is searching the main data source for a specific record that has a matching serial number. That query will always return a table, however if no match is found the table is blank. Depending on the results we will either launch the modal to make a new record or take us to the process selection screen ("SectionSelect"). The modal's visibility is determined by a context variable named "notfound". Context variables exist only in the context of a specific screen.
The function entered is pretty long but the logic is straightfoward. It is saying "If the Search() result IsEmpty() then "notfound" equals true. Else set the variable "WorkingSerial" to the text value entered in the text box then Navigate() to the screen named "SectionSelect".
Serial numbers for computers are unique, and we can take advantage of the fact by knowing a match will only ever return one record or an empty. To be safe we should add some more advanced data validation, but that will not be discussed in this breakdown.
In the "SectionSelect" screen we need to give the form viewer an item that contains a record for the fields we want to fill. The record is the entire entry for a given computer, while the field is the individual cell inside of a record. The fields we are trying to view have the column names specified in the "DataField" property of the respective cards. So the card "Staff_DataCard1", for example, has the "DataField" property set as "Staff". The item used is a LookUp() function that is searching through the TrackedDataSource for a record whose "ServiceSerial" field is equal to whatever the "WorkingSerial" is.
With the "SectionScreen"'s form populating we need to add the navigation logic. Each button will use the Navigate() function that takes it to the appropriately named screen. The X icon will not only go back to start, but will first clear out all our global variables so we can know for certain we are returning to a clean slate when we try to search again.
The four screens for each part of the process are quite similar to each other. We need to set the default value of the checkbox, set the "Select All" button to mark each checkbox as selected, have the check icon update and navigate back to the "SectionScreen" and have the left and right arrow icons Update and navigate to either the next or previous screens in the step.
First, for each form we need to set the item property as we did above in the "SectionScreen". In this screen shot, we are going to set the OnSelect property of the "Select All" button to be an UpdateContext() function that updates a variable named "allselected" to true.
Next, in the OnVisible property of each screen, make sure to explicitly set the "allselected" variable to false using the UpdateContext() function.
The default value of each checkbox will need to be an if statement. It is saying "If "allselected" is true then set the default to "allselected" (checkboxes have are binary, either on or off; otherwise stated as true or false). Else, see if the field called "CreateBackup" in our WorkingCollection is true and if it is then set the checkbox default to true. While a bit convoluted it seems to work well so far. The tedious part is we need to apply this statement to every checkbox, but referencing the appropriate field rather than "CreateBackup" every time.
I didn't mention creating any variable named "WorkingCollection" above. We are going to go ahead and do that now. A collection in PowerApps is essentially a table stored in a variable. This table is stored locally and does not persist upon leaving the app. For setting default values, we can take advantage of a collection by storing the entire record we searched earlier. We will set the working collection in OnVisible property of screen screen. Use the Collect() function to make a collection by searching the main data source for a record with the "ServiceSerial" field equal to the working serial. In the OnHidden property of screen we want to clear out the entire collection to prevent building up multiple records in the table. Since we are using the First() function to get the first record in the collection, it will be easier to predict the value if we can verify there is only ever one record in the table at a time.
The check icon in each screen should already have the OnSelect property filled out with a SubmitForm() function. The X icon will probably have the OnSelect property set to false; change it to use the Navigate() function to navigate back to the "SectionScreen". The right and left arrow buttons need the appropriate SubmitForm() function added to them, in addition to a navigate function that will go to the next or previous step. The syntax of the SubmitForm() function used can be the same as what is filled into the check icon mentioned above.
In the BasicInfo screen add navigation controls to the check and X icons. The check icon will submit the form and navigate to the MachineInfo screen. The X icon will navigate back to the intro screen. This behaviour will need to added to the MachineInfo screen as well.
The Item properties of both the BasicInfo and MachineInfo forms need to share the same LookUp() function used in each of the other forms.
This last step is particularly important. We will set the WorkingSerial variable based on what was already entered and then Patch() that back into the table to create a new record containing that serial number. This will let our forms work properly and kickstart the process off right.
And that's it! I wasn't able to build the search functionality in this first sprint but that is planned for the next one. I was a bit frustrated at times as the Power platform doesn't work in ways I expected, but it was nonetheless easy to acclimate to. I spent the first few days of the sprint running through training materials from Microsoft, and another 20 or so hours building out the rest.
It is also important to note that this wasn't thoroughly tested. I did find a few bugs before showcasing the progress but it certainly needs more work.