Update SharePoint List record with MS Form response

[MasterBruce]

Can someone advise if it is possible to update an existing record on a SharePoint list when a user submits a response to MS Forms?

[ScottShearer]

@MasterBruce 

Yes it is but there needs to be a way to link the response from Forms to the list item. Then you would use a filter query to find the related item.

Is there a value that will be returned by Forms that will allow you to uniquely identify a specific row of data in the SharePoint list?

[MasterBruce]

@ScottShearer Thanks for your reply. I've been trying to solve this for a while now.

The SharePoint list contains all the fields that are recorded on the MS Form, plus some more that are entered later. 

These include: forename, surname, date of birth, email address and other basic demographics.

Ideally, if I could get the Flow to update once finding a match for the surname and date of birth, or the email address, that would be perfect.

Do you know of any examples that I could try and apply to my own scenario? Thanks very much for your help.

[ScottShearer]

@MasterBruce 

Probably the best way would be to use the email address.

Can you tell me:

• if the Form is anonymous?
• can external users submit the form?
• is the email address entered supposed to be for the person who is submitting the form?
• should there be just 1 and only 1 entry for each email address?

Please provide the information above and I'll provide an example.

[MasterBruce]

Hi @ScottShearer 

Thanks for this! Very helpful.

Yes, the Form is anonymous and external users submit the form. The email address is the email of the individual responding and yes, only 1 entry for each email address.

Cheers!

[ScottShearer · Accepted solution]

@MasterBruce 

See my example below.  The column name in the filter query is the column's internal name which may not be the same as the display name.

Let me know if you have additional questions.

[MasterBruce]

@ScottShearer Thank you for putting this together. I'm going to test it today and see how I get on.

Just a quick question - if a new user submits the form who is not yet on the list, will it just record the form response on the list or do I need to make an amendment for that to work?

[ScottShearer]

@MasterBruce 

If the email does not exist and you want to add it, add a condition per my example below.  Bu sure to convert the email to lower case when you add it so that it will match.

[MasterBruce]

Hi @ScottShearer - I've been trying to get this working over the past day or so. When I get to the 'Get Items' part, I select the Email Address field within Filter Query. However, I am unable to do anything further there and can't add the expression to covert to lowercase.

Also, the 'add dynamic content' link doesn't appear for me on the right side.

[MasterBruce]

Hi @ScottShearer 

So, now I am able to see the add dynamic content link and when I select 'Email address' I then am not to sure what to do with the expression.

[MasterBruce]

@ScottShearer So, after a little more work I have managed to create the flow. However, I have just tested it with a dummy form response and the flow has failed with the following error.

Do you have any ideas? Again, thanks for your help here!

[MasterBruce]

@ScottShearer 

Apologies for yet another message, but I've managed to work round the previous error. Forgot the single quotes! The flow is now running successfully but when I look at the SP List, the data isn't importing from Forms.

Again, I'd really appreciate your help here. Feels like I'm so close!

[PTDIPI]

This still does not work. I have also found many inconsistencies with mapping MS Form responses back to already existing items in sharepoint. I have even tried including a "Form ID" field within the MS form. Then in my workflow use a condition to see if the MS Form "Form ID" is equal to the Sharepoint List ID and my condition never finds a true result. It is extremely frustrating and the only way I have ever been able to get MS Form responses into a Sharepoint list is via Create item, never Update Item. 

[katrina_morris]

Hi Scott,

I've got a Microsoft form to complete a start date of a mentoring relationship and then I want the 2nd Microsoft Form to add the End date of the relationship on the same line.

I've tried following through what you've said, however, I get an error saying it's an invalid expression for the Filter query i.e. my Mentor's Email column on my list. Any ideas why this might be happening?

I've created the Title field which is unique and is a combo of the mentor's email, mentee's email (all internal emails) and start date, which ideally I would like it to pick up and then change that record, but I'm not sure how I can pick itup as I get limited options in the Dynamic content. Any suggestions?

[ScottShearer]

@katrina_morris 

When using a Filter query, you must use a columns internal name what may/may not be the same as a columns display name (the name you see.

Mentor's email is not the internal name - internal names can't have spaces.

The easy way to find the internal name is to go to the list settings and click on the column name.  The internal name will be at the end of the URL after field

Give it a try!

If you still have issues, please create a new post and mention me - I will respond.

[katrina_morris]

Thanks for your response.

I've updated the name, but it is still having a similar error on the Get_items stage...

The expression "MentorsEmail eq'katrina.morris@xxxxxx.org.uk" is not valid. Creating query failed.
clientRequestId: 23cc0a18-5b99-4878-b44b-fd6e507ec2ce
serviceRequestId: 23cc0a18-5b99-4878-b44b-fd6e507ec2ce

Any ideas what I'm doing wrong?

[katrina_morris]

I'd not closed the '. It now says it's working but it didn't update the list! Just having another look at it.

[katrina_morris]

I've sorted it, thank you 🙂

Is there any way I can filter on more than one column e.g. the combo of emails or the combo of emails and the start date? If not, what I've got is adequate.

[TunstallD]

hi Scott,

i used this flow recently and it was seemingly working - however I have recently sent form out to a number of email addresses and on return the response has auto filled all list items rather than specifically the one containing their email address. could you help?

[faisalkhawaja]

@ScottShearer  I am attempting a similar flow (update completion date based on email of form respondent), but receive the message: "Unable to process template language expressions in action 'Get_items' inputs at line '0' and column '0': 'The template language function 'toLower' expects its parameter to be a string. The provided value is of type 'Null'..

This is the expression in the Email address field: 

toLower(outputs('Get_response_details')?['body/Email_address'])