Send Email with options - add multiple options from excel file

[JB1972]

Hello

I have a an excel file with the following

In my flow I have a Form where you can select a RideAlongExperience.   If you select "Contact Centre - Service" there are two dates available as per the file above.

How can I use the above file to dynamically insert the 2 date into the User Options field in the step I have in my flow below?

Thanks JB

[ekarim2020 · Accepted solution]

Here is one approach to the issue.

(1) Starting with your Excel file with the following rows:

(2) This is the demo form Ride Along Experience that users will be completing. Note that the values in the Excel column RideAlongExperience match exactly the values in form's choice field:

(3) We now construct the flow: Get the form response and read all the rows from the Excel file. Note the DateTime Format is set to ISO 8601 in the List rows present in a table action:

(4)  We have read all the rows from the Excel file above. We now want to get (filter) all rows with experiences that match what the form submitter had selected. For this step we can use the Filter array function:

(5) Next, we select the Date column from the results returned by the filter array action above.  We  just want the dates and we will be using these dates as options in the Send email with options action below.  We can use the Select action. The Select action will return the results to us as a collection (array). For example for "Contact Centre - Service" we get ["20/11/2021", "21/11/2021"] from the Select action.

(6) Finally we construct the email with options. The email is sent to the responder and the user options will be the dates we got from the Select action above.

Each User Options in the Send email with options action needs to be separated with a comma. We can use the join function to join the date strings for the User Options. For example, the join function can take the following array ["20/11/2021", "21/11/2021"] and return "20/11/2021", "21/11/2021 as the User Options.

This is the output of the flow.

The form is submitted:

The responder receives an email with date options to select:

The responder selected the date 21/11/2021:

Hope this helps.

Ellis
____________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.

[JB1972]

Thank you Ellis.  I've added the above to my flow and works!  Much appreciated.  JB

[JB1972]

Hi me again

Further to the flow above I how wish to update the excel file above.  As per the image below I have added 3 columns Booked (First Name), Booked (Last Name) and Booked (Business Unit)

What I want to do is get (and trying to do) is to filter the Excel file based on Condition #1 the RideAlongExperience selected, and Condition #2 the output date from the send email with option.  With the output from this filter I then want to update the row in the filtered excel table using the output from the Form Reposnse Detail and apply then to their respective columns; 

Booked (First Name) - , Booked (Last Name) and Booked (Business Unit)

Ellis could probably help as he did also with other parts of the flow.

Thanks

Justin

[ekarim2020]

Based on a form like this:

We can update an Excel spreadsheet that has a format like this:

You need to think about what happens:

(1) If an experience is already booked?

Perhaps you need to add status column which can use to filtered for available experiences. These experiences can then be emailed to the responder?

(2) The first person to respond

It is also possible for the experience to be emailed to multiple responders. The first person to respond will be the person who books the experience. You will need to check if the experience is still available (Status is not equal to Booked) before updating the Excel file. That means you will have to manage a valid booking and decide what to do for booking that is no longer available.

(3) Is the form only going to be completed by users in your MS 365 tenancy or by external users?

You can see the how the flow gets more complex the more rules and conditions you need to manage.

Free Flow charting software see: https://www.diagrams.net/

Ellis

[JB1972]

Thanks Ellis again.  I had thought about the impact of multiple people trying to book the same experience.  I will add a status column to check availability at the 2 decision points above and the Y/N responses flows.  And work on updating a row.  Give me a few days to get it working, I'll update you shortly.  Thanks again for your input.