Table of Contents
Background
In early 2020, my employer sent everyone home in lieu of complications and business restrictions introduced by the COVID-19 pandemic. Since March 2020, the entire company (aside from electric linemen, plant operators and other site managers) has been working from home to support business continuity and our promise to maintain “world class performance delivering hometown service” to our customers.
Being in network security, my primary functions normally involve operational support for our Palo Alto firewall platform and other corporate infrastructure. However, I am also routinely tasked with “one-off” projects since I am the only programmer on a team of network design engineers; examples have included a Node.js application to track sites by geographical location using the Google Maps API, and most recently, a Microsoft Power Apps mobile application to manage every conference room at every site around the state of Michigan.
Power Apps is a highly integrated data service that provides rapid cross-platform mobile application development. This service allows the developer to build custom applications that either connect to existing data (“Canvas”), or are generated based on existing data (“Model-driven”), or both from multiple data sources - these include SharePoint, Microsoft 365, Dynamics 365, SQL Server and many more. Excel workbooks stored in the cloud (OneDrive) are even valid data sources.
Implementation
I was able to leverage the power (no pun intended) of Power Apps by speaking with our software development teams and becoming an early user of the service by requesting internal approval. Once granted, I got to work.
Ironically, Power Apps requires little to no prior knowledge in software development before working with it. Prior knowledge and experience may be beneficial, but the biggest design decisions to consider are the layout and logic flow of the UI. Since my task was to build an application listing out sites and rooms at each site, I figured the most straightforward solution should include 3-4 screens:
- “Location” screen - list out every site
- “Rooms” screen - list out every room at a specific site
- “View” screen - view room info
- “Edit” screen - edit room info (reuse for adding a room)
Upon reading that Power Apps possesses capability to generate UI from preexisting data, I had to see it for myself. So I elected to try the “Model-driven” approach and prepared a very rudimentary Excel spreadsheet matrix for my data. I opted to start with the conference room data since it was readily available to me, provided by another team member. To meet tracking requirements, each room needed multiple columns for the technology available inside (Polycoms, Projectors, Surface Hubs, etc.) which were just primitive boolean values at first (at a later date, I added “N/A” to make this field a string).
From this simple matrix of room numbers and their associated T/F columns, Power Apps was able to generate most of the UI and logic flow required for 3/4 of the screens noted above (#2-4). Despite tweaking the design and placement of various fields and titles, I was impressed at the level of automation I had just witnessed. I had not written a single line of code nor implemented any kind of logic flow of my own, and yet I was looking at the generated template for a mobile application that could run on my phone. I had only created an Excel workbook and populated a few cells. Cool, right?
From here, I only needed to create a fourth and final screen for the list of sites. This data was also provided to me by another team member, so just like before, I created a separate worksheet and populated a few cells. Using one of the gallery screen templates provided by Power Apps, I created a new screen and connected it to my new worksheet (this changed my project into a “Canvas” app). Power Apps quickly detected the columns in my table, and before I knew it, the list of sites was present on my screen.
Now I needed a way to navigate from the “Location” screen to the “Rooms” screen and only display rooms present at that site. After all, it would not make much sense to select a site and see rooms from other sites. To do this, I used the Navigate() function:
Navigate(
RoomsScreen1,
ScreenTransition.None,
{ site: ThisItem.Site, siteRule: ThisItem.Rule }
)
This tells the application to navigate to RoomsScreen1 without any sort of transition effect and to pass in two context variables to it. ThisItem.Site is the name of the site, and ThisItem.Rule is a standardized 3-letter site code used as a prefix on every room number.
Thus, whenever the “Rooms” screen is opened, it has the site information within its own scope. To filter on rooms at the specific site, I used the SortByColumns() function. The following tells Power Apps to filter only on room names beginning with the siteRule context variable passed in from Navigate() in ascending order:
SortByColumns(
Filter(
Room_Checklist,
StartsWith(Room, siteRule)
),
"Room",
Ascending
)
Results
When I was finally satisfied with the UI, I saved the current version of the project and published it out to our internal environment for testing. I only had to grant permission to my OneDrive document and share the application with the specific users requesting access. Once granted, these users installed the “Power Apps” container application on their mobile devices and my “Room_Checklist” application was then immediately viewable to them.
This is technically an ongoing project, as usability testing cannot be completed with the “work from home” requirements of the company. Once the application is in the hands of the end users, I intend to update this post with more information and any additional changes that are made to the project’s functionality.
Following are screenshots of the project in its current state.
“Location” screen:
“Rooms” screen:
“View” screen:
“Edit” screen:
Changelog
Update: Mar 14, 2022
This project can be considered completed due to its current use in production. I was able to transfer ownership to the messaging ops teams at the company and my continued contributions are only at a high-level, managerial type position having been the original architect of the application. I intend to append updates to this project post if future changes are requested.