Covid and MS PowerApps

Oct 19, 2021 · 6 min read · PowerApps

Some of my notes on how we used PowerApps to solve some COVID problems at work

I should have done this sooner, because I'm not likely to remember all the details too much. This won't be a "technology post" so much, or even a how-to - maybe more a reminder of the tools we used to solve the problems we ran into when COVID came to town.

The Visitor Screening App

Out of the gate, we all went home, and for a brief period, it was fine. One location outright closed up, the other location practised what were considered good practices at the time - distancing, cleaning, no sharing, masks and most importantly: No visitors. As we all collectively learned more about the pandemic and looked to re-open, visitors (as in, non-company employees) were being welcomed back into the building.

We needed a way to perform the standard COVID-19 screening questions, and collect some data from the visitors. We also had to really tightly manage who had access to the data, what purpose was it used for and all that good stuff. We considered getting the in-house software development team to write something, but we were concerned that it would cut into their "productive" time - so why not use my time (because, you know, I'm not productive anyway).

These PowerApps can either run as a "portal" - and requires them to be licensed accordingly. This is fine, but it was never made clear to me just how to license it. In the end, I created a user, licensed it with E3, then set the tablet to log in automatically, open the portal website, and be done. It was a clean solution, and didn't require my VAR to figure things out.

I was aware of PowerApps and MS Flow (now called Power Automate), so off to work I went. I wasn't clear on how to store the data - I'd considered an Azure instance of SQL, briefly explored the connector option to use the on-prem SQL server as an option, but learned that PowerApps seems to be geared to use SharePoint. So, off to SharePoint Online I go.

I (had) no experience here, so here comes the learning curve.

I figured out that I was able to make lists within SPO, and lists can refer to items in other lists - so like a foreign key in a relational database. With that in mind, the "database" structure starts to look a bit like this:

Basic entity diagram of the screening app

Since this was enforced in SharePoint, the overall forms got brought into PowerApps just fine. A few small tweaks:

Between pages in the app, it would be necessary to know the UniqueID from the Visitor Record table. This was accomplished by adding a hidden field to the children forms, and setting it's default data to:

1{
2    '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
3    Id: BasicVisitor_Form.LastSubmit.ID,
4    Value: BasicVisitor_Form.LastSubmit.ID
5}

Where BasicVisitor_Form is the name of the Form object on the initial record creation page.

We wanted a confirmation window on the COVID question to confirm that a visitor REALLY did mean to check yes on an item. This was achieved with information from Creating a dialog in PowerApps.

Small Demo

The first page of the app collected the visitors name, company, and asked for a company contact. In the event of a COVID case, we would use this company contact to facilitate communication to the visitor.

The visitor's name and company would be carried in from the first page, so the person would not have to re-enter it. Nothing terribly fancy here.

The default behaviour for a SharePoint choice column is to be a combo box. We found these to be annoying, so we changed them all to be these switches. This was cumbersome to change for each field, even moreso when the request was made to make them stand out a bit if a no selection was made (the red colour). If any options were left at no, a confirmation window would appear before proceeding to the next step.

The tablet would get handed back to the receptionist, and they would then verify that the visitor was allowed to enter the building. We had problems where people would just keep tapping the "next" button, not allowing the receptionist a chance to actually conduct the screening...

The final page would show the results, with highlighted rows where the visitor had selected yes. We found that visitors would just hit the Back to start button, making it impossible to do the screening. We added the reset PIN field above the Back to start button so that a PIN would be entered to enable to button.

Future State

In the end, it served its purpose. The application (and tablets is was running on) were replaced with an iPad and a website-as-a-service after over a year or so of it's use.

It had it's issues - the occasional random crash, records not getting put into SPO.. but 99% of the time, it was great - the 1% kinda sucked though :P

Vaccination Status

After we've been through the screening app efforts, and switched to what would be permanent solution (that also addressed some challenges that the Office Admin team had been facing), we started to look at satisfying safety requirements for returning back to the office. In this, it was time to deal with working with employee's vaccination status and information.

We had a bit more lead on this one - we knew we would be doing this, and we watched our parent company carefully as they navigated this scenario. We engaged legal, records management and HR (who were driving this effort). With additional time, we also had some time to get an idea of what the workflow should look like - how a user would be interacting with parts of the system:

With this in mind, we were a bit more prepared to put pen to paper.

Different use case

Out of the gate, this application would be used differently than the screening portal. Other staff members would be using this, so instead of controlling the computer and application all the way though, it would be necessary to allow users to access the PowerApp, as well as the underlying SharePoint site. I needed a way to surpress the user consent prompt that you get on the first run of a PowerApp.

Because there is less data being collected than the screening app, it made a bit more sense to make this a single screen app. This should make it a bit easier to onboard staff.

Separate apps

We were under a bit of a timeline, so we plan (present tense!) to release the user facing application to collect the data, and we would follow up with the Admin application after. This simplified the user application, so it would be deployed basically immediately, and provide the time necessary to develop and test the Admin application for the HR staff.

Posts in this Series