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

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.

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
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,789)