cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chucksta
Helper II
Helper II

Extracting Data from Emails but need help

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:

 

1.jpg2.jpg3.jpg 

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
abm
Super User III
Super User III

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


Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blogPower Automate Video Tutorials

View solution in original post

14 REPLIES 14
abm
Super User III
Super User III

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


Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blogPower Automate Video Tutorials

View solution in original post

efialttes
Super User III
Super User III

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



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



chucksta
Helper II
Helper II

@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
Super User III
Super User III

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



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blogPower Automate Video Tutorials
chucksta
Helper II
Helper II

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

 

abm
Super User III
Super User III

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



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blogPower Automate Video Tutorials
chucksta
Helper II
Helper II

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
Super User III
Super User III

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.



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blogPower Automate Video Tutorials
chucksta
Helper II
Helper II

@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:

 

5.png

 

 

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:

6.png

 

 

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.

 

 

 

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



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



abm
Super User III
Super User III

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



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blogPower Automate Video Tutorials

@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. 

8.png

 

 

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:

 

10.png

 

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

11.png

 

 

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:

 

12.png

abm
Super User III
Super User III

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



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blogPower Automate Video Tutorials
abm
Super User III
Super User III

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.

 



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blogPower Automate Video Tutorials

Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (3,752)