Tracking office occupancy rate with Power Apps

Many of us have worked from home almost the whole spring. But now employees are gradually starting to come back to the office to work again. Little by little.

At the same time many employers are trying to restrict the number of employees that are simultaneously at the office, to practice sufficient social distancing while the COVID-19 pandemic is still upon us. This is a worthy goal, but how could we managed the process in practice?

Several organizations are struggling with this problem currently and seeking mechanisms that would easy enough for everyone to follow. I have heard of one company where all the employees are divided into two groups. Group A is allowed to work at the office every other week, then it’s teurn for group B the next week. This ensures there are max 50% of the workforce at the office at any given time.

Clever, but we can do better.

My Office Days – Power Apps

Let’s build a Power Apps Canvas app for this. In short, what we’re gonna do is:

• Define all the offices and how many persons each of them can handle in this new situation. You can list the whole office as an item or divide them into floors or even smaller entities (like Office 1, Floor 2, Area B)
• Users enter information about when s/he is will be at the office (and in which specific location)
• Every user has their own preferred office. By default the office days are spent working there, but the user can change this for each office days separately if needed
• The users can book their next week’s office days after Friday 11.00

In this particular scenario all the information is stored into SharePoint Online Lists. Not as convenient and flexible as using Common Data Service, but I wanted to build a solution that every (Office 365) organization can use, regardless of their current Power Platform licensing situation.

Don’t worry, a CDS based solution will be built later on.

List 1: Office Usage Summary Weekly

First we need to list all the offices. Every item contains:

• Title of the office (or part of the office)
• Max capacity
• Columns for each weekday. These values are updated when users interact with the Power Apps
• First day of week. This column just tells which calendar week’s data we are dealing with

Yes. This solution keeps track of only the current week’s numbers – no historical data.

List 2: Office Usage Weekly

Next we’ll create a list storing each user’s office days for the current week. Every user has their own item in this list, containing the following columns:

• Title (user email, just for the information)
• Columns for each weekday. This time value is referring office defined on first list. Value 0 means that user is working at the home
• First day of week. Same as before, the calendar week we’re working with.

We don’t have to create these items manually, as the Power App will populate the data when user launches the app for the first time.

Power App

Then we’ll move to creating a Power Apps Canvas app to read and update the two SharePoint lists we just configured.

The main screen of the app could look something like this:

At the top we show the current user information (image, name and preferred location).

Then we have a gallery for the current week’s working days, where the user can select if s/he is working at home or at the office.

After choosing the Home/Office option for each day, the user just submits the information.

I have described implementation in little bit more detailed in the blog post I published on my personal blog (encrypted in Finnish).

We’ll also create another screen for selecting the office. This screen’s Gallery is linked to Office Usage Summary Weekly list and we can show the capacity and current numbers of employees signed up for each day.

Flow – Prepare data for the next week

Every Friday at 11:00 we’ll start the reservation process all over again. We have a Flow resetting the weekday counters for each office. This Flow also updates the first day of the week value to match next week Monday.

Reporting?

This solution doesn’t support any real reporting on the historical office occupancy rates. This is because we are playing with the current week’s data only, on the minimum level need to generate the user interface for the app. However, if you were to store the weekly data somewhere (SQL, another SharePoint list…) every Friday with Power Automate, then you could offer some interesting analytics like:

• How quickly are people returning back to the offices to work?
• Which offices are the most popular ones?
• Which weekdays are the most crowded?

Conclusion

In this post I described very briefly how you can implement a Power Apps based solution that helps organizations control how many employees work at the offices at the same time.

You can find a little bit more details about the Power Apps (and related Flows) by reading the blog post on my personal blog.

You can also download the Power Apps and Flows from Github.