cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ekoro
Frequent Visitor

Collect Data via Forms, apply Excel logic, send summary email

Hi all,

 

I've been wrestling this for the past few days, and wondered if anyone can help. I am trying to send an email to Form respondents with a summary of 

 

I have a survey set up in MS Forms to collect real time data for inspections. The excel table (Table 1) linked to the form has a few additional columns appended to it where I've applied rules and logic related to the data (ie: if col A="yes" AND col B="no", then col X="PASS"; col z is a sumifs fx to calculate the total inspections completed in a month). This all works really well in Excel Table 1.

 

Where I am struggling is populating a summary email with dynamic values. I thought I could add Dynamic Values using "Send an Email v2" using the html editor to reference the appropriate summary columns (ie: col x: PASS/FAIL output, col z: total inspections complete this month). Theoretically it works, but the email Dynamic values are not being returned for any of the appended columns. I've tried adding multiple delays in the flow, but these don't seem to help.

 

As my most recent attempt, I created a separate Table 2 to capture the summary data and pull dynamic values from Table 2 only. Even from this, the only data I get is data collected directly from the Form on Table 1.

 

Here's a high level view of that most recent flow - I realize there are probably more "delays" here than I need, especially because they don't help anyway, it seems. Previous attempts basically resembled the below, but did not include the 3 separate "Table 2 steps".

 

Any help is greatly appreciated - apologies if this has been tackled elsewhere in the forum.

 

ekoro_0-1648482534654.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
ekoro
Frequent Visitor

Thanks for the reply @ekarim2020 ,

 

Technically I captured response details with the "Get a Row" action tied to the Form Response Table (Table 1). I actually found a workaround that I should post for anyone else who may need it.

 

After trying everything else I could think of, I created a separate Excel Workbook dedicated to the email summary data only, referencing the Key column of the Form ID from Table1's "ID" column, then updating the row that matches  Key column "ID" on NewWorkbook's Table2 with the "ID" column in Table1. When the row on NewWorkbook Table2 is updated with Table1 content, the excel formulas run mostly as expected on Table2 and the email is created with dynamic values from Table2.

 

The only excel formula trouble I had was converting the Completion Date data to a format that could define submissions by month, so I had to run an output from a series of variable and compose actions.

 

It's surprising to me that I had to create a separate table instead of the source Table1 that is created by MS forms - possibly this is a glitch (??), but the workaround is pretty inconsequential and gets me where I want to go.

 

Below is a screenshot of the working flow:

 

ekoro_0-1648647702922.png

 

 

 

View solution in original post

2 REPLIES 2
ekarim2020
Super User
Super User

Hi @ekoro ,

How is your flow getting the forms response? I was expecting to see a Get response details action:

 

Snag_81cc16a.png


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

ekoro
Frequent Visitor

Thanks for the reply @ekarim2020 ,

 

Technically I captured response details with the "Get a Row" action tied to the Form Response Table (Table 1). I actually found a workaround that I should post for anyone else who may need it.

 

After trying everything else I could think of, I created a separate Excel Workbook dedicated to the email summary data only, referencing the Key column of the Form ID from Table1's "ID" column, then updating the row that matches  Key column "ID" on NewWorkbook's Table2 with the "ID" column in Table1. When the row on NewWorkbook Table2 is updated with Table1 content, the excel formulas run mostly as expected on Table2 and the email is created with dynamic values from Table2.

 

The only excel formula trouble I had was converting the Completion Date data to a format that could define submissions by month, so I had to run an output from a series of variable and compose actions.

 

It's surprising to me that I had to create a separate table instead of the source Table1 that is created by MS forms - possibly this is a glitch (??), but the workaround is pretty inconsequential and gets me where I want to go.

 

Below is a screenshot of the working flow:

 

ekoro_0-1648647702922.png

 

 

 

Helpful resources

Announcements
 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Users online (3,468)