cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JamesH251
New Member

Forms to Excel Flow: Multi-Answer Choice Questions to Excel Table Multiple Rows and Columns

Background

I built an excel database at work several months ago that is used to track productivity and activities of workers and tasks. It uses VBA forms and macros, which outputs data into a database that is then split into many pivot tables and displayed on a dashboard. This has worked well, but the file is currently hosted on a Sharepoint site and accessed by multiple individuals for data entry. In order for the VBA form to work, they must all "Open in Desktop App," which has created some accessibility and data entry issues when two or more individuals attempt to access the file simultaneously (i.e., not saving, not updating, requiring resubmission, etc.).

 

I am hoping to move the entire thing online so that data entry is less cumbersome, and the excel file will not need to be accessed by the various users. I'm attempting to do this with Microsoft Forms, Power Automate, and an Excel file hosted on a Sharepoint site. However, I am running into a few issues that after hours of searching the web, I'm not finding solid solutions for.

 

Problem: Multi-Answer Choice Questions

I have two questions in the Form that are multi-answer choice questions that need to output to the excel file in two separate ways--one by creating multiple rows and the other by adding a true/false variable to multiple columns (the form does not need to create the columns, just add information to existing columns).

 

Problem 1: I need the Flow to create a new Row entry in the Excel table for each option that is selected on one of the multi-answer choice questions. For example, if the questions listed Option 1, Option 2, and Option 3, and all were selected, the Flow should create three rows in the excel table, a row for each option. If two options were selected, it would create two rows, and so on. The Flow would also need to duplicate all other values from the remaining Form questions into the other columns on the table for each row. For example, if Question A was the multi-answer choice question, and there were three additional questions, the table output may look like the following for one form submission:

Question AQuestion BQuestion CQuestion D
Option 1XYZ
Option 2Z
Option 3Y

Z

 

I thought maybe breaking the options into separate questions would work, but this would really muddy up the data-entry side and the Flow, as altogether there are about 60 options that need to be included. Not only would the Form need to have 60+ questions, but the Flow would also need about the same number of Condition controls and parallel branching. Additionally, these options will change over time and the Form will need to be updated. I'd prefer not to also need to edit the Flow with each minute change to the various options.

 

Is there a way to separate out the string that Multi-Answer Choice Questions produce and automate a new row entry into Excel for each element in the string, however large it may be?

 

Problem 2: I need the Flow to output a True/False value in specific columns on the Excel table based upon the options selected from a second Multi-Answer Choice Question in the Form. With this question, there are 11 specific options that if a user selects, I would like the Flow to deposit a true/false equivalent, such as a 1 or 0, into a specific column for that option if it is selected (1) or not (0). For example:

Question DOption 1Option 2Option 3
Z101
Z101
Z101

 

Thank you for taking the time to read this and offer any thoughts or solutions.

0 REPLIES 0

Helpful resources

Announcements
Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Top Solution Authors
Top Kudoed Authors
Users online (1,976)