A Practical Application and How-to Guide for Schools with Microsoft 365

Back in December of 2020, I shared a brief introduction to Microsoft’s Power Platform in the hopes that school districts would more seriously consider the opportunities available to create meaningful, digital solutions that were already protected under Microsoft’s data privacy agreement (DPA) and required no additional purchases. Who doesn’t love the sound of a product that doesn’t need another DPA and is free?
​
Since then, educators have not been wowed or interested much beyond Power Automate (to automate time off requests, mileage claims, and much more); but just recently, I was presented a simple request seeking a solution for a single scenario, and it evolved into solution for the entire middle/high school. Incidentally, the solution could work for your school too!

Tom Simon, superintendent of Portville Central School (PCS), asked Dave Suain, the director of the Envisioneering Center (the name of the space many schools would think of as a STEM/STEAM lab) to think about a digital solution to simplify the process of students obtaining a pass permitting them to attend the Envisioneering Center. Think about what a digital solution can do to improve the analog process of getting a hall pass in this situation: it eliminates the time that it takes to walk to the Envisioneering Center, it eliminates the time to walk back to class or to the room of the teacher needing to approve the pass, it can instantly notify each teacher as well as the student, etc. Since I help provide on-demand technology integration support roughly once per month at Portville, Dave asked my thoughts about creating the best solution.
​
After a little brainstorming, Dave and I settled on creating the quickest, functional solution possible to show how easy creating digital solutions to workflows can be with Power Automate so we could identify whether a more robust solution in Power Apps was worth the time and effort. Essentially, we created a workflow that is automatically triggered by a student submitting a response to a Microsoft Form containing three questions (What period do you need the pass? Who is your teacher that period? and What do you intend to work on during the period?), sends Dave the data from the Form, creates an approval process that terminates when Dave denies the pass or continues when he approves it, sends the pass details to the appropriate teacher if approved by Dave, and finally creates a Chat group in Teams communicating with everyone involved whether the pass was approved or denied. In not much time at all, the workflow was tested and ready to go.

Thankfully, Mike Torrey, PCS Technology Director, was apt to make sure that the IT department was in the loop during these discussions since technology specialists Wan Leong and Nicole Ramsey provided great support in making sure the workflow runs smoothly. This process will be piloted through the Envisioneering Center with a small group of students who frequent the space after they have returned from winter break.

Without hesitation, Wan acknowledged that the workflow would not be able to handle passes for the entire middle/high school, so we discussed how Power Apps was a much more desirable solution for that context. For example, any time we work with manually entered data, we must account for user error. In the workflow mentioned above, the student manually had to type in the teacher’s name into the Form, and Dave then needed to type in that teacher’s email address correctly in the approval process in order for the workflow to run correctly. In Power Apps, we can use connectors like Office 365 Users, Office 365 Groups, and/or Azure Active Directory (AD) to retrieve both student and staff names and email addresses exactly as they appear in AD so we can be certain the appropriate people are included in any of the notifications.
​
The app is still a work in progress, but we made a great start. It also bears repeating that the app itself is not being utilized at this time, but it is available for future use and development. Use the how-to guide below to get started in your district, too.

Getting Started with the Digital Hall Pass Power App Template
Step 1: Create Three SharePoint ListsLists is Microsoft’s take on what were formerly known as SharePoint Lists to allow users to create lists (i.e. tables or collections of data) without having to establish an entire SharePoint site. Rather, Microsoft Lists is now its own application that can be found by signing into your Microsoft 365 home page and finding Lists in the App navigator.

Although my preferred data manipulation tool is Excel, Power Apps seems to interact with SharePoint much nicer; and since Lists is built directly on SharePoint, Lists are the recommended data source for beginners. Power Apps allows for other connections such as Microsoft Dataverse, Access, or a SQL server, but most people will not have a need to interact with these more complex alternatives. Lists is also a great application for monitoring and sharing item inventories, tasks, and more since it can be shared with viewers and collaborators in the same manner you would share a file from your OneDrive.
​
For the Digital Hall Passes Template, you will need to create three lists, each of which using the same column titles and column types (it will be less work if you completely establish the first list and copy it as a template):

1.  Active MHS Passes
2.  Last 30 Days MHS Passes
3.  All MHS Passes History

Step 2: Create an Automated Workflow in Power Automate
​In order to help the app run more efficiently, it was not designed to delete records from any of the SharePoint Lists but rather modify specific records for their respective approval statuses. Therefore, the process also requires an automated workflow in Power Automate to remove expired records from the Active MHS Passes list, delete records older than 30 days from the Last 30 Days MHS Passes list, and update the PassStatus to PAST in both the Last 30 Days MHS Passes and All MHS Passes History lists if either adult did not acknowledge the pass before it expired.

This workflow requires four steps outlined below:

1. Create a “Scheduled cloud flow” to run once every day. We scheduled the flow to run at 5:00AM, but you could schedule the flow to run at another time such as 11:59PM or 12:00AM.
   
2. Step 2 occurs in three branches; however, only two of the branches are shown in the image below.
   
   1. Use the SharePoint connector to “Get items” from the Active MHS Passes list. You will need to enter the SharePoint site address for wherever your List is stored. If you created a list on a specific SharePoint Site, select the drop-down and choose that site. If you created the list using Microsoft Lists, you will need to enter a custom value using your account’s personal SharePoint site address; you can get this link from the List URL (i.e. https://***********-my.sharepoint.com/personal/username*******org/). Select the Active MHS Passes list under List Name.
      
   2. Repeat Step 2a using the Last 30 Days MHS Passes list.
      
   3. Repeat Step 2a using the All MHS Passes History list (not shown in the image below).
      
3. Create a Condition control to check the information for the item in the list from the previous step. (NOTE: creating this step will automatically create the “Apply to each” control that cycles through each value of the list. The “Apply to each” control is labeled Active Pass in 3a and Last 30 Days Pass in 3b)
   
   1. Set the Condition to Or with the following three conditions:
      
      1. formatDateTime(items('Active_Pass')?['PassDate'],'yyyyMMdd') is less than utcNow(‘yyyyMMdd’)
         
      2. ToStatus is equal to DENIED
         
      3. FromStatus is equal to DENIED
         
   2. Setup the condition such that the expression utcNow(‘yyyyMMdd’) is greater than formatDateTime(addDays(items('Last_30_Days_Pass')?['PassDate'],30),'yyyyMMdd')
      
   3. Set the Condition to And with two conditions:
      
      1. It checks if passes are still listed as PENDING
         
      2. It checks if PassDate is prior to today
         
4. Lastly, we want to update the list.
   
   1. If 3a is true, then create a SharePoint “Delete item” action that deletes the Active MHS Pass record if it is expired or has a denied status anywhere.
   2. If 3b is true, then create a SharePoint “Delete item” action that deletes the Last 30 Days MHS Passes.
   3. If 3b is false, then create a SharePoint “Update item” action that changes the record’s PassStatus to PAST (not shown in the image below).
   4. If 3c is true, then create a SharePoint “Update item” action that changes the record’s PassStatus to PAST (not shown in the image below).

Step 3: Import the Digital Hall Passes Template in Power AppsAmong the many benefits to Microsoft 365 is the ability to collaborate and share resources; thankfully, Power Apps shares this benefit making it simple for district leaders to download the Digital Hall Passes .zip file and upload it to Power Apps by selecting the “Import canvas app” option.

Upon importing the app, you will need to update the import to create the file as a new app, and after minimal processing the open will be ready for a few final touches to make it operational:

• StudentHomeScreen
  • StudentHomeScreenNewPassGroup
    • FromChoice – update the Office365Users to include the appropriate Azure AD group/object ID (this can be found at portal.azure.com)
    • ToChoice – update the Office365Users to include the appropriate Azure AD group/object ID (likely to be the same as FromChoice; this can be found at portal.azure.com)
• StaffHomeScreen
  • StaffHomeScreenStudentSearch - update the Office365Users to include the appropriate Azure AD group/object ID (this can be found at portal.azure.com)

Step 4: Consider Making Improvements
Before sharing this app with students and staff, I would recommend making several other adjustments that may not be necessary but will give the app the personalization it deserves for your district. I have listed only a few, but don’t let your imagination stop there.

• Replace the LoadScreenLogo with your district mascot.
• Change the color scheme on LoadScreen (changing the fill color for the screen as well as the fill color for the LoadScreenBackground object), on StudentHomeScreen (changing the RequestPassLabel fill color in StudentHomeScreenHeadersGroup), and on StaffHomeScreen (changing the fill color for the screen).
• Make the app a responsive app using containers, so it adjusts to the screen size of the user’s device.

Where Do We Go from Here?If you are considering using the Digital Hall Passes Template or you would like help making improvements, then I think a great first step would be to ask for help, and we can work through the process together. In an ideal world, Microsoft EDU and PowerSchool would respond to my invitation to collaborate so the Digital Hall Passes app could serve as a replacement to services such as eHallPass.com that, among other things, can limit the number of students in the halls during any given period; this feature would allow the app to serve as an approval process for brief passes (i.e. passes to the office, restroom, water fountain, etc.) as well.
​
Beyond that, I say we should go to the drawing board and think of all the ways we can create in-house, digital solutions that require no additional purchases or DPAs.

By: Mark Beckwith, CA BOCES Professional Development