Updating list item from email reply - Get emails (v3), filter, update column

[sudosaurus]

I have built the below flow where I am expecting a SharePoint list item to be updated when a user replies to a previous automated email.

When a comment is added into the Comments column in a SharePoint list I am sending an email to the requester.

Once an email is sent from the "Send an email (V2) action, it arrives into the users mailbox.

The user replies to this email and it is forwarded to another mailbox and folder.

This folder "Ticket Updates" is watched in the "Get emails (V3) action and I was expecting the "Subject Filter" to match against an email that has "RE: Ticket Updated - CXP-ID" in the subject line where "ID" is grabbed from the "ID" column in the "When an item is created or modified" action. 

From here I was expecting the body of the email to be captured and populated into the "Comments" field in the SharePoint list.

The "Updated Item" action seems to keep skipping with this error:

{"code":"ActionConditionFailed","message":"The execution of template action 'Update_item' is skipped: there are no items to repeat."}

See my flow below. In theory this should work but maybe I am getting some of the logic wrong???

[JohnAageAnderse]

Hello @sudosaurus 

Did you find a solution to your need or is the question still relevant?

You should use two flows for the scenario you describe 🙂 The first one is sending out the email. The second one triggers when an email is received in that folder.

Kind regards, John

[sudosaurus]

@JohnAageAnderse I did get it working however its now stopped again!

This is my updated workflow:

This is the output when the flow runs:

[JohnAageAnderse]

Hello @sudosaurus 

The run information shows that your For each step only loops 100 times. This means that the Get items step only reads the first 100 items.

Please check the Get items step, open Settings, turn on Pagination and set the Threshold to 5000.

This will allow the flow to work for some time, till there are more than 5000 items in the list.

To avoid that, think about moving resolved items (assuming a ticket system here) away from this list and into a Resolved tickets list. That way your list will hopefully always be way below 5000 items.

Kind regards, John

[sudosaurus]

@JohnAageAnderse this worked nicely!

I have one other query.

So when I reply to a "ticket" that has been updated in SharePoint and has come through to my email, if the Subject line is "Ticket Updated - CXP-78 - Final test email" then the comment in the body of the email goes back into my comments field in SharePoint - this proves the workflow now works. 

However I have another email that is sent when an item is resolved and the subject line only differs very slightly to "Ticket Resolved - CXP-78 - Final test email" - see I've only changed the wording from "Updated" to "Resolved" here however when hitting sent, this doesn't seem to hit the above workflow and instead hits my Email to SPO workflow instead and a new ticket is created - I cant see any logic as to why this would be happening - thoughts?

[JohnAageAnderse]

Hello @sudosaurus 

Thanks you for the reply that the change to settings worked for you.

To be able to handle any email you should include a unique ticket id in the subject and possibly also in the email body. Then you flow will just check for the ticket id pattern and extract it. A pattern could be "<ticket type>+<your unique abbreviation>+<ticket id>" where ticket type is either "INC" for incident, "REQ" for request, etc.; and your unique abbreviation could be a 3-4 letter abbreviation; and last followed by the ticket id/number.

With such a pattern you should be able to extract it from the subject and from the body, do the check and react accordingly.

Kind regards, John

[sudosaurus]

Just wondering why when a comment goes out from SharePoint and I reply to it from my email that the workflow picks up the comment and puts it back into SharePoint

However on the closed workflow it doesn't work in the same way when I reply to the email.

You will see that both Subjects are almost identical with the exception of "Updated" being replaced with "Resolved".

How would you suggest based on your last reply that I configure this for it to work?

[JohnAageAnderse]

Hello @sudosaurus 

About the comments - the problem I see is that your "ticketing system" only has one comment field, thus when you update the item, it will replace the content with the email body. What you need is to be able to store many comments, so store them in a related list instead.

About the suggestion I gave - when you send out an email, add the unique identifier to the subject like "INCXXX01234567 - <ticket title><, registered/updated/resolved>".

In your flow handling incoming emails - extract the unique identifier and use that in your Get items step, Filter query parameters, to retrieve the one ticket that the unique identifier points to.

Then create a new item in the Ticket comments list (the one I suggested above) and store the email body there.

You will now have this solution - Tickets -> Ticket comments - two lists 🙂

Hope this gives you some ideas!

Kind regards, John

[sudosaurus]

In terms of my comments field, I have "append comments" enabled on a multi-line text box, so this gets updated in the sp list item.

With your example "INCXXX01234567 - <ticket title><, registered/updated/resolved>"

Would this formatting in my environment work?

"CXP-ID - <ticket title> <Resolved>"

Is it best to put the ticket title and state of ticket in brackets? as this is not what I'm doing presently.

For the CXP-ID bit is it OK to structure with a dash or better to remove the dash and just have for example, CXP150?

Thanks.

[JohnAageAnderse]

Hello @sudosaurus 

The brackets are just to show that this information is replaced with something else, so no need to have them around your ticket title, etc 🙂

The identifier "CXP-ID", where ID is a number, then yes, that is fine, you can then use it to get the ticket in the Get items step. Is your unique ticket id the whole value "CXP-ID" or only the "ID" part?

Ok, know about the comments being appended using the version control, although I never found it useful when I had to present it later on.

Kind regards, John

[sudosaurus]

I have a flow that appends "CXP" to the ref column in SPO:

[JohnAageAnderse]

Hello @sudosaurus 

Ok, with that Ref column you should be able to get the single item with a Ref equal the subject unique identified in the Get items step.

In the Get items step, use the Ref columns internal name and in the Filter query enter:

<Ref column internal name> eq '<dynamic value here for the CXP-ID that you extracted from the subject>'

That will mean that the Get items step will only retrieve items that has a Ref column with the value of "CXP-ID", which should be one item only 🙂

Kind regards, John

[sudosaurus]

How do I include the Ref internal name (TicketID) in the Get Items step - there doesn't seem to be an option?

For the filter query, I only seem to be able to add Outlook fields - should I enter "CXP-" without the ID number as this will vary per email..

[JohnAageAnderse]

Hello @sudosaurus 

You will have to use variables and expressions to extract the CXP-ID from the subject. When you have that value in a variable, then you can use the variable in the Filter Query field of the Get items step.

Example from one of my flows:

The CustomerName eq '<variable CustomerName>' part is to understand as:

• CustomerName is the internal name of the column "Customer name" - a sharepoint list column has internal name and display name, so you have to look it up in the sharepoint list.
• eq means equal
• '<variable CustomerName>' is the Variable connectors variable I used to extract the customer name from somewher else.

Kind regards, John

[sudosaurus]

So would mine look like CXP-ID eq 'TicketID' ?

I'm sorry, I don't know how to use a variable/expression to extract the CXP-ID from the subject.

[JohnAageAnderse]

Hello @sudosaurus 

Here follows an example on how to extract your ticket number from the email subject, on the assumptions that the ticket number looks like this "CXP-123456" followed by a space (blank) in the email subject.

Note that the number "123456" can be any length, not only 6 digits.

Trigger and get index of ticket number in subject

In the above screenshot is the trigger, which only reacts if the email subject contains "CXP-".

The 2nd step then gets the index of "CXP-" in the subject, ie. at which position is "CXP-"!

Get subject length, extract from ticket number, get ticket number length

In the above steps, the lenght of the subject is gotten. Using that and the index of "CXP-" the text from "CXP-" till the end of the subject is extracted.

Last step then gets the index (position) of the blank after the ticket number, which then is the same as the length of the ticket number.

Extract the ticket number

The above step then extracts the ticket number.

Observe that each step has a comment which show the actual expression used.

Hope the above helps you towards a solution

Kind regards, John

[Shelly-S]

@JohnAageAnderse, this is SUPER helpful to me, too! Thank you for sharing. 🙂 

[sudosaurus]

Hi @JohnAageAnderse 

I am getting this error:

Flow save failed with code 'InvalidVariableOperation' and message 'The inputs of workflow run action 'Extract_text_in_subject_starting_from_CXP-' of type 'InitializeVariable' are not valid. The variable 'StartIdx' must be initialized before it can be used inside action 'Extract_text_in_subject_starting_from_CXP-'.'.

I didn't see a step above for creating the StartIdx variable - did you miss this?

[JohnAageAnderse]

Hello @sudosaurus 

Yes, I looked at my post and it seems like I missed adding that snippet. I no longer have the example flow.

I think though that you can replace the StartIdx variable usage with the TextIdx variable.

Kind regards, John 

[sudosaurus]

@JohnAageAnderse  and @Shelly-S 

I'm still having problems with this. I have posted screenshots, in this new thread:
https://powerusers.microsoft.com/t5/Building-Flows/Extract-subject-line-from-email-and-update-ShareP...