Microsoft Forms multiple choice, insert into SQL database as boolean or varchar

[Peter_Groen]

Hi there,

I a fairly new to Power Automate and trying to achieve the impossible, but I hope you guys can save my day.

The scenario is the following:

• We have a Forms with multiple choice answers, which contain fairly long answers. It also has the "Other answer option".
• We have a SQL database to store the Forms' data, where the multiple choice selections must be stored as boolean values with a far more simple key name like "answer_1"
• For each selected answer the boolean value should be inserted in the table as 1 (true), for each non-selected value it should be 0 (false)
• The "Other answer" string is optional. If there is an answer, insert it the column "answer_other" as nvarchar(50)

I am getting the response and with json() I can see the selected choices. The next step is "for each selected choice, insert a 1 into the corresponding table column, and insert a 0 for the non selected choices. If there is an "Other answer", insert it in the column answer_other". (P.S. I have the SQL Server add-on).

Because the individual choices don't have an identifier, I must

1. Search the returned string and convert them for matches on the SQL columns
2. Find a non-matching string and assume that is the "Other answer", otherwise assume there was no "Other answer"

I hope my problem is clear to you and I very much appreciate your time to look into this.

[Mark-Shenouda · Accepted solution]

Hi @Peter_Groen 

You can apply a solution for this , but it will include many string manipulation and array comparison 

Here is a sample approach that can be done. 

- in the below solution I had 3 options in addition to the other option (4 options) 

- you need to have 4 variable that will hold the data for each option 

- to obtain the boolean value of the options you need to use contains expression similar to the below     

                               contains(outputs('Compose'),'Option N') 

- Now you will have the boolean values for the database fields.

- For the other option you need to create an array variable that includes all options except the other option , 

- Using a foreach you can check if this option is included or not , if not then this will be the other option and you save it     to a variable 

- At the end you insert the row based on the values. 

-  Check the below diagram for further explanation

Hope this helps you in your solution 

Best Regards

Mark

[Peter_Groen]

Hi Mark,

I have implemented your solution and it works as expected, thank you very much for the clear explanation!

One thing I'm not sure about, is the "Apply to each" step near the end. I suspect you use the same json() expression as in the "Compose" step. I have changed this to "Outputs('Compose')" and it works as well.

For clarity, in the "If no" step inside the "Condition" step, Set variable "OtherOption" to "Current item".

Thanks again!

[Mark-Shenouda]

Hi @Peter_Groen 

You are right , You can use the compose output instead of the json expression , they both the same value

Also for the no branch , it is used to set the OtherOption Variable 

Most welcome 

Best Regards

Mark