cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Extract email content to add row to Smartsheet.

Hello All,

This is my first Flow so I'm trying to piece together what I have discovered in my searches.

I'm trying to copy text from an email and add it to a row in Smartsheet.

 

Email looks like this:

SUBJECT

ICPO SUBMITTED FOR APPROVAL #IC79-0591 Plywood

BODY

Submitted for approval by USER.


Job: Job_Name LA791723
Order Date: 12/2/2019 5:00 PM
Need By: 12/3/2019 5:00 PM
EF Job Number:

Cheers!

Additional hyperlink here.

 

 

I want to extract the following text and add it to the row.
#IC79-0591
Plywood
USER
LA791723
Need By: DATE

 

Any assistance would be greatly appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Responsive Resident
Responsive Resident

Hi @RichLucas, you're on the right track. Kudos on your attempt in your first flow.

I would have (and have indeed) used the same combination of functions\expressions.

Source

PAUG_Parseemail2.png

Flow / Results

PAUG_Parseemail1.png

At a high level, each variable is tied to an expression. They are:

IC number  - grabs anything between "Approval" and "Plywood" in subject

substring(triggerOutputs()?['body/subject'],add(lastIndexOf(triggerOutputs()?['body/subject'],'APPROVAL'),9),sub(lastIndexOf(triggerOutputs()?['body/subject'],' '),add(lastIndexOf(triggerOutputs()?['body/subject'],'APPROVAL'),9)))

Product  - Grabs anything after the ICnumber(#IC79-0591) in subject

substring(triggerOutputs()?['body/subject'],lastIndexOf(triggerOutputs()?['body/subject'],' '),sub(Length(triggerOutputs()?['body/subject']),lastIndexOf(triggerOutputs()?['body/subject'],' ')))

User - Anything between 'approval by' and 'Job:' in the body

substring(triggerOutputs()?['body/bodyPreview'],add(indexOf(triggerOutputs()?['body/bodyPreview'],'approval by'),11),sub(indexOf(triggerOutputs()?['body/bodyPreview'],'Job:'),add(indexOf(triggerOutputs()?['body/bodyPreview'],'approval by'),11)))

Job - Grabs anything between 'Job:' and  'Order Date:' in the body

substring(triggerOutputs()?['body/bodyPreview'],add(indexOf(triggerOutputs()?['body/bodyPreview'],'Job:'),4),sub(indexOf(triggerOutputs()?['body/bodyPreview'],'Order Date:'),add(indexOf(triggerOutputs()?['body/bodyPreview'],'Job:'),4)))

Need By  -  Grabs anything between 'Order Date:' and  'EF Job Number:' in the body

substring(triggerOutputs()?['body/bodyPreview'],add(indexOf(triggerOutputs()?['body/bodyPreview'],'Need By:'),8),sub(indexOf(triggerOutputs()?['body/bodyPreview'],'EF Job Number:'),add(indexOf(triggerOutputs()?['body/bodyPreview'],'Need By:'),8)))

This was quick and very preliminary.The expressions may need tweaking and you can choose to use variables or not. It's up to you. Might be easier to use variables to help troubleshoot.

 

Hope this helps,

Aman

------------------------------------------------------------------------------------------------------
If my post helps you with your problem or answers your question, please mark it Solved or Answered. This helps anyone with similar challenges. If you like my response, please give it a Thumbs Up.
------------------------------------------------------------------------------------------------------

 

View solution in original post

5 REPLIES 5
Highlighted
New Member

This is what I have so far.

 

First Part.pngSecond Part.png

Highlighted
Super User
Super User

@RichLucas 

Let make it in simple way using split.

1 use compose pass subject

2. Another compose with expression 

Last(split(compose (output),'Approval'))

It will give your string starting with # in subject.

3. To collect words from string which we got in step 2. Use another compose pass same expression with index like Last(split(compose (output),'Approval'))[0] it will give you #with code string.

In this way you can extract all words. 

Use split, first, last,index.

 

Not infront of laptop so unable give screen. If you need will give you later.

 

Thanks

Hardesh

Highlighted

Thank you @Hardesh15 for replying.

When you have a chance, could you elaborate more?

 

Highlighted
Responsive Resident
Responsive Resident

Hi @RichLucas, you're on the right track. Kudos on your attempt in your first flow.

I would have (and have indeed) used the same combination of functions\expressions.

Source

PAUG_Parseemail2.png

Flow / Results

PAUG_Parseemail1.png

At a high level, each variable is tied to an expression. They are:

IC number  - grabs anything between "Approval" and "Plywood" in subject

substring(triggerOutputs()?['body/subject'],add(lastIndexOf(triggerOutputs()?['body/subject'],'APPROVAL'),9),sub(lastIndexOf(triggerOutputs()?['body/subject'],' '),add(lastIndexOf(triggerOutputs()?['body/subject'],'APPROVAL'),9)))

Product  - Grabs anything after the ICnumber(#IC79-0591) in subject

substring(triggerOutputs()?['body/subject'],lastIndexOf(triggerOutputs()?['body/subject'],' '),sub(Length(triggerOutputs()?['body/subject']),lastIndexOf(triggerOutputs()?['body/subject'],' ')))

User - Anything between 'approval by' and 'Job:' in the body

substring(triggerOutputs()?['body/bodyPreview'],add(indexOf(triggerOutputs()?['body/bodyPreview'],'approval by'),11),sub(indexOf(triggerOutputs()?['body/bodyPreview'],'Job:'),add(indexOf(triggerOutputs()?['body/bodyPreview'],'approval by'),11)))

Job - Grabs anything between 'Job:' and  'Order Date:' in the body

substring(triggerOutputs()?['body/bodyPreview'],add(indexOf(triggerOutputs()?['body/bodyPreview'],'Job:'),4),sub(indexOf(triggerOutputs()?['body/bodyPreview'],'Order Date:'),add(indexOf(triggerOutputs()?['body/bodyPreview'],'Job:'),4)))

Need By  -  Grabs anything between 'Order Date:' and  'EF Job Number:' in the body

substring(triggerOutputs()?['body/bodyPreview'],add(indexOf(triggerOutputs()?['body/bodyPreview'],'Need By:'),8),sub(indexOf(triggerOutputs()?['body/bodyPreview'],'EF Job Number:'),add(indexOf(triggerOutputs()?['body/bodyPreview'],'Need By:'),8)))

This was quick and very preliminary.The expressions may need tweaking and you can choose to use variables or not. It's up to you. Might be easier to use variables to help troubleshoot.

 

Hope this helps,

Aman

------------------------------------------------------------------------------------------------------
If my post helps you with your problem or answers your question, please mark it Solved or Answered. This helps anyone with similar challenges. If you like my response, please give it a Thumbs Up.
------------------------------------------------------------------------------------------------------

 

View solution in original post

Highlighted
Community Support
Community Support

Hi @RichLucas ,

 

Have your problem been solved by @Amanthaper  's solution?

If yes, please go ahead and mark the post as solved by clicking “Accept as Solution” so that this thread will be marked for other users to easily identify!

 

Best regards,

Alice       

 

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Top Solution Authors
Top Kudoed Authors
Users online (7,805)