cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Submitting Survey (Forms) Triggering Email (Outlook) Capability

My survey in Forms is composed of ranked numbers. Currently, I have a Flow automatically emailing the results of the survey to myself using Dynamic content.

 

I also would like the results of the Form to be emailed to the person completing the Form. And I would like the email to calculate and show an average of the numbers in the survey.

 

Is this possible to do in Flow?

1 ACCEPTED SOLUTION

Accepted Solutions
Anonymous
Not applicable

I'm not sure if you're still looking for an answer to this question, but I have an alternative solution that I use for one of my own Flows.

When a response is submitted >> Get response details >> Create a new item on a Sharepoint list with all of the responses from the Form >> Get item. For Item Id, use dynamic content "ID" from "Create item" step. In other words, get the item you just created. Then, in your e-mail, all dynamic content is from the "Get item" step, NOT the "Get response details" step.

How does this solve your problem?

You'll log all of the Form data into a Sharepoint list which you'll set up with columns for each of the questions, plus additional columns for things like Submission time and Respondent e-mail. You probably want these logged somewhere to keep as records anyway, right?

Your list will also have calculated columns. You set the formulas in those to calculate whatever it is you want to calculate based on certain responses. Once you create an item on that list and enter the responses into the appropriate columns, the calculated columns on that list will fill in with the values you want to calculate, and by referencing the list item you just created, you'll have those values available as dynamic content. You'll also have a permanent record of all responses that are submitted from your form, which includes those calculated values  - it's all stored in your Sharepoint list.

Hope that helps.

Ryan

View solution in original post

7 REPLIES 7
v-yuazh-msft
Community Support
Community Support

Hi @ piercearc,

 

Could you please share more details about your Forms?

 

I have made a test on my side to create a Form with three questions as below:

Capture.PNG

 

I have created a flow as below to get the average of the numbers in the three questions:

Capture.PNG

 

Capture1.PNGCapture2.PNG

 

Note:

1.Add "Append to array variable" until add all ansewer of question to the variable

2.The expression in the "Increment variable" as below:

int(outputs('Compose'))
3.The expression in the "Compose 2" as below:
div(variables('sum'),variables('amount'))
 
The flow would run successfully as below:
Capture.PNG
Capture1.PNG

 

Please let me know if your problem could be solved.

 

 

Regards,
Alice Zhang

Anonymous
Not applicable

I'm not sure if you're still looking for an answer to this question, but I have an alternative solution that I use for one of my own Flows.

When a response is submitted >> Get response details >> Create a new item on a Sharepoint list with all of the responses from the Form >> Get item. For Item Id, use dynamic content "ID" from "Create item" step. In other words, get the item you just created. Then, in your e-mail, all dynamic content is from the "Get item" step, NOT the "Get response details" step.

How does this solve your problem?

You'll log all of the Form data into a Sharepoint list which you'll set up with columns for each of the questions, plus additional columns for things like Submission time and Respondent e-mail. You probably want these logged somewhere to keep as records anyway, right?

Your list will also have calculated columns. You set the formulas in those to calculate whatever it is you want to calculate based on certain responses. Once you create an item on that list and enter the responses into the appropriate columns, the calculated columns on that list will fill in with the values you want to calculate, and by referencing the list item you just created, you'll have those values available as dynamic content. You'll also have a permanent record of all responses that are submitted from your form, which includes those calculated values  - it's all stored in your Sharepoint list.

Hope that helps.

Ryan

Anonymous
Not applicable

Finally had time to work on this again. This solution works the way you described, but now I'm seeing a new issue with what I'm trying to do. 

 

This is what I have now. 

 Flow.PNG

 

The Form is for an Audit that I'm adding the results to excel to graph all results, and I'm emailing each Form submission to that person with a breakdown of their scores. In Sharepoint, I created columns to perform calcuations and these are what will be emailed. The issue I'm seeing now is that the Sharepoint dynamic content for these calculated numbers are being emailed like ".4645373820" in a long decimal. I'd like this to be sent in percent form like "46.5%". I can't seem to manipulate this the way I'd like in the "Send an email" step. Another issue I'm seeing is when that item is added to the excel table, the decimals are entered like "0,64" rather than "0.64" 

 

Any idea how to address this?

Anonymous
Not applicable

So, I think there are possibly two different issues.

 

With the comma instead of the decimal being entered, it's either a setting in your Excel file (I THINK it's under format cells, where you can have decimals displayed with a "." or a ","). If that's not the problem, it might be a setting in your sharepoint list.

You should look at the calculated columns' settings. You would think Calculated columns would automatically be numbers, but Sharepoint allows them to be set to different types. Make sure they are set as numbers. That could also be what is causing the comma issue, but I'm not sure. The one other thing causing it could be your region. You're not located somewhere where the comma is the norm for decimals, right? I assume you wouldn't have any complaints if that was the case.

 

As far as displaying them the way you want, Sharepoint outputs numbers to too many decimal places, even if you change the setting to display only 2 decimals. All that does is make it display 2 decimals in the Sharepoint list, but it still exports to other places with a lot of decimal places. The solution I've had to employ is to create another calculated column. Set that one as a text format column. Then you can use formulas like this:

 

=ROUND([column name]2-LEN(INT([column name])))                             Round xx column to 2 significant digits

=TEXT(ROUND([column name]2-LEN(INT([column name])))&"%")         Round xx column to 2 significant digits, add "%" on the end, and convert all that to text

 

Again, make sure this column is formatted as text.

If you are seeing what you want to see (e.g. "46.97%") in your Sharepoint list but not on the dynamic content output, you could also try the simple formula,          =TEXT([column name])

 

Hope that helps

 

-- Ryan

Anonymous
Not applicable

The calculated columns don't seem to be supporting 'round' function in Sharepoint. Even when I try to add a column with the formula:

 

=ROUND([Column Name], 3)

 

it tells me there's either a syntax error or it's not supported. 

Anonymous
Not applicable

Just realized my error is the same as the commas issue. My region is set to a country that uses commas because my company is based there and they set up the original sharepoint. Everything works for me now. 

Anonymous
Not applicable

Glad to hear it! Just for future reference, the notation I posted for the formulas is from Microsoft documentation specifically for Sharepoint, and sometimes Sharepoint functions are a little different than Excel ones. So if you want to round in the future, try it the way I wrote it with the "2-LEN" or "3-LEN" for the number of significant digits you want (and it is significant digits rather than digits, which is different).

Ryan

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!

Users online (4,368)