Update SharePoint List Item based upon Email Conversation ID

[sn12C]

I have a flow that uses data from a Sharepoint List to send out an email from a shared mailbox when there is a change in the Status column of the list.  When the recipient of the email responds, I simply want to update that specific Sharepoint list item. 

So I've built a second flow for incoming emails that uses the Condition of checking the Outlook Conversation ID against the "Conversation ID" captured in the original Sharepoint list item to determine whether or not to update.  Unfortunately when it finds the condition to be true it does an Update Item to all items in the list.

I am pretty sure it is because I am using "Get Items" to check for the matching Conversation ID instead of Get Item (which is not possible initially) and so the system is then doing an "Apply to Each" across all items.  Attached is a screenshot of my flow set up. 

I have also tried numerous variations including initializing and setting variables, filter arrays, and more.  I also have had a sneaking suspicion a few times that the email sent out from the flow, may have a different Conversation ID, but thus far - when I include the ConvID in the email body, I am seeing it is the same as what comes back in with the email reply.  I'd be very keen and grateful to get some guidance on the best method to achieve this.  

[Caspar_Rubin]

Hey

I suggest you simply use a filter query the Get items action to only get the items back you want. Then you can use the Dynamic content ID from that action to update the item. You don't need the "Get item" action.

Basically like this:

Filter Query in the Get items action should look something like this:

ConversionId eq '@{triggerOutputs()?['body/conversationId']}'

You have to use the internal name of the SharePoint column... so if your SharePoint column is called "Conversation Id", it might be "ConversionationId" or "Conversation%20Id" or something like that... Here's a guide on how to find the internal name of your column: https://sharepointstuff.com/2022/02/09/find-internal-column-name/

Cheers

[sn12C]

Thanks for the response - I have tried this in the past without much success.  Just tried it again and continue to get "The expression "......" is not valid.  Creating query failed.  Attached is a screen cap.  

[grantjenkins]

If you had a space in your column then you would use _x0020_ instead of %20.

Conversation_x0020_ID eq '@{triggerOutputs()?['body/conversationId']}'

[sn12C]

Thanks for the reply but I still get the same error.  Meanwhile I have handled all this with a filter array and think I am making some progress - only remaining problem is if I use the Item ID from Get Items - it goes and updates every item in the SharePoint list, rather than the actual Item I want to update.  Not sure if this is a consequence of my Filter or the fact I am choosing the ID from "Get Items".  Attached are some screen captures

[grantjenkins]

The reason it's not working correctly is that you filter the results using the Filter array, but then just use the output from the Get items action which includes all items. You would need to use the output from the Filter array in your Apply to each.

This is how I would build your flow. I think you just need to confirm what the internal name is for your Conversation ID column and the Submission Status choice column.

See full flow below. I'll go into each of the actions.

I've used When a new email arrives, but would be the same for When a new email arrives in a shared mailbox.

Get items has two conditions. One for the Conversation ID and one for the Submission Status which you had within your Apply to each before. This should return only the item you need to update.

ConversationID = '@{triggerOutputs()?['body/conversationId']}' and SubmissionStatus ne 'NEW'

Apply to each will iterate over the items returned from Get items. In our case it should only be one item, so the Apply to each will only run once.

Update item would use the ID from Get items and whatever other fields you need, including setting your Submission Status to UPDATE.

To check the internal names of your columns, go into the List settings, then click on your column. It will show the internal name at the end of the URL as shown below.

[sn12C]

Thank you very much - I do think this makes sense and does get me closer to the solution but the query itself is still failing to function (see attached).  Even when I simply run the query against only one field (say ConversationID) it fails.  One the matter of my "Submission Status" column, that is a choice column which may be the reason that needs a filter array vs a query.  I have simplified its name to merely "Status" to avoid any naming convention issues which may have been related to a two word column name  

[sn12C]

After further research, I am seeing that because my ConversationID field is a Multi-line text field and because my Status field is a choice field, the Filter Query in "Get Items" is expected to fail.  It seems the best route is using Filter Array to ensure I get the appropriate item, but I welcome any recommendations anyone may have.

[sn12C]

Still in need of some assistance here - in the event anyone has any guidance.  Thanks.

[grantjenkins]

The Status Choice field would work as expected in the Filter Query of Get items. However, you would then need to use a Filter array to further filter on the ConversationID since it's a multi line of text column.

See full flow below. I'll go into each of the actions.

Get items only contains the filter on the Submission Status column. Just confirming this is the condition you want - won't bring any items back that are set to NEW.

SubmissionStatus ne 'NEW'

Filter array check to see if the ConversationID column contains the Conversation Id from the email.

Apply to each iterates over each of the items returned from the Filter array.

Update item uses the following expression to get the ID of the current item we are iterating over.

item()['ID']

[sn997]

Hello,

Sorry for going MIA on this, but got pulled into other initiatives and this fell to the back burner.  I have implemented the above solution yet it still fails - below is the error message I get.  Is it potentially due to the ConversationID column being a multi-line column? 

Error Message:
"The field 'ConversationID' of type 'Note' cannot be used in the query filter expression."