cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
garethaw
Helper III
Helper III

Find values in an email and sum the values

Hi,

 

I'm trying to collect data from emails, I would like to build a flow that will look for values in an email and give me the sum total.

 

The number following the = sign is dynamic and changes all the time.

The body of the email is not the same length every time.

1 ACCEPTED SOLUTION

Accepted Solutions
grantjenkins
Community Champion
Community Champion

As long as the format of the email body was consistent in terms of the Black & White Scanner Count and Full Colour Scanner Count then this might get what you want.

 

Full flow below. I'll go into each of the actions:

grantjenkins_0-1669725355899.png

 

When a new email arrives would be setup the same as what you have for your requirements. I would assume you have some filter on the Subject, etc.

grantjenkins_3-1669725454394.png

 

Html to text will convert the Body of the email to text.

grantjenkins_2-1669725447254.png

 

Select will extract out each of the rows of text and split them by = to generate an array of objects with a Name/Value pair.

 

The From expression is below. It will split the text by new line character to give us an array of lines.

split(body('Html_to_text'), decodeUriComponent('%0A'))

 

The Name and Value expressions are below which split each of the lines in the array by = and trim out any extra spaces.

trim(first(split(item(), '=')))

trim(last(split(trim(item()), '=')))

 

grantjenkins_4-1669725533567.png

 

We then use Filter array to remove all objects except for the ones that match our criteria which is:

@OR(equals(item()?['Name'], 'Black & White Scanner Count'), equals(item()?['Name'], 'Full Colour Scanner Count'))

 

Note that we need to go into Edit in advanced mode in order to paste in the expression above.

grantjenkins_5-1669725714891.png

 

We can then use a Compose (or variable of type number) and use the following expression. It will convert each of the values to int then add them together to get the total.

add(int(body('Filter_array')[0]?['Value']), int(body('Filter_array')[1]?['Value']))

grantjenkins_6-1669725796433.png

 



----------------------------------------------------------------------

If I've answered your question, please mark the post as Solved.

If you like my response, please consider giving it a Thumbs Up.

View solution in original post

4 REPLIES 4
grantjenkins
Community Champion
Community Champion

As long as the format of the email body was consistent in terms of the Black & White Scanner Count and Full Colour Scanner Count then this might get what you want.

 

Full flow below. I'll go into each of the actions:

grantjenkins_0-1669725355899.png

 

When a new email arrives would be setup the same as what you have for your requirements. I would assume you have some filter on the Subject, etc.

grantjenkins_3-1669725454394.png

 

Html to text will convert the Body of the email to text.

grantjenkins_2-1669725447254.png

 

Select will extract out each of the rows of text and split them by = to generate an array of objects with a Name/Value pair.

 

The From expression is below. It will split the text by new line character to give us an array of lines.

split(body('Html_to_text'), decodeUriComponent('%0A'))

 

The Name and Value expressions are below which split each of the lines in the array by = and trim out any extra spaces.

trim(first(split(item(), '=')))

trim(last(split(trim(item()), '=')))

 

grantjenkins_4-1669725533567.png

 

We then use Filter array to remove all objects except for the ones that match our criteria which is:

@OR(equals(item()?['Name'], 'Black & White Scanner Count'), equals(item()?['Name'], 'Full Colour Scanner Count'))

 

Note that we need to go into Edit in advanced mode in order to paste in the expression above.

grantjenkins_5-1669725714891.png

 

We can then use a Compose (or variable of type number) and use the following expression. It will convert each of the values to int then add them together to get the total.

add(int(body('Filter_array')[0]?['Value']), int(body('Filter_array')[1]?['Value']))

grantjenkins_6-1669725796433.png

 



----------------------------------------------------------------------

If I've answered your question, please mark the post as Solved.

If you like my response, please consider giving it a Thumbs Up.

Hi,

 

Thanks for putting so much time into this, this is doing exactly what I need.

 

Would the initial email body only contain those 4 items, or could there be more in the email body?

 

You could add a Send an email action at the end of the flow and change it to HTML mode. Then add in something like the following. Note that Output is our Total count from our Compose, and Body is the body from our original email.

 

IMPORTANT: Make sure you don't email it to the same email address with the same Subject heading otherwise it might keep triggering the flow over and over.

 

grantjenkins_0-1669732080075.png



----------------------------------------------------------------------

If I've answered your question, please mark the post as Solved.

If you like my response, please consider giving it a Thumbs Up.

-

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Users online (1,627)