cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Ciprian
Level: Powered On

Flow to send email based on distinct column email with a bit of boolean to make life harder

Hi everyone,

 

After two days of pulling my hair out and googling everything I could, it seems I can't crack this nut.

 

I have a SPO list that tracks a monthly attendance with the following columns: Student (Person column), Supervisor(Person column), Month (text), MeetingsWithStudent(number), LeftCollege (boolean Yes/No column).

StudentSupervisorMonthMeetings with studentLeft college?
Student ASupervisor ANovember3Yes
Student ASupervisor AOctober2No
Student ASupervisor ASeptember3No
Student BSupervisor BNovember2No
Student BSupervisor BOctober2No

I want to send a monthly reminder to each supervisor reminding them to add a record for their student for that month unless the student has left the college.

 

Not really sure how to solve this.

 

I got halfway there with getting unique values from the Supervisor column so I now have a unique array of Supervisor emails however it includes supervisors from students that left as well.

 

Example:


Student A might have 4 records: June, July, August, September. If September is marked as true for StudentLeft then the idea is to not email that supervisor the reminder anymore.

 

The way I picture this in my head is to group the list by Student then sort the result by ID(can also use Created) then check if the last record in the array has StudentLeft as false/blank, if so then email the supervisor.

 

Have no clue how to do this, driving me mad, any help would be greatly appreciated!

 

Thank you very very very much!

 

The best of wishes,

Ciprian

 

 

4 REPLIES 4
dbro
Level 8

Re: Flow to send email based on distinct column email with a bit of boolean to make life harder

Hi @Ciprian ,

 

perhaps i'm missing something, but have you tried using the Get items action?  Then entering an ODATA query in the Filter Query section.

 

Like:

LeftCollege eq false()   

 

False would be an expression. This would get you all items where the student has not left college. From there you can apply to each result and send your email.

 

FlowOData.jpg

 

If this helps solve your problem, please mark this answer as complete.

Ciprian
Level: Powered On

Re: Flow to send email based on distinct column email with a bit of boolean to make life harder

Thanks @dbro 

 

I've tried that, the problem is that it will still pick up the older records of the student where 'Left College' was true. 

 

The data looks like this

StudentSupervisorMonthMeetings with studentLeft college?
Student ASupervisor ANovember3Yes
Student ASupervisor AOctober2No
Student ASupervisor ASeptember3No
Student BSupervisor BNovember2No
Student BSupervisor BOctober2No

 

The above would mean the student was engage with studies until they left the college and from that point on Supervisor A shouldn't receive any more notifications.

 

Thanks.

 

Ciprian

 

dbro
Level 8

Re: Flow to send email based on distinct column email with a bit of boolean to make life harder

So you want to email the supervisor monthly if the Left College is no and it's the current month?  If so, the following should work, though you may want to add a text value for month in your list as shown:

 

FlowOData.jpg

 

Ciprian
Level: Powered On

Re: Flow to send email based on distinct column email with a bit of boolean to make life harder

Thanks for that @dbro. Your flow is great though the trouble is for months that are not the current one. For example I have supervisors that forgot to submit and are behind. So if a student 's last attendance record is September than the flow won't pick it up. I think I need another approach where I group the list by student and somehow process each student's last record. Don't know how but for now I have created a flow that creates two separate arrays of supervisors' emails, one for students that left and one for all students. It then creates an except array with the difference between the two, then sends an email reminder to this result. It's not perfect and it's flawed because a supervisor might have more than one student. I need to somehow find a solution where a flow that groups the list by student and then checks the most recent item under that student then uses your flow to check due date.

 

Thank s for your help anyway, much appreciated.

 

Ciprian

Helpful resources

Announcements
firstImage

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

firstImage

Incoming: New and improved badges!

Look out for new contribution recognition badges coming SOON!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Solution Authors
Top Kudoed Authors
Users online (6,347)