cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Converting Forms data to SharePoint List

My company is looking to collect data from prospective candidates (we are a staffing company) in particular states to determine if they would be able to work at certain locations in particular states.

 

The Form is straightforward (see list of questions below).  However, the main issue is that Forms lumps all multiple selection question values into one cell when downloading to Excel, so the data isn't easily usable for reporting.

 

My goal is to take the Forms response data and translate to row-level data.  Essentially, if someone would select 3 NY locations below, ultimately the final output would have three line items for John Smith & the basic info, and then on each line would have the separate value under that particular column.  I'm assuming we would need to utilize an Array function & set the variables, but I'm not entirely sure where to begin.


Below is the survey as it stands today.  I'm happy to help provide any additional info to arrive at a solution.

 

  1. Full Name (free text)
  2. Email (free text)
  3. Phone Number (Free text)
  4. Recruiter's Name (drop-down selection; max one selection)
  5. Select a state (multiple choice; max one selection
    1. Connecticut
    2. Delaware
    3. Maryland
    4. Massachusetts
    5. New Hampshire
    6. New Jersey
    7. New York
    8. Pennsylvania
    9. Virginia
  6. Which locations in Connecticut (multiple choice; multiple selections)?
    1. Address #1
    2. Address #2
    3. Address #3
    4. Address #4
  7. Which Locations in Delaware (multiple choice; multiple selections)?
    1. Address #1
  8. Which locations in Maryland (multiple choice; multiple selections)?
    1. Address #1
    2. Address #2
  9. Which locations in Massachusetts (multiple choice; multiple selections)?
    1. #1
    2. #2
    3. #3
    4. #4
    5. #5
    6. #6
    7. #7
    8. #8
    9. #9
  10. Which locations in New Hampshire (multiple choice; multiple selections)?
    1. #1
  11. Which locations in New Jersey (multiple choice; multiple selections)?
    1. 33 addresses/options total
  12. Which locations in New York (multiple choice; multiple selections)?
    1. 50+ addresses options total
  13. Which locations in Pennsylvania (multiple choice; multiple selections)?
    1. 20+ addresses/options total
  14. Which locations in Virigina (multiple choice; multiple selections)?
    1. #1
    2. #2
  15. Which locations in Washington DC (multiple choice; multiple selections)?
    1. #1
    2. #2
3 REPLIES 3
Highlighted
Super User II
Super User II

Re: Converting Forms data to SharePoint List

@bposa217 

Hi there.  Interesting use-case.  Will questions 6-15 show up no matter how question 5 is answered?  It seems like you may not even need that question and can just cycle through the addresses selected below that, no?

 

I'll start mocking up a similar form to see how the data comes over, but I think you're right..we'll break up the array and essentially work through each address in a Apply to Each loop, probably.

 

Keep us posted.

-Ed

 

 

If you liked this reply, please give it a thumbs up! If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

Highlighted
Regular Visitor

Re: Converting Forms data to SharePoint List

Hi--good question.  No, we have it mapped in Forms to only populate the appropriate question in 6-15 based upon the state selected.

Highlighted
Super User II
Super User II

Re: Converting Forms data to SharePoint List

@bposa217 

Ok, I think I've got this sorted:

Each of the multiple-choice answers come back as an array, right?  So if we add a compose after the Get form details, and then use union() to basically combine all of the collections/arrays (the answers to each of the "which addresses" questions, then you should end up with a single array as a result of that compose.

 

From there, you can use an apply to each on the output of the compose, and add a step inside to add the respective row to your SP list.  Below is a pic of what I did...note that I wrapped the forms response in an array() function, as well.  This is because (for whatever reason) empty responses were converted to strings and union() only likes arrays.

 

20200605a.PNG

 

I'm happy to explain any of this in more detail, just didn't have a baseline for your comfort level on this stuff.

 

Keep us posted.

-Ed

 

If you liked this reply, please give it a thumbs up! If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Upcoming Events

Experience what’s next for Power Automate

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Users online (7,462)