Extracting Data from Emails but need help

[chucksta]

I currently have a Flow setup that when a new SharePoint item is setup via a power automate form it updates the SharePoint list. 

I also was trying to setup another flow that when the group replies to the email, with a specific ticket number, it would update the SharePoint list field for that corresponding number. Below is my flow:

 

What I am currently trying to do is when they reply back with the ticket number to the email, 

is take that number and update it back to a JIRA field I have in an sharepoint list and update it. I had it working with another list but cant seem to use the same functions to capture the data.

email text:

Title of Incident: Pending address updates overwriting phone consent flags 

Easy Vista Parent#: INC190726-054

Easy Vista Sub-Incidents: 

Priority: (1) High 

Full Issue Description: BLAH, BLAH, BLAH, BLAH, BLAH, BLAH, BLAH, BLAH, BLAH, BLAH, BLAH, BLAH, BLAH, BLAH, BLAH, BLAH, BLAH, BLAH, BLAH, BLAH, BLAH, BLAH, BLAH, BLAH, 

TEST JIRA#:  

External Ticket/JIRA: EXTR-3123

Contact Type: Support Ticket 

External Vendor Name: Shaw 

Vendor Contact: Marcus E. 

The replier replies back with this info to the email:

TEST JIRA#: SHAW-12345678

I wanted it to update the field labeled "TEST JIRA#: " in my SharePoint list for that particular issue ID. 

Can seem to get it to work and I am not the greatest at Expression writing. Any assistance would surely help.

Thanks in advance

[abm · Accepted solution]

Hi @chucksta 

To extract the TEST JIRA value 'SHAW-12345678' you need the following expression

trim(split('TEST JIRA#: SHAW-12345678', ':')[1])

Once you have the ID then  Get Items by filter the list using the above value. Then finally use the Update List Item using the ID from the Filter.

Thanks

Thanks

[efialttes]

Hi

"The replier replies back with this info to the email:"

TEST JIRA#: SHAW-12345678

Is this the whole info on the replay email body?

If so, I would suggest the following:

1.- Use 'HTML to text' action block to convert reply email body into pure Text

2.- Use the following expression

last(split(body('HTML_to_text'),'TEST JIRA#: "))

once applied it should provide the value SHAW-12345678

Please note if your email body contains other extra info, the expression suggested would not work as expected

More info on this technique here:

https://powerusers.microsoft.com/t5/Using-Flows/Flow-HTML-email-to-excel-sheet/td-p/199965

Hope this helps

[chucksta]

@efialttes  the person that replies back should just reply back with the the value needed, but sometimes they will reply back with:

Examples: (These are just examples and can vary based off what what type of JIRA project needs to be created)

EFC JIRA#: EFCADMIN-12345678

or 

EFCSTRL-12345678

 or 

a multitude of different ticket projects names.

So they can sometimes send it with out the TEST JIRA# in front of it. I can tell them to make sure to add it to the body if needed?

[abm]

Hi @chucksta 

For split expression you need to have some standard to maintain it whether it is ':' or '-'. It's better to implement a standard format when users replies it. How about add the ticket number in email subject? If not for different combinations you need to check using different expressions and check whether it's empty not. If empty move on to next expression and so on.

Thanks

[chucksta]

@abm  are you suggesting, I have them add the Ticket # to the subject?

[abm]

Hi @chucksta 

I would go with that option so that Subject will contain the ticket number. You could ask users to just use the ticket number or  JIRA Ticket - xxxxx or JIRA  Ticket : xxxxx. If going with JIRA Ticket format you can use the split expression to extract the ticket number. If not directly use the ticket number from the subject of email.

Thanks

[chucksta]

That is cool then, since its my process to change or update.

I will have them add it to the subject:

So the variable be: 

last(split(body('HTML_to_text'),'TEST JIRA#:"))

 for the subject search? Wont I just need the information that comes after, "TEST JIRA#:"  ?

Would I need these two variables:

[abm]

Hi @chucksta 

Thanks for your quick reply. Instead of body('HTML_to_text') you need to map the email subject. Then split with # or colon to get the number. See my earlier post about some of standards which I mentioned.

[chucksta]

@abm  I see what you are saying but that is the difference. I may not have done a great job explaining it.

The ticket number field which is in the body of the email will be empty on the initial email that is sent to the users.

See below, the email they will receive:

The JIRA Test# will be empty on the email sent to them, once they create the JIRA, they will then either reply back with the ticket number either in the field next to JIRA Test# as so below:

or just at the top of the email body. All I want is the data that is next to JIRA TEST#(EFCADMIN-TEST) be be filled into the corresponding SHAREPOINT field for JIRA TEST and that particular item once the reply back to the email I have sent them.

[efialttes]

Hi

Thanx for the effort to explain with more details your challenge.

Does the JIRA ID (the one you need to extract) have a fixed lenght? If so, how many characters?

As it is explained here, split() splits the original text in N pieces, by using a delimiter.

In the expression I suggested, the delimiter was 'JIRA TEST#: ', assuming it is present only once in your email body. If so, your email body is to be splitted in two pieces: Piece#1 (first) contains all the characters before the delimiter; Piece#2 (and last) all the characters after the delimiter.

Now, if the body of the email just contains

JIRA TEST#: <your JIRA ID>

, then you just need to combine the previous expression with last() to extract Piece#2 from your email body.

But if the email body contains more characters after the delimiter

JIRA TEST#: <your JIRA ID>

then we need either to nest two split() funtions, or use substring() to extract a fixed number of characters from the front of Piece#2.

That's why I am asking if the JIRA ID (the one you need to extract) has always a fixed lenght

Hope it helps

[abm]

Hi @chucksta 

Who generates the initial email? Is that generated by flow or other automated way? I would still recommend to add the ticket number in the subject so that you can easily extract the value. If you going to extract from body of the email you are looking to do split and string manipulations to achieve your requirement.

Thanks

[chucksta]

@abm  So this is what I had setup but it keeps showing the error below:

and is not pulling the value from the subject in the email. 

This is the subject before the reply, I added the EXETER JIRA#: via the flow so they will have this field in the subject now, but its not capturing the value after the colons:

This is what i placed in the test email subject i sent to test the flow:

But in the above error no value was pulled and the flow errors out. Sorry if I am making this difficult but i am struggling to understand the writing of the expressions.

This is my flow for the trim:

[abm]

Hi @chucksta 

In your expression you need to map the email subject. Currently you have hardcoded the text which is causing the issue. If you want a screenshare let me know. Happy to help. I have send you an email earlier.

Thanks

[abm]

Hi @chucksta 

Thanks for the screen share. Glad that we managed to resolve the issue. If you have any more questions please post in this community forum. There are lots of good knowledge and people here to help you.