Add Rows to table based on response of Microsoft forms

[Rajpatelb86057]

Dear Friends,

I am beginner to design of power automate flow and this is my first flow that I have prepared by putting too much hard work. I am in need of your help into implement large power automate flow from already designed small test flow that running successfully.  I am updating excel table based on response from Microsoft forms. 

QC sheet that i am updating based on forms response.

i am using Microsoft forms to update to QC sheet. Initial value for Drug, Event, Narrative, Patient, Report, DCandRC and Labelling are same for all that is 1. if user select Drug from drop down of Error1 then value should be converted to 0 for Drug and for rest it should remain 1 as per first row shown in QC sheet.

 if user select DCandRC  and Report from questions of forms and then value should be converted to 0 for both DCandRC  and Report and for rest 05 value should remain 1 as per last row shown in QC sheet.

i have successfully implemented this for 07 QC parameters as described below in explanation of my test flow. i have total 188 QC parameters in my real case.

first have set initialize variables for 07 QC parameter (1 for all)

then i have use switch action for question named Error1 that set variables to 0 based on answer of Error1.

then I have use switch action again for Error2 and inside switch action used composed action to prevent variable (already set at 0 in Error1) to reset again at 1 in Error2 then set variable based on answer of Error2. same switch for further Question like Error3 and Error4.

And then finally add a row into table.

This small-scale flow running successfully. I need your support to implement this flow in my real operation that have 188 parameters like Drug, Event, Narrative, Patient, Report, DCandRC, Labelling and so on and 10 question like Error1, Error2 to Error10.

it would be great help for me if someone guide me best approach in this regard as setting 188 initialize variables for 188 QC parameters and 188 cases under switch action are not possible.

Thank you in advance. 😊

@creativeopinion   @grantjenkins     @ManishSolanki   @Pstork1  @trice602    please help.

[Rajpatelb86057]

Hi, can anyone please help me to manage 188 variables?

[Pstork1]

If you absolutely have to you can get 188 cases under a switch. Use a switch for the first 25 cases and then add another switch in default for cases 26-50. Another switch in the default of that switch will get you 51-75, etc.  But that wouldn't be a very good way to do it.  Instead I would suggest seeing if you can get the values to produce an index 0 to 187. I haven't looked to see if the index is available from the dropdown in Forms or not. If it isn't you may have to build a JSON array with all the potential values and their associated Index number. Then use a filter Array to match the value returned from the form to the one in the Array. Once you have the Index you can use the Index to set the value in another 188 member array to 0. Then use that array to populate the row in Excel. If that process isn't clear from the description let me know and I'll put together a small flow proof of concept.

[Rajpatelb86057]

Thank you for your response!! @Pstork1  

i will not be able to build flow from your description as i am new to power automate. kindly put small flow with JASON array. i have simply 188 drop downs in questions of Microsoft forms. first i want to initialize variables for 188 QC parameters as 1 then based on answer set it to 0. once any variables set at 0 then it should not again set to 1 from answer of subsequent question of forms. And then add row to table in excel. thank you in advance.

[Pstork1 · Accepted solution]

Here's a short POC of the method I used to get the array of variables where all are set to 0 except the one that matches the answer.

1) Use the Form Submitted Trigger

2) Create an array variable with all the values and their Indexes.

3) Get the response details.

4) Use a Data Select to generate an array of 1's and 0's. If the value property in the array is equal to the response use a 0 otherwise use a 1.

5) The resulting array is a list of 7 values where the value that matches is 0

This was the result when I picked Patient, which is the 4th response. 

[

  1,

  1,

  1,

  0,

  1,

  1,

  1

]

[Rajpatelb86057]

Dear @Pstork1 ,

Thank you for reply.

i am facing difficulties in select action as shown in below screenshot.

 also kindly guide me how to set resulting array in add a row into table action.

Thank you😊

[Pstork1]

Can you show me the array you setup as a variable?  In my screenshot you can see that it has a property for each entry called Value.  The other side of the equals is the dynamic content from the response.  YOu need to pick that from the list.  It looks like you are using the one from my form.

For the entries you'll use the index array of the values you are creating.  So each value in the list will be set to something like this.

body('Select')[3]

The number inside the bracket is the 0 based index of the value in the array. 

[Rajpatelb86057]

please see below setup of variables and Select action.

@Pstork1 

[Pstork1]

Instead of outputs('Get_Response_Details')?body('Get_Response_Details') you need to pick the answer to the question where they are picking the value from the dropdown in the form.

You should also use the Icon to the right of the mapping to switch the data select to just map values and not keys, since you want to leave the key blank.

[Rajpatelb86057]

i have selected dynamic value of questions Error1, Error2 and Error3 in select action but still invalid. please see my forms that have questions Error1, Error2 and Error3 with same dropdown.

@Pstork1 

[Pstork1]

You need to do one question for each row.  Error1 answer will be one entry, Error2 will be another and Error3 will be the third. You are trying to do all of them in one row.

[Rajpatelb86057]

Hi @Pstork1 ,

i will have first 5 questions as shown in below forms screenshot that should go as it is in excel. then rest 10 question like Error1, Error2 to Error10 with same dropdowns of 188 QC parameters like Drug, Event and so on. if user select Reporter in Error1 and DCandRC in Error3 then value should be 0 for both Reporter and DCandRC and for rest 186 should be 1 (Second Last row of excel with case ID BR456). if user don't attend Error1, Error2 and Error3 then value should be 1 for all 188 QC Parameters (Last row of excel with case ID Ind123).  is this type of set up possible with power automate flow?

Thank you in advance.😊

[Pstork1]

It should be possible using the procedure I showed you above. But the formula in the data select field will be much more complex since you have to set 0,s for multiple conditions.  Where you are using the equals() function today you'll need to add up to 9 more equals() clauses and put them all inside an or().

[Rajpatelb86057]

Dear @Pstork1 ,

thank you for your response.

it will be ok for me. can you please guide me step by step with action with dynamic contents plz? it would be great help for me.

[Pstork1]

I've already shown you all the techniques you need for this. But I think the complexity is beyond what you will be able to do at this point given your knowledge and experience.

[Rajpatelb86057]

Dear @Pstork1 ,

i tried hard with techniques you suggested but unfortunately it count not able to make it. But i am able to get data in Excel A from Froms. is it possible to transfer that data to Excel B as shown in screenshot?

 Thank you for your time.😊

[Rajpatelb86057 · Accepted solution]

Hi @Pstork1  thank you for your time and suggesting techniques. i manage to create var Array and selection action that gives desired results.