cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate I
Advocate I

Forms - save each answer in a new database row

I would like to save answers from Forms in a database in the most dynamic way possible. If my users add a new question in a form, I do not want to adjust the Flow for the new question in the old form. This way I can save multiple forms of different length to the same database. This will make is much easier to manipulate in Power BI.

 

Answers Database Columns:

  1. ID (unique ID of the submission)
  2. Question ID
  3. Answer

One form submission will have as many rows as there are questions in the form. I will probably have 2 other tables to save question text and individual response submitter names and data. TBD

 

Is this possible with flow? The "get response details" action has all the answer in the 'body' part of the json, but I can't manipulate the 'response' data from that action like I can with a normal webhook. I was thinking a loop to go through all the 'body' part of the JSON. Any other ideas?

4 REPLIES 4
Highlighted
Super User III
Super User III

Re: Forms - save each answer in a new database row

Hi @grovercorner2 , I don't think what you want to do can be achieved. Forms questions can't live outside Forms and then dynamically populate the form, so if your user adds a new question you will need to adjust the Flow.

Rob
Los Gallardos
If I've answered your question or solved your problem, please mark this question as answered. This helps others who have the same question find a solution quickly via the forum search. If you liked my response, please consider giving it a thumbs up. Thanks.

Highlighted
Advocate I
Advocate I

Re: Forms - save each answer in a new database row

@RobElliott , no, I would still use Forms to create and edit the forms. This question is related to how the data coming out of Forms is saved in a database. I updated the initial post as my wording was not clear, sorry about that.


I have done something similar with Survey Monkey, using the webhook action and the API to get the raw answers, parsing the json. I haven’t figured out how to do something similar with Forms as the available action are quite restrictive.

Highlighted
Dual Super User III
Dual Super User III

Re: Forms - save each answer in a new database row

Hi @grovercorner2 

 

Please see the flow screenshot below:F3.png

 

Here, I am getting the response of the flow and extracting the attributes and the responses into an array and then running an apply to each on that array to extract individual responses and insert a new row in SQL for each of those. 

 

Expressions:

Compose 3:

split(replace(replace(string(body('Get_response_details')),'{',''),'}',''),',')

Append to array variable:

concat(first(split(items('Apply_to_each_2'),':')),substring(string(items('Apply_to_each_2')),indexOf(string(items('Apply_to_each_2')),':'),sub(length(string(items('Apply_to_each_2'))),indexOf(string(items('Apply_to_each_2')),':'))))

 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Advocate I
Advocate I

Re: Forms - save each answer in a new database row

Thank you, this works. I didn't know I could still access the raw body of an action if it wasn't available in the dynamic. 

 

The last steps:

1. What is the formula to save grab each item of the array in the last 'Apply to each 3'

2. The question from Forms doesn't show up as text, but as some kind of key. Is there a way I can lookup each key.

 

For example: 

"r0ed3d62cbb12319b9c4a62b46ff486ad":"AnswerText"

 

How do I lookup the question from forms? That information doesn't seem to come back in the "Get Response details", but Flow somehow magically knows what each question is. 

 

Helpful resources

Announcements
firstImage

Super User Program Update

Three Super User rank tiers have been launched!

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

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!

Top Kudoed Authors
Users online (7,066)