Creating Email Reply Flow to post item to a SharePoint List field

[chucksta]

Hello All,

I have created a FLow for Problem Management. SO what the flow currently does is sends an email to our PM team letting them know that a problem management ticket needs to be created. Tis email is sent once a Sharepoint list item is created using the form below:

It then sends an email to our Problem Management Team: with the response below:

What I have done is added to the flow in the body of the email for the PM team to reply and place a Problem management number to the email in the body as below:

I am lost on my next step as to what to do to my flow below. Once the Pm team replies and places the PRB # next to the "Problem Management ticket Number", Like in the screen shot above, I want this to be updated for the problem listed in the sharepoint list in the PRB#(Problem#) field.

I am not sure how to setup the the flow to look for this newly added number based off the body of the email from the PM teams once they reply and place the PRB number from their reply. Can anyone help me with this? it seems easy trying to set it up but it gets a bit harder as it is not working for me at all.

My Flow so far:

Any help would surely be appreciated.

[MattWeston365]

Hi @chucksta the solution which I think may help is quite simple but would benefit if Regular Expressions become available for Flow rather than doing simple string matching.

If I understand you correctly, an email is sent back with the PRB code typed into the email (feel free to correct me if I'm wrong). I have approached this by creating a Flow which is triggered when a new email arrives.

From there I parse the body of the email and look for two key pieces of text: Jira, and PRB_

I can then use Flow Expressions to find the start of the key pieces of text, and then extract a number of characters from that point. E.g. my email looks like this:

So when my email comes in, I'll first of all look for the word JIRA and then get the characters after it. Once I've done that I'll knock off the JIRA part so that I'm just left with the code:

substring(substring(triggerBody()?['Body'],indexOf(triggerBody()?['Body'],'JIRA:'),15),6,9)

What this is doing is extracting the JIRA text by finding where it is in the email body (indexOf) and then taking the next 16 characters (substring). I then do that again by taking the substring, but this time starting after the JIRA: text (I know exactly how many characters it is this time).

From my email above, this will result in EFC-16171

I then do the same for the PRB reference, however I'm assuming that the code will always have PRB in it, so that is the string I'm going to try and find with my indexOf expression. I'm then going to take the 12 characters so that it will return the entire code.

substring(triggerBody()?['Body'],indexOf(triggerBody()?['Body'],'PRB_'),12)

From my email above this results in PRB_84209483

With those two pieces of data, I can now find the list item in SharePoint and perform the necessary update.

[chucksta]

@MattWeston365  I am trying it right now. Thanks for your reply sir! But quick questions since I am looking for only emails labeled "Problem Management Issue Created" that arrive when setting up the subject filter, is it ok to use this expression i set up? I really suck at the dynamic content.

contains(triggerBody()?['Subject'], 'Problem Management Issue Created')

[MattWeston365]

Hi @chucksta the subject filter is much more simple than that to use. Just put the string you want to match into the Subject Filter box and it will match it.

[chucksta]

@MattWeston365  I really want to say thanks for your help on this!

 So what I have done is in the value fields just for purpose of showing you my varibles to check is pasted them in the fields on the screen shots.

So instead of looking for JIRA ticket number which is not always setup there is always a EV Incident# so I used that to search for in the body for my first variable, I then used the next variable to search for the PRB_ which once the reply email comes back from the Problem management team, it should be in the field next to "Problem Mangement Ticket Number:" .

So i then added the Sharepoint-Update Item, but when I try to select the PRB field to update the sharepoint list, it states the other fields with the red stars are required. If so I understnad and dont mind updating them, but when I try to select them in the dynamic content, say for ID: all i see is "importance" (like in the screenshot). 

Am I not supposed to see the fields in the dynamic content to be able to ad them to be updated. I thought it was the browser, so I closed it and started fresh but still i dont get the dynamic content to work. Am I missing a step btween the last variable and the SHarepoint-Update Item or maybe I have my steps struturedincorrectly? 

[MattWeston365]

Hi @chucksta this is one of the few things about Flow which grips me now and then, but I've got myself into a habbit now so that I don't hit this issue. I always make sure I have got the list item before I try to run an update. If my trigger is providing the list item e.g. the trigger is When an Item is Created or Updated, then I have all the details about the list item to start with. If I'm using another Trigger, then I just make sure that I use the Get Items action before my update.

Finally when I come to the update and need to provide values to the mandatory fields, I'll simply use the dynamic content from Get Items.

It's an extra step, but it's one which is needed.

[chucksta]

@MattWeston365 

it seems to have worked, however it updated all the tickets, in the sharepoint list instead of the specific ticket pertaining that is it  prb field with "PRB_12345678 substring(triggerBody()?['Body'],indexOf(triggerBody()?['Body'],'PRB_'),12)". 

SO I just found out the ProblemManagement team wont be using the underscore.

They structure the tickets like this as of now, smh!!

PRB0058 - INC190613-032

My Code for the PRB variable is: 

substring(triggerBody()?['Body'],indexOf(triggerBody()?['Body'],'PRB'),24)

the PRB for some reason when this runs writes to every line in the PRB Problem field. 

My Code for the EV Incident# is:

substring(substring(triggerBody()?['Body'],indexOf(triggerBody()?['Body'],'EV Incident#:'),15),6,9)

 

For some reason my variables seem to post the data but incorrectly and to every tickets in the sharepoint list?

Can you see what I may be doing wrong or left out?

[chucksta]

@MattWeston365  So I was able to figure out the issue with getting the information to post to the field correctly, but not sure as to why it post to every field and how I structured it wrong.

POst perfectly, but now post to every field in the SharePoint list.

I am wondering what i need to do to get it to post to the correct list item. The only differnece in the email identifier will be the EV Ticket number but not sure how to make it post to that particular list item once the reply comes through.

Its late here so hope to hear from you soon. Matt I really appreciate your help on this!!!

[MattWeston365]

Hi @chucksta we're almost there. The only thing that is missing, is that in your Get Items action you need to provide a filter to determine which item you want to update. You'll notice that your update code is now wrapped in a loop, therefore without a filter it will loop round each item in your list and change identified field.

Filters require an odata query to tell it what to look at. So for example if I'm going to find the EV Ticket Number, it may be something like this:

EVTicketNumber eq 'MW365001'

The reason say it may be, is because the field name is the internal name of the SharePoint field, and will depend on how you named it when you created it. E.g. if you create a field with spaces in the name, then it will look like this:
EV_x0020_Ticket_x0020_Number

You can find out what the internal name is by browsing to your list setting, scroll down to the columns, and then click on the column you want to find the internal name for. The internal name is displayed in the URL as the Field querystring parameter.

[chucksta]

@MattWeston365  GOod Morning Sir!

Thanks for the reply so see below for my results.

SO It ran successful but still updated every item in the table again.

When I look at my int variable for the EV Incident#: this is what i get, the value is not correct. Does this look right to you?

FYI: I forgot to mention also, usually when they create Problem Managment it is usually they just add the PRBXXXX - and then the EV Incident# equaling PRB0058 - INC190101-042, which is what is pasting to every PRB field in the table right now. 

Sample EV Incident#: INC190101-042

The code for EV Incident# Variable:

substring(substring(triggerBody()?['Body'],indexOf(triggerBody()?['Body'],'EV Incident#:'),15),6,9)

Like you say we are almost there but i think its just the variable that is messing up.

Results from test run.

Value is incorrect

Let me know what I entered incorrectly and Thanks again!

[MattWeston365]

If you are up for it, it will probably be more beneficial to do a screenshare on Microsoft Teams. If you want to do this, can you private message me a contact email and I'll send you a teams invite.

[MattWeston365 · Accepted solution]

@chucksta and I got there. There were a couple of changes we made to the above Flow.

The first thing we did, to increase the likelihood of our expression getting the data we need, we ran the email body through the HTML to Text action to strip out all of the HTML. This meant that we were then only dealing with the raw content of the email.

We then tweaked our substring expressions to ensure that we were isolating the correct data. We however had a stray character space before the text we wanted. After a little bit of looking, we had a space in front of the expression in the action. One of those little spaces that are notoriously difficults to spot but give you a huge headache.

Once there, we tidied up the Get Items so that we were pulling back the correct record, and then updating it.

It was great speaking to  you @chucksta, if there's anything else the community can do, let us know.

[chucksta]

@MattWeston365  This community is so positive and super knowledgable. Matt is a GODsend we got this knocked out in no time and this helped my process run smoothly and it is working as expected.

Again, Thank you @MattWeston365 for all your help sir!!