Solved: How to make multiple lines of text in SP appear as...

Anonymous · ‎11-02-2020

Hi All,

I have built a flow that triggers when I receive an email from a certain mailbox. The flow saves the body of the email as a multiple line of text field. The body of the email is autogenerated and follows the following format:

Name:

Account Number:

Email Address:

Date:

I want to be able to split these fields in the body of the email to appear in separate columns of the SharePoint list. Is this possible?

Thanks you in advance for your help.

Ian

Paulie78 · ‎11-02-2020

The HTML source I got from you 😆 But I just put what you provided into a "Compose" step to simulate your scenario. I trigged the flow manually.

So yours would be like:

When an email arrives
HTML to Text (Using the body of the email from the "When email arrives" step).
Compile the data as per the flow I gave you.
Create your item, augmenting it with the output from the parse JSON step.

I would build it into your current flow. It shouldn't be difficult to modify.

View solution in original post

Paulie78 · ‎11-02-2020

Yes it is possible. I do this often and just trying to help someone else in another thread doing a similar thing. The best thing is for you to post the output of the email into the "Code Sample" on here, then I or someone else can help you build the expression.

Anonymous · ‎11-02-2020

Hi Paulie,

Thanks for your response. Stupid question but is the "Code Sample" the html output of the body? If so please find below. Apologies if this is not what is required, Let me know and I can provide the correct information. Thanks a lot for your help.

<!--

@font-face

{font-family:Helvetica}

@font-face

{font-family:"Cambria Math"}

@font-face

{font-family:Calibri}

p.MsoNormal, li.MsoNormal, div.MsoNormal

{margin:0cm;

margin-bottom:.0001pt;

font-size:11.0pt;

font-family:"Calibri",sans-serif}

a:link, span.MsoHyperlink

{color:#0563C1;

text-decoration:underline}

a:visited, span.MsoHyperlinkFollowed

{color:#954F72;

text-decoration:underline}

p

{margin-right:0cm;

margin-left:0cm;

font-size:12.0pt;

font-family:"Times New Roman",serif}

span.EmailStyle17

{font-family:"Calibri",sans-serif;

color:windowtext}

.MsoChpDefault

{font-family:"Calibri",sans-serif}

@page WordSection1

{margin:72.0pt 72.0pt 72.0pt 72.0pt}

div.WordSection1

{}

-->

</style></head><body lang="EN-IE" link="#0563C1" vlink="#954F72"><div class="WordSection1">Hello,A person has requested to be contacted.Information:Name: Joe Bloggs<p style="margin-right:0cm; margin-bottom:0cm; margin-left:0cm; margin-bottom:.0001pt; background:white; box-sizing:inherit; font-variant-ligatures:normal; font-variant-caps:normal; orphans:2; text-align:start; widows:2; text-decoration-style:initial; text-decoration-color:initial; word-spacing:0px">Account Number: 100<p style="margin-right:0cm; margin-bottom:0cm; margin-left:0cm; margin-bottom:.0001pt; background:white; box-sizing:inherit; font-variant-ligatures:normal; font-variant-caps:normal; orphans:2; text-align:start; widows:2; text-decoration-style:initial; text-decoration-color:initial; word-spacing:0px">Email Address: joebloggs@gmail.com<p style="margin-right:0cm; margin-bottom:0cm; margin-left:0cm; margin-bottom:.0001pt; background:white; box-sizing:inherit; font-variant-ligatures:normal; font-variant-caps:normal; orphans:2; text-align:start; widows:2; text-decoration-style:initial; text-decoration-color:initial; word-spacing:0px">Date: 02/11/2020   C2 General</div></body></html>

Paulie78 · ‎11-02-2020

That is the output I meant. But I also meant for you to use this button:

Because the HTML output, which is key to making what you want has to be accurate for us to be able to make the correct expression and pasting it in the normal window changes it a bit. Can you paste it to https://pastebin.com/ and then share the link, then it will be exactly right.

Anonymous · ‎11-02-2020

Thanks Paulie,

Please find link here: https://pastebin.com/QAzcdAMh

Paulie78 · ‎11-02-2020

You can do it like this: https://ibb.co/J5kDHx7

Explanation:

HTML is converted to text
Create a variable that is just a linefeed
Split the text on a linefeed into an array:

split(outputs('Html_to_text')?['body'], variables('lineFeed'))

Filter the resulting array to remove all blank lines.
Create a JSON with the output from the filter:

{
 "Name": "@{trim(substring(body('Filter_array')[3],5))}",
 "AccNo":"@{trim(substring(body('Filter_array')[4],15))}",
 "email":"@{trim(substring(body('Filter_array')[5],14))}",
 "Date":"@{trim(substring(body('Filter_array')[6],6))}"
}

Add a parse JSON step to make the values easier to access.

That should be it!

Please...

If I answered your question Accept it as a solution ✔️

If I helped you out, please give me some Kudos 👍

Thanks 😙

Anonymous · ‎11-02-2020

Hi Paulie,

Thank you this is very helpful. Sorry for the stupid questions but where are you getting the HTML source and what is your trigger? Do I build this into my current flow or should I make it a separate flow? My current flow is simply When an email arrives > Create Item

Paulie78 · ‎11-02-2020

The HTML source I got from you 😆 But I just put what you provided into a "Compose" step to simulate your scenario. I trigged the flow manually.

So yours would be like:

When an email arrives
HTML to Text (Using the body of the email from the "When email arrives" step).
Compile the data as per the flow I gave you.
Create your item, augmenting it with the output from the parse JSON step.

I would build it into your current flow. It shouldn't be difficult to modify.

Anonymous · ‎11-02-2020

Perfect, Thanks for all your help and timely responses. I t was very helpful.

Anonymous · ‎11-02-2020

Hi Paulie,

I am getting the following error message. Do you know how this can be resolved?

Thanks

Ian

https://ibb.co/Q8dGyBP

Paulie78 · ‎11-02-2020

I can certainly help you figure it out. That expression is trying to access the 3rd element of the array. Which is the first line of data that you are trying to access. So it suggests that the array has not been created. Go into the run history and see what the output is from the filter array action, that will show you what the state of the array is. Based on that, we can find a solution.

Anonymous · ‎11-03-2020

Hi Paul,

Thanks again for your response. You are correct the output of the filter array is blank. This is the input:

[

"Hello,\n\nA person has requested to be contacted.\n\nInformation:\n\nName: Joe Bloggs\n\nAccount Number: 100\n\nEmail Address: joebloggs@gmail.com\n\nDate: 02/11/2020\n\n\n\n\n\n\n\n\nC2 General"

]

This is obviously also the output of "Create an Array of Lines".

Would you be able to advise me on a solution?

Thanks

Ian

Paulie78 · ‎11-03-2020

Yes, it seems to me that the "split" has not happened at the creation of the array. All of the newline characters are still present. Which makes me think that you did not put a line feed in this step:

You need to literally put your cursor in the Value field and press the enter key. Then it should split on every new line.

Paulie78 · ‎11-03-2020

Try changing your split expression to the following, it is a bit neater:

split(outputs('Compose'), base64ToString('Cg=='))

Anonymous · ‎11-03-2020

Hi Paul,

I made the change you mentioned and I hit enter in the value field. The output from the filter array is the same as the input and is as follows:

[

"Hello,\n\nA person has requested to be contacted.\n\nInformation:\n\nName: Joe Bloggs\n\nAccount Number: 100\n\nEmail Address: joebloggs@gmail.com\n\nDate: 02/11/2020",

"\nC2 General"

]

However I am still getting the same error message: https://ibb.co/5R6j62f

Do you know why this might be? Please see full flow for your reference.

https://ibb.co/KjX3xSQ
https://ibb.co/8DYhr1m

Thanks again

How to make multiple lines of text in SP appear as separate entries in their own columns from a flow

Helpful resources

Power Automate News & Announcements

Power Automate Community Blog

Help make Flow Design easier