FORMS info mitigation to lists with multiple choice answers

[skalinna3]

Hello,

I managed to get information from FORMS to LISTS via Power Automate (from now on PA). 

In our FORM, we have multiple selections activated to gather the information which of the following xyz departments are affected. I want to have a list that is presenting the following, just the way it would be presented if you generate a list entry manually in LISTS.

The bubble selection, the result of a manual added entry to the lists, is clearly highlighting the department and seperating the entries, whereas the automated transaction is gathering it as this ["HR","TQM","BPM"].

How do you get the multi selection field answers presented as if you would enter the data manually?

The overall goal is to get the seperate answers (in this case for example HR or TQM) analyzed later on by Power BI for our management to see how many Change Requests there were in a given year, what type of we had so far, how long they were processed, who had the mosts, etc. you name it. But I suppose that this will only be possible if they are seperate and not conglomerated to ["HR","TQM","BPM"].

Long story short, I need help...

[MarconettiMarco]

Hello @skalinna3 ,

you should create an array of items, and then put the variable in the SharePoint choice column.

If I have answered your question, please mark my post as Solved.
If you like my response, please give it a Thumbs Up.

My blog site about Power Automate: https://powerautomatejoy.com/

BR,

Marco

[skalinna3]

Hello Marco,

I have seen a solution with array of items, tried to go through the steps in power automate and failed at the step to create the array.

Can you help me with that?

As I understood, you are telling the software that e.g. choice one (HR) equals to "HR" and will then be presented as such in Lists, right? And for all other departmens in an equal manner, choice two = "TQM", ... 

If I follow this route, will the values be presented as required as a color coded buble?

[MarconettiMarco]

Hello @skalinna3 ,

please share the detailed flow in Edit Mode and where you're getting the error.

BR,

Marco

[MarconettiMarco]

Hello @skalinna3 ,

you can follow the instruction in the following article until the action "Set the “varResponsesDef” variable by adding the “varResponsesTemp” variable value.":

https://powerautomatejoy.com/2022/08/01/how-to-get-multiple-choice-responses-from-forms-and-put-them...

Then:

1. Add a "Compose" action in which put the following expression:

split(variables('varResponseDef'),',')

2. Initialize an "Array" variable:

3. Add an "Apply to each" control, where put the previous "Compose" outputs as "Select an output from previous steps". Add the "Append to array variable" putting:

{
"value": "@{items('Apply_to_each_2')}"
}

4. Outside the Apply to each loop, update your item putting the array variable in the multiple choice column:

If I have answered your question, please mark my post as Solved.
If you like my response, please give it a Thumbs Up.

My blog site about Power Automate: https://powerautomatejoy.com/

BR,

Marco

[skalinna3]

Hello Marco,

I followed your instructions of the provided link until step varResponsesDef and started from there on with the guidance you provided here (thank you very very much btw.).

A problem occured, renaming the variable "varArray" again, a second time, after using it the first time a few steps before.

So I tried to use as a title for the action Initialize variable-varArray 2 (marked red) but stayed with varArray (marked green)

Going further, instead of updating something, based on the trigger "a new element was created", I chose "create a new element" added everything in as before, used the "varArray" condition but was told that I cannot use "varArray" 2 times. So which one needs to be deleted, the one at the beginning or the one before "apply to each"?

[MarconettiMarco]

Hello @skalinna3 ,

it should be the first one.

Anyhow, I've updated the article:

https://powerautomatejoy.com/2022/08/01/how-to-get-multiple-choice-responses-from-forms-and-put-them...

If I have answered your question, please mark my post as Solved.
If you like my response, please give it a Thumbs Up.

My blog site about Power Automate: https://powerautomatejoy.com/

BR,

Marco

[skalinna3]

Hello Marco,

sorry, I was distracted by other daily topics. I used your guide and am currently running into an issue at the following step:

• "Compose-JSON":

here I am trying to use your previously shared expression 

{
"value": "@{items('Apply_to_each_2')}"
}

I used the same names in the apply to each parts, so references should be correct, however I got a note stating: 

This translates to "The expression is not valid."

I am not sure why this is the case. If I used "dynamic content" I am able to select the "apply to each" value, if I only do enter your code manually to that level ("@{items("...")}") , that I try to add the dynamic content into the "..." spacer, it is no longer there.

I added the text now manually, although I am not quite sure if this works or not.

• Additionally ("Apply to each 2"):

When I tried the next step, I could not choose "varArray" as shown in your example on your website, it is only showing "varResponsesTemp" and "varResponsesDef". I checked your guidance and recognized that only "strings (Zeichenfolge in German)" are to be selected here, whereas arrays aren't.

So I changed the setting of "Initialize variable-varArray" from array to string and was able to select it here. Was the screenshot just outdated?

• "Scope":

It wasn't particularly mentioned in your text, I just noticed the added function. So I changed the setup to match with yours by adding "Bereich" or "scope" to the flow, it eliminates the error message I receive for flow testing. 

"Create item":

I could not select the varArray as dynamic content. So I went back to "Initialize variable-varArray", chose array instead of string, was able to add it in here and left the selection in "Append to array variable-varArray", it was at least not dismissed automatically.

Now, trying to save it, I get the following notice:

I expected that the JSON part was causing trouble, but I am also stuck with all above.

Please help me figure it out.

[MarconettiMarco]

Hello @skalinna3 ,

please follow the steps in this article:

https://powerautomatejoy.com/2022/08/01/how-to-get-multiple-choice-responses-from-forms-and-put-them...

Please let me know if the issue is fixed.

If I have answered your question, please mark my post as Solved.

If you like my response, please give it a Thumbs Up.

I may not have all the answers, but I'm curious and passionate enough to keep looking for answers and to keep sharing them.

My blog site about Power Automate: https://powerautomatejoy.com/

BR,

Marco

[skalinna3]

Hi Marco,

I did follow your guide as far as I could. One question is popping up my mind, in a questionnaire, with e.g. 3 answers, where does this logic get that it needs to pull the multiple choice answers from question 3?

This is how I set it up at the moment:

Wih "create array" = 

createArray('[',']','"')

replace(...) = 

replace(variables('varResponsesDef'), item(), '')

This is 100% according to your guide, where it becomes tricky is:

The JSON text, can you paste it here for me to copy and paste it in?

It seems like there are failures: 1. ""The template validation failed: 'The inputs of template action 'Compose-JSON' at line '1 and column '3321' is invalid." and 2. Action 'Auf_alle_anwenden' must be a parent 'foreach' scope of action 'Compose-JSON' to be referenced by 'repeatItems' or 'items' functions.'."

And I don't know why, but I cannot select the varArray in the last step before create an item, see here:

It is only open for selection if I change (at the beginning) for "initialize variable-varArray" from array to string but then it is no longer selectable for create an item:

I hope you can help me out, I don't get were it all falls down.

[MarconettiMarco]

Hello @skalinna3 ,

have you added the Compose action after the "Set variable-Updated varResponsesDef" action?

BR,

Marco

[skalinna3]

Hey,

I got one step closer but still face a problem.

the overall structure is now:

There was one failure, I did not notice directly, your scope-try part is containing all the steps, from "apply to each" "compose" and "apply to each 2", so I was able to select "varArray" later on at the step "create item". 

I still, however, receive the following message when I try to save it:

"Code: InvalidTemplate, Message: "The template validation failed: 'The inputs of template action 'Compose-JSON' at line '1 and column '4055' is invalid. Action 'Auf_alle_anwenden' must be a parent 'foreach' scope of action 'Compose-JSON' to be referenced by 'repeatItems' or 'items' functions.'."."

May it be possible, that you try to contact me or I am contacting you to share the screen an trouble shoot before we are going back and forth?

BTW: You, Sir, are patient... thank you for all the help so far!

[skalinna3 · Accepted solution]

Thank you Marco, for all your help, with your troubleshooting effort, we managed to get it done!

And for all of you, who followed the discussions and want to know what caused the problem and how to fix it:

1. the JSON part should be linked to "apply to each 2", not "apply to each", which was not directly obvious

2. Check your connections, if they are not corrupted and need a "repair"

3. if there are multiple questions with multiple choice answers, it is getting immediately rediciously complex, just as a note!

Again, Thanks Marco! Problem solved!