04 - Primary School Automation Solution - School attendance automation

Hi all,

How are you? Welcome back to my blog series I'm calling Parent Pulse. This is all about trying to help schools and their administrators get a handle on repetitive tasks and automating them.

In Part 1 we looked at the data structure, part 2, we looked at automating RSVPs and event invitations; Part 3 was all about scheduling and creating the weekly newsletter.

In this post, we are going to look at attendance.

Download the entire 4 part solution here.

School attendance automation Use Case

In the weekly newsletter there is an area for attendance figures, these are the percentages students have attended their respective year group over the course of a week.

100% is calculated as:

All students in the year group attended both AM and PM sessions, Monday to Friday. I would like to get to something like below:

This will be the premise to the school attendance automation.

Introducing the Model Driven App

I have created a Model Driven app, forms and views for all the tables we created in part 1 of this blog series. I am not going to go into detail about how the app was created, but I do want to show you some of the data

One of the area is Attendance Records. Currently this is made up of synthetic data, but it captures the status of a student (whether they are present, late or absent) for an AM or a PM session every day of the week):

We can create a Power Automate cloud flow that will, calculate the total number of students in each class, then count the number of non-absent attendance records for each student and calculate a percentage for that week, and then update the Attendance section of the weekly news letter.

Let's crack on with Power Automate

The cloud flow is going to be an Instant cloud flow, as we might want to incorporate this as a button on the Model Driven app Attendance record ribbon at some point.

We will be passing the Start and End Date of the week we need to calculate the attendance percentages for:

We then need to Initialise variables for each year group, this will be used later to count the total number of non-absent records we get back from our Attendance Records:

Next I have added a scope step which holds all the list rows I need to do on the Student table by Year Group. When I created the Year Group choice field on the Student table, the values I gave were indicative of the year group name. Reception = 0 , Year 1 = 1, Year 2 = 2 and so on, this makes it easier when you need to filter:

This then gives us this scope:

Next we have another scope, this time, I am multiplying the number of Active student rows per year group by 10, as there are 10 sessions per week, one AM and one PM session every day:

Next I list all the attendance records which are not marked as absent (this includes Present and Late records), I am using a filter query here based on the start and end date as well as filtering on the Attendance Status not being equal to Absent:

This next screenshot is hard to see exactly what is going on, but I have a Switch condition that checks for the value of the year group, and then increments the respective count by 1:

I then have one final scope which divides the year group count by the year group session count and multiply it by 100.0 to get a percentage:

Finally, I use the Populate a Microsoft Word template step and the create file step in the SharePoint connector to put the figures in the weekly newsletter:

And we get the following result:

Thanks again for reading, if you have any questions please get in touch.

Download the entire 4 part solution here.