Answered

Update SharePoint List when new MS Form is submitted - cannot select specific SharePoint Item

(0) Share

Report

Posted on by HMJ_Somerset

118

I have a SharePoint list containing staff information and a MS Form that staff complete to indicate what skills they have and their availability to respond to redeployment requests to help in our Covid response.

I want the staff member's entry in the SharePoint list to be updated whenever they complete the MS Form.

I can create a Power Automate flow to create a new entry in the list each time a Form is submitted but not to update a list entry.

I am failing on selecting only that staff member's list entry. The only unique field in common is email address.

I have tried filtering Get Items but get the error: The expression "EmailAddress eq 'Staff@email" is not valid. Creating query failed.

I have tried using filter array. The flow runs successfully but does not return a record.

Until I return the correct record I cannot use the Update Record...

I have the same question (0)

All responses (4)

Answers (1)

tom_riha 10,179 Most Valuable Professional on at

Like (0)

Report
Re: Update SharePoint List when new MS Form is submitted - cannot select specific SharePoint Item

Hello @HMJ_Somerset ,
'Filter Query' needs the column internal name to work, based on your screenshot it seems to be 'Email_x0020_Address', not 'EmailAddress'.
And make sure you've got apostrophes around the email address itself.
Email_x0020_Address eq 'xxx@company.com'

Was this reply helpful? Yes No
HMJ_Somerset 118 on at

Like (0)

Report

Re: Update SharePoint List when new MS Form is submitted - cannot select specific SharePoint Item

Thanks for the suggestion. I have tried it but still get the error 'The query is not valid'
I used this filter but it failed with this error
The expression "Email_x0020_Address eq 'xxx@emailaddress" is not valid. Creating query failed.
When I looked at the output Email_x0020_Address eq 'xxx@emailaddress the final ' is missing.

If the filter query doesn't work, why doesn't the Filter Array return any results?

Was this reply helpful? Yes No
HMJ_Somerset 118 on at

Like (0)

Report

Re: Update SharePoint List when new MS Form is submitted - cannot select specific SharePoint Item

UPDATE:
I couldn't get the filter query to work but I did get the Filter Array to work
The problem was that the Form email address was in lower case but the SharePoint list email address was in Upper Case. I set a variable to convert the Form email address to upper case
toUpper(outputs('Get_response_details')?['body/xxxxxx']
And filtering the array on this variable returned the correct row.

I am now stuck on how to retrieve the ID of this row to enter it in the Update Item so that only that row is updated with the form data.

Was this reply helpful? Yes No
Verified answer

tom_riha 10,179 Most Valuable Professional on at

Like (1)

Report
Re: Update SharePoint List when new MS Form is submitted - cannot select specific SharePoint Item

Hello @HMJ_Somerset ,
I'd try again to make the filter in the 'Get items' to work. Output from 'Get items' will always give you dynamic content to use later in the flow, while output from 'Filter array' applied on an array of SharePoint items won't.
But if you insist of using the 'Filter array' then you'll need to manually define what property you want to get. That means adding an 'Apply to each' action on the filtered array, and inside of it reference the ID column in a similar way as on the screenshot below, but instead of 'MultipleLoT' you should use the 'ID' property identifier.
e.g. to get 'MultipleLoT' column copy/paste the string below, including the @ into the field @items('Apply_to_each_4')['MultipleLoT'] Note: make sure the 'Apply_to_each' part corresponds to the 'Apply_to_each' where you're processing the 'Body'. On the screenshot above it is 'Apply_to_each_4'.

Was this reply helpful? Yes No