How do I get responses from more than one Microsoft form to show in a single row on a Sharepoint list?

[Rachel_1992]

My organisation has a lengthy file opening form for new clients.

We have split the questions into 3 Microsoft Forms:

• 1 for admin staff to complete;

• 1 for senior staff to complete; and

• 1 for clients to complete.

I have created a Sharepoint list so that senior management can view responses from all 3 forms in a single location. I have created separate flows using Microsoft Power Automate so that when a new response for each form is submitted it triggers the flow and sends the data to the Sharepoint list. However, the data for forms 1, 2 and 3 are displayed on different rows in Sharepoint list. The idea was that admin staff populate the first set of columns, senior staff populate the middle columns and clients populate the last columns as the data collected across the 3 forms will relate to the same file. We want a new row for each new file (which will be responses from the 3 forms) rather than for each form. I understand there is a way to update an existing data set by linking the forms together using a Form ID but would like step-by-step instructions on how to do this please.

Below are screenshots of my test flows, you can see the second flow is populating the correct columns, but the wrong row.

[v-xiaochen-msft]

Hi @Rachel_1992 ,

'Create item' action is designed to create a new item.

If you want to update the same item , you should use update item action.

Best Regards,

Wearsky

[Rachel_1992]

Hi,

Thank you for your reply.

The above will work in circumstances where I always want my flow to update an existing item, however that's not the case. For every new client Forms 1, 2 and 3 need to be completed and it should run the "update item" flow after the first form has been submitted (while the remaining 2 are outstanding). 

When repeating the process for a new client I want it to create a new entry (row) on the SharePoint list. 

Thanks

Rachel

[ScottShearer]

@Rachel_1992 

In order to do what you want, there needs to be a way to link the 3 Forms responses to a specific row of data in SharePoint.  Without that, you will not get this working.  Is there a question in each Form where the user enters a unique ID of some type that can be used to find the specific SharePoint row?

[Rachel_1992]

@ScottShearer 

I've not created all the columns for Sharepoint list yet or split questions into 3 separate forms yet. The forms are lengthy and I wanted to ensure it would work with test forms/flows before I do that. Each file has a unique file reference. I could use that as the Form ID and add a question at the start of forms 1, 2 and 3 asking for the File Reference to be entered. I'm just not sure how to create the flow, and if it will require an expression to pick up the unique identified so it knows which entry to update.

Many thanks for you assistance.

Rachel

[ScottShearer]

@Rachel_1992 

If you have something unique such as your file reference, then the SharePoint list should have a column where that info is stored.  I suggest configuring the column so that each entry must be unique

Here is how I would approach your issue:

1. Use a When a new form is submitted trigger followed by a Get response details action
   1. Th Get response details action should not be in a loop - I suspect that you may have used an old template...
2. Add a SharePoint Get Items action 
   1. Use a filter query so that only rows where the file reference entered on the Form matchs the SharePoint file reference column
   2. There should only be either 1 or 0 rows
3. If no rows are returned create a new row
4. If 1 row is returned, update that row with data from the new From

Give it a try and let me know if you need additional help.

The issue that I see with this solution is that you are relying on people to accurately enter the file reference.

[Rachel_1992]

Hi @ScottShearer 

Many thanks for the above. I will create the forms and use the steps above to try & create the flow for each. I also have concerns that this relied on the file reference being accurately entered. We will just have to stress importance to respondents that the file reference is copied and pasted exactly as shown in first form, do you know if it will be case sensitive? Can you think of a solution? Auto-fill or something more straightforward that a file reference to reduce risk of error?

Thanks,

Rachel

[ScottShearer]

@Rachel_1992 

You're using Forms, so I can't think of a way to validate the File reference.

I would go about this differently. 

Option 1:

• Have your users enter that data directly in SharePoint

Option 2:

• Create a Power App
  • You can configure the app such that only certain users can create new entries
  • Use Groups and make it so that each group can edit only certain columns
  • This would allow for the file ref to only be entered once
  • When an item is create, you can send an email notification to the second group 
  • When the second group completes their entry, and email can be sent to the third group
  • Make it so that when the app is opened, users only see items that apply to them.

[Rachel_1992]

Hi @ScottShearer 

Many thanks for those suggestions. 

For option 1 - would the user be able to see other entries in the Sharepoint list? One of the forms is to be send to clients (so users external to the organisation). The Sharepoint list will contain sensitive client data, so I cannot provide external users with access to the list unless I can restrict data visibility to only the entry they are updating. 

Additionally I need a solution that is very user-friendly for those that are not computer literate (hence we thought Forms would be the best solution here).

Option 2: This seems like it would meet our needs more efficiently. Do you have a screenshot/video you could share of the user-interface for entering data? Is this also web-based like Forms so our clients wouldn't need a O365 login/app?

Thank you,

Rachel

[ScottShearer]

@Rachel_1992 

The fact that you have external users probably eliminates option 2 due to licensing issues.

Try your original plan and hope that users enter the file reference accurately.

You might try creating a Flow that runs periodically and looks for items in the SharePoint list that may have a bad file reference. 

[Rachel_1992]

@ScottShearer 

Thank you very much. I am going to create the list and forms for my original plan, and I will also have a go at the Power App. 

In an ideal world we'd want external users to complete Form 3, but given that this relies on our clients entering the File Reference correctly, I can foresee some difficulty. It is not necessary that external users complete Form 3 (it was to save time), and our Admin staff could easily enter the data on behalf of clients.

I will put both options to management (one I have created the flows & hopefully the Power App) and see whch solution they prefer.

Thanks,

Rachel