Hi,
How are you?
Introduction to the Primary school automation solution
As I might have mentioned in previous blog topics, or when I have spoken at User Group sessions, I often mention my children, Esme and Tilly. They are at primary school at the moment, and there are various school clubs that they attend, various notifications that come out from the school. Schools are very busy, I can imagine the administration side is a very large amount of effort. I wanted to look into this a bit deeper over the holiday period, with the idea of volunteering at their school to help to see what could be automated.
I want to build a Primary school automation solution that primary school's could use, to be able to help unburden the load of the school secretary/administrator , so that they can focus on other tasks that really require their full concentration.
Implementing a solution like this would help streamline processes, reinforce better communication with parents, and ensure for better data processing/clarity.
This is part 1 of this series of blog posts.
Pre-Requisites
One of the biggest considerations must be cost. To be able to automate this, ideally would need a Power Platform environment with Dataverse as well as a user set up with Power Platform premium licensing. As I write this Power Apps Premium licensing for a single user is $20. But I am aware that there are educational pricing plans which could further reduce this. Because of the sensitivity of the data, Dataverse as the data store solution is preferable over SharePoint. That is not to say that we can't use SharePoint for static data lists, that we can then refer to in our automations.
Download the entire 4 part solution here.
Where shall we start?
We must first start with the use cases, I have thought of some, but this might not be an exhaustive list:
1. Attendance Percentage Calculation
Use Case: When a weekly student register is completed, a calculation to show the attendance percentages per year group is calculated.
Trigger: School admin using a Model Driven-app to confirm the weekly registrations are complete.
Automation: Power Automate calculates attendance percentage and updates a weekly newsletter for parents.
2. Event Reminders and Sign-ups
Use Case: Send reminders about upcoming school events, such as parent-teacher meetings, field trips, or extracurricular activities.
Trigger: Calendar event nearing date or manual form submission.
Automation: An email is sent with RSVP options or forms. Power Automate can track responses and generate reminders for those who haven't responded.
3. Emergency Announcements
Use Case: Send mass emails during emergencies, such as school closures due to weather or unexpected events.
Trigger: Manual trigger by the school administrator.
Automation: Send templated emails to all parents quickly, ensuring consistency of messaging.
4. Homework and Assignment Reminders
Use Case: Automatically notify parents about important homework that has been issued by the class teacher.
Trigger: When a homework record is created,.
Automation: Power Automate sends an email to the parent with the details of the homework, plus the link to any documents they need access to.
5. Weekly Newsletters and Announcements
Use Case: Send a weekly or monthly school newsletter to all parents summarising upcoming events, highlights, and updates.
Trigger: Pre-scheduled time (e.g., every Friday morning).
Automation: Power Automate merges announcements into a formatted email and sends it to parents.
6. Recognition Emails
Use Case: Send recognition emails for achievements to parents.
Trigger: Date field in the system (e.g., birthday).
Automation: Email templates personalised with the student’s name and achievement.
7. Extra Curricular Activities
Use Case: Send emails to parents/guardians of children who attend extra curricular activities, when there is a time change or a notification needs to be sent
Trigger: Entry of an update to an Extra Curricular Activity
Automation: Email automatically sent to Parent/Guardian.
Wow, that's a few use cases. Next we need to think about the data.
Entity Relationship Diagram
I have created an ERD to visualise what the tables look like in our solution, some of the column and table names may get edited/added to or amended as we get into the build.
1. Table: Student
Purpose: Stores details about each student.
Table Name: Students
Key Fields:
Student ID (Primary Key)
First Name (Text)
Last Name (Text)
Date Of Birth (Date)
Year Group (Choice)
Teacher (Lookup → Teachers)
Parent ID (Lookup → Parents)
Address (Text)
Health Notes (Multiline Text)
2. Table: Parent/Guardian
Purpose: Stores contact information for each student’s parent or guardian.
Table Name: Parents
Key Fields:
Parent ID (Primary Key)
First Name (Text)
Last Name (Text)
Email (Email)
Phone (Phone Number)
Relationship To Student (Choice: Mother, Father, Guardian, etc.)
Preferred Contact Method (Choice: Email, SMS, Phone)
Address (Text)
3. Table: Teacher
Purpose: Stores details of teachers for linking to homerooms and communications.
Table Name: Teachers
Key Fields:
Teacher ID (Primary Key)
First Name (Text)
Last Name (Text)
Email (Email)
Subjects Taught (Text)
4. Table: Attendance Record
Purpose: Tracks student attendance and absence information.
Table Name: Attendance Records
Key Fields:
Attendance ID (Primary Key)
Student ID (Lookup → Students)
Date (Date)
Attendance Status (Choice: Present, Absent, Late)
Notes (Multiline Text)
5. Table: Event
Purpose: Tracks school events and enables parent RSVPs.
Table Name: Events
Key Fields:
Event ID (Primary Key)
Event Name (Text)
Event Date (Date/Time)
Location (Text)
Description (Multiline Text)
Organiser (Lookup → Teachers)
RSVP Required (Boolean)
6. Table: RSVP
Purpose: Tracks parent responses to event invitations.
Table Name: RSVPs
Key Fields:
RSVP ID (Primary Key)
Event ID (Lookup → Events)
Parent ID (Lookup → Parents)
Response Status (Choice: Accepted, Declined, No Response)
7. Table: Communication Log
Purpose: Logs all email communications sent to parents.
Table Name: Communication Logs
Key Fields:
Communication ID (Primary Key)
Parent Guardian Name (Lookup → Parents)
Student ID (Lookup → Students)
Date Sent (Date/Time)
Message Content (Multiline Text)
Status (Choice: Sent, Failed, Pending)
8. Table: Extra Curricular Activities
Purpose: Logs all email communications sent to parents.
Table Name: Extra Curricular Activity
Key Fields:
Extra Curricular Activity Name (Primary Key)
Start Date (Date)
End Date (Date)
9. Table: Extra Curricular Activity Participants
Purpose: Logs all email communications sent to parents.
Table Name: Extra Curricular Activity Participant
Key Fields:
Extra Curricular Activity Participant ref (Primary Key)
Student Name (Lookup → Students)
Extra Curricular Activity Name (Lookup → Extra Curricular Activities)
Relationships and Connections:
Students → Parents (One-to-Many)
Students → Attendance Records (Many-to-Many)
Students → Extra Curricular Activities (One-to-Many)
Parents → RSVPs (One-to-Many)
Parents → Communication Logs (One-to-Many)
Events → RSVPs (One-to-Many)
Extra Curricular Activity Name → Extra Curricular Activities (Many-to-Many)
Automations
Let's start with Automations. The first on on the list - Event Reminders and Sign-ups.
What we want to do here, is have the ability for the school administrator to be able to create Events, set whether or not the event needs to be responded to by the parents (RSVPs) and then send out reminders if, say 1 week before the event date, the parent hasn't responded. Events at my daughter's school are either sent out by year group (like school trips) or to the whole school like a Christmas Carol concert for example.
In our data, we have the Events, RSVPs, the Student and the Parent tables.
We need to first of all create a trigger, that will check to see if an Event record's RSVP status has been set to yes.
Let's create a new Power Automate cloud flow and call it "Event | Update Create | RSVP equals Yes", we only want it to trigger when the RSVP value is true:
In this step, we are checking the Events table, and if the RSVP Required value changes, in the trigger condition of this step I have added:
@equals(triggerOutputs()?['body/jdr_rsvprequired'], true)
Which means, that the flow will only trigger if the RSVP required on the Event record is Yes (true).
If you want a quick and easy way to help you write trigger conditions, look at my filter FTW post.
Next up, we need to see if the Event is for the whole school or it's just for a particular year group.
I have created two events, the summer fair (think circus, carnival atmosphere, and lots of hand made bunting), or a Year 2 school trip to the Zoo. Bonus !.
We will need to check what our event is, so we need a Switch and we also need to get the values of the choice column for the Year Group Attending Value. As you can see in the below screenshot, the value for All School is 9, so this will be our switch case, we can then leave the default side as is, as this will cater for individual year groups
The Switch:
If the event is setup to go to the entire school, then we need to add steps to the left hand side of the Switch action.
First, we need to get a list of all Parents/Guardians , so that we can get their email addresses. We will add a List Rows step, and we will just get their email addresses and full names and we also only want the rows which are active:
Let's test this, and see what happens. Ensure you have your School Event setup, and set the RSVP to yes and that the Year Group Attending is set to All School.
The flow runs and we can see that the list rows step has completed successfully:
If you click on the "Click to download" link, and you have rows inside your Parent Guardian table, then you will see that rows are returned.
I have actually made a slight modification to the List Active Parent Guardian Rows, so that it only returns records, which have my email address in. This is useful for testing.
Once we have this, we then need to Add a row to the RSVP table. This will add an RSVP record attributed to the event we want RSVPs for (from the trigger) and associate with the Parent / Guardian record:
Note here for the Event Name and the Parent name, I am using the syntax to add the lookup value for the Event and Parent Guardian record respectively. I have also set the Response Status of the RSVP to No Response.
Next we need to use the "Send email with options" action from the Outlook for Business connector. Here we are going to send an email to the Parent/Guardian and ask them to Accept or Decline the invite to the event. We can then use this Selected Option value in a condition step to set the Response Status value of the RSVP record we just created.
Based on that condition we can then update the RSVP row:
Now we need to scroll back up, and take a look at the Default side of the Switch action. Here we need to list rows from the Students table where the Year group, is the Year Group that has been submitted as part of the trigger. You can ignore the Esme Russell comment, but you might want to add that in with the name of your test student record if you just want to get one record back here.
Next, we need to add an RSVP row which is Year Group specific for each of the parents associated with the event and set the Response Status to No Response:
After that it is pretty similar to the All School side of the Switch condition:
I'll leave it there for now, but in the next blog post, I will show you how to set reminders for these RSVP records, and then we will move on to another use case.
Download the entire 4 part solution here.
In the meantime any questions, get in touch.
Comments