Hey,
How are you doing? Welcome back and this is part 3 of my blog series on the Parent Pulse solution.
You can find previous posts here:
Check part 1 for an introduction into why we are doing this and the background behind it.
Download the entire 4 part solution here.
What is next? Automating School Newsletter
I have noticed with the school that my daughter's attend, there is a weekly newsletter that goes out to the whole of the school, It has regular sections like:
Dates for your diary
Advance Notice diary dates
Attendance
A message from the headteacher
Parent Teach Association News
Each of these sections could be automated, the school administrator could ask for the content from various members of staff before hand, and then on Friday the newsletter is compiled and sent.
For this we could utilise SharePoint as a document store of the newsletters as well as the place where teachers can submit content for each section.
Over to SharePoint
I am going to create a new site in my tenancy for my fictious Oakridge Primary school. As a SharePoint administrator, go to office.com and then click on the Apps > SharePoint.
Click on Create site > Team Site > Standard Site > Use Template, and then I am going to give my site a name of "Oakridge Primary", I'll also add myself now as a member, then click finish:
We are taken to the Oakridge Primary SharePoint Teams site homepage:
I am going to head over to Documents, and create a new folder called Templates. I am also going to create another folder called Weekly Newsletters:
We will come back to SharePoint later possibly to add more folders, but for now that's good.
Word Template
For this next part, we need to make sure we have access to the desktop version of Microsoft Word. We need the desktop version, because that has Developer Tools, and is required to complete this next step.
Word templates, can be super powerful. I am not going into super detail here, but you can find a really helpful blog post here, which helps you set and configure one.
I have created a word document template, which we can now use Power Automate to populate:
This template has followed the instructions outlined in the linked blog above. For now I am just going to concentrate on Key Dates, and Advance Notice dates as part of this template. But it can be extended for the use cases above.
Back to SharePoint
Now that we have the template, we need to upload that to our Templates folder we created above. We also now need to create a SharePoint List where we are going to hold this information.
At the moment, we have two main components, dates, and the edition number of the newsletter. The Edition number can be handled pretty easily, and we will get to that in due course. But lets create a SharePoint list and call it Key Dates.
Navigate to the home page of your teams site, and click on New > List > Blank List. Give it the name of Key Dates and click Create.
We are given a new blank list with only a Title column:
We can leave Title as it is, as we will use that for the name of the event. But let's create three new columns:
Description (Multiple lines of text)
Date
Year Group (Choice: Reception, Year 1, Year 2, Year 3, Year 4, Year 5, Year 6, All School)
Newsletter section (Choice: Key Dates, Headteacher Message, PTA News)
 |  |
For now, we are going to add some key dates, with associated title, description and year group. I have created around 40 records and added them to my SharePoint list:
Let's jump over to Power Automate
Now that we have our dates in a Key Dates list, we can start working in Power Automate. The newsletter is going to be sent to the whole school's parents/guardians, and we only need to pull in the relevant events for that week's newsletter.
Let's create a new scheduled Power Automate cloud flow and we will call it "Weekly Newsletter creation and send":
As we can see from the screenshot, we will only be sending this out once a week, every Friday at 14:00.
Next we need to get the Week of the year, to do this, we need to use this expression:
div(add(dayofyear(addDays(subtractFromTime(utcNow(), if(equals(dayofweek(utcNow()), 0), 6, sub(dayofweek(utcNow()), 1)), 'Day'), 3)), 6), 7)
Bit of a mouthful, but it does the job:
Next we need to list our items in our SharePoint list where the Date of the event is less than or equal to 7 days from today. We can do this by using the Get Items SharePoint action and use the name of the Date column from our SharePoint list (in our case "Date") and then using le (for less than or equal to) and add that with the adddays expression:
adddays(utcNow(),7)
Now that we have that we need to add this to our Word document template. Before we do that, we need to create a string array variable, as there may be multiple key dates in the next 7 days.
Above the compose step, add a new action "Initialize variable" and set it's type to string, and call the variable "varKeyDates". We can also add one for Advance Notice Diary Dates too:
Next from the values that are returned from the Get Key Dates happening in the next 7 days action above, we need to append the Title, Description and Year group (value) to the varKey Dates variable:
We can then use this in the word template when the time comes.
Following on from that, we need to do another Get Items action, this time to get events which are happening more than 6 months away, and then as we have done before append these values, this time to the varAdvanceNoticeDiaryDates variable:
Next, we then use the "Populate a Microsoft Word template" action:
This adds all of the key dates from the string variables, and adds the edition number (week number, from the compose step).
We can then do whatever we like with this document, we can email it to the parents/guardians of the entire school.
This completes Part 3 of the Parent Pulse solution.
Thanks for following along, next time we will look at the Model Driven App, and the processing of attendance.
Thanks, any questions please get in touch.
Download the entire 4 part solution here.
Comentários