Syncing data from single selection field on MS form to a lookup field on SharePoint using PowerAutomate

[Pao_paO]

I've tried to sync data from single selection field on MS form to a lookup column on SharePoint, using PowerAutomate. but it didn't work

in the SharePoint list, I have 2 lookup columns.

  - The first column shows as a drop-down (let's call it column A)

  - The other one will automatically pop up related content when you select an item in column A

I created MS form to let users fill in content easier. For that, I have a single selection field that I designed to map with column A using PowerAutomate.

Here is my flow  

Other data works fine. Only that single selection field didn't sync to column A, The lookup column.  

an error message that I got below.

I found a similar problem here: https://powerusers.microsoft.com/t5/Building-Flows/Populating-SharePoint-Lookup-Column-from-MS-Forms...

but after I followed the steps it still didn't work. noticed that the only thing different is in the above case, they use text field on MS form, not single selection one.

Anyone faced this before? please advise. Thank you!

[David_MA]

This isn't something I have done, but this is what I would try:

• For the response from MS Forms that contains the value for your lookup in SharePoint, use the Get items action on the SharePoint list where all your lookup values are stored for Column A, and use a filter query on the value that is returned from the forms response that corresponds to its matching value in SharePoint.
• Then use the length expression to make sure at least one matching value was found.
• If at least one item is found, then you can use an Apply to each from the results of the Get items action to populate the lookup column in your SharePoint list (what you refer to as Column A) with the actual value from the lookup list.

My guess is that it is a relationship issue, and using the value from the actual list will contain that relationship as it will contain both the ID of the row in SharePoint along with the stored value.

[Pao_paO]

Hi, 

Thank you for your suggestion. I understand the 1st and 3rd bullet but have no idea how to tackle the 2nd one. Would you mind explaining more, please?

[David_MA]

No problem. So that your flow does not error if the Get items action does not return any values, you want to do a check to make sure at least one item was found with your filter query. It would look like this in your flow:

The expression below is what is used in the above condition, which will return an integer of the number of items returned in the Get items action (note, if you renamed the Get items action to something else, you will need to account for that in the expression).

length(outputs('Get_items')?['body/value'])

[Pao_paO]

I tried your solution and didn't get an error message anymore. But there is no new item added to my SharePoint. Not sure what am I still missing.

Here is my flow

[David_MA]

There were no errors in your condition, which I assume is the one checking the length to make sure the get items action found items matching your filter query. Since you didn't expand the condition, I don't know whether it found something or not, but since nothing was added to your list, my assumption would be that nothing was found. You should double check to make sure the list contains a value that matches your filter query.

The problem could be with your filter query. What are you trying to filter and find? You need to do an apples-to-apples comparison. For example, if the field in your list is an integer, then the field from MS forms needs to return an integer. In a filter query on an integer, the field containing the value would not be in quotes. Quotes are used when you are searching for string values.