Creating attendance tracker - once user ID is added three times to a list, send email to that user

Question

I'm attempting to create an attendance tracker for a school in a University using SharePoint Lists.

So far, I have created:

List 1 (Students)- a list of all our students with their student numbers

List 2 (Absence Tracker) - where teachers manually would add the dates that a student is absent by selecting the student's user number from lookup column in List 1 and enter the class and date

List 3 (Absence Report)- ideally this would tally up and summarize how many times each student that had been added to list 2 was absent.

Once the total number of absences reaches 3, I want to make a flow to send an email automatically to that student's email (which can be found in list 1) to remind them of our absence policy and send an attachment with info for student support.

I feel like if I could figure out how to tally up the absences for each student from List 2 into List 3, I would know how to make the flow to send the email, but I haven't been able to do this yet. I've seen some other tutorials where you can create a lookup column that would be also a count, but I don't seem to have this option with how I've set things up.

Any ideas how to make this work?

Many thanks!

Answer 1

For anyone that wants to know - I've found my own solution!

I've created a flow that takes form responses them and creates items in a list.

Then get those items and filter them to that student's email.

Then compose a function to see the number of items in the filtered list.

If the number of items is three, then send an email.