cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
JamesF12
Level: Power Up

Building a flow for school trips

Hi All

 

I hope there is someone out there that can help. This may also be of use for the wider community. I have setup an online form to take the details of school trips and then managed to dump this data with a flow into a spreadsheet that is shared with staff. I would love to send an email to the member of staff with the students that are attending their trip after the signup deadline has passed. The complication is there could be multiple trips on the same day. This is going to involve looping through data but have no idea where to start. This Flow only needs to work once per day.

 

Capture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Building a flow for school trips

 

Hi @JamesF12 ,

 

This time seems to understand what you mean, you only need to use Recurrence as a trigger, set Flow to run once a day. Please refer to the following method to configure Flow.

 

  • First use List rows present in a table action to get all the rows, and initialize two variables to store Organiser's email and the data that needs to be rendered in the Html table.
  • Here we need to use the Date and time functions to restore the Deadline in the Excel table to the date format. Filter out the rows with the same Deadline date as the day before the current date.
  • Then append the emails contained in these rows to the array variable. And use the union() function to remove duplicate emails.

 

The above operations are similar to grouping by Organiser. The following is the need to send emails according to Organiser.

  • The data should be shown in the email body are also filtered and then summarized into the HTML table.
  • We need to use array variables to integrate the data that needs to be displayed in the Html table, and append it to the array in the JSON object format.

 

Be sure to follow the screenshots I provided to configure Flow, paying attention to every place I mark.

If you have any questions, please provide us with an error message and related screenshots to solve the problem.

Image reference:

OverAll.PNG

Hope it helps.

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

Re: Building a flow for school trips

 

Hi @JamesF12 ,

 

I am afraid I have not understood your needs. Can you give an example of which students need to send an email to each day?

Take the screenshots you provided, for example, today is 2019/12/12, then which students in the table will receive the mail?

 

Also, note that the premise of operating the data in the Excel file in Flow is that the data is stored in a table.

 

Please provide more details and we would provide a proper workaround for you.

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
JamesF12
Level: Power Up

Re: Building a flow for school trips

I probably have not explained things very well.

 

  • The real spreadsheet is in a table so don't worry about that
  • The spreadsheet fields are explained below
    • Students is a field containing the student name
    • Name of the trip - is the name of the trip the student has signed up to
    • Deadline - deadline date of the trip the student has signed up yo
    • Organiser - this email address is email of the teacher,

so if the trigger ran on the 13th December the flow would collect all the students who had signed up to the Disney trip and email them to the organiser email - someone@email.com

 

e.g 

email would look like this 

To : someone@email.com

subject : Disney

 

contents of email 

Alice

George

Bob

James

 

are all coming on your trip.

 

The system gets more complicated when the trigger runs on the 16th of December as their are two trips that the deadline was the 15th, the football and hockey trips.

 

so anotherperson@email.com should receive an email called Footbal with a table of the students - Phil,Daisy,Fred

 

Sport@email.com should receive an email called Hockey with a table of students Chloe, Jess,Bob

 

Of course in the actual tables there is a lot more information such as parental contacts, medical information etc. I simplified the example as I hope I will work it our once the basics are in place.

Community Support Team
Community Support Team

Re: Building a flow for school trips

 

Hi @JamesF12 ,

 

This time seems to understand what you mean, you only need to use Recurrence as a trigger, set Flow to run once a day. Please refer to the following method to configure Flow.

 

  • First use List rows present in a table action to get all the rows, and initialize two variables to store Organiser's email and the data that needs to be rendered in the Html table.
  • Here we need to use the Date and time functions to restore the Deadline in the Excel table to the date format. Filter out the rows with the same Deadline date as the day before the current date.
  • Then append the emails contained in these rows to the array variable. And use the union() function to remove duplicate emails.

 

The above operations are similar to grouping by Organiser. The following is the need to send emails according to Organiser.

  • The data should be shown in the email body are also filtered and then summarized into the HTML table.
  • We need to use array variables to integrate the data that needs to be displayed in the Html table, and append it to the array in the JSON object format.

 

Be sure to follow the screenshots I provided to configure Flow, paying attention to every place I mark.

If you have any questions, please provide us with an error message and related screenshots to solve the problem.

Image reference:

OverAll.PNG

Hope it helps.

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
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!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

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 (Last 30 Days)
Users online (5,352)