So many good blog posts come about from questions asked by others in the community. Add in some collaboration, and the creative and inquisitive minds of others, and you’ve got yourself a nice work around to fix a slight annoyance. Al Eardley tagged me on Twitter with the following question. How can we prevent line breaks from being stripped out of multi-line text fields populated in a Forms Pro survey response?
@MeganVWalker I am hoping that you might be able to help being the #Forms guru that you are: Whenever I use #PowerAutomate to save a multiline field to SharePoint it loses the line breaks. How can I retain the line breaks?— Al Eardley @ #MSIgniteTheTour (@al_eardley) January 13, 2020
I HATE not knowing the answer to things, and to this I wasn’t sure, so of course I went off to investigate. Forms Pro doesn’t strip out the line breaks, so something else must be doing it. For Al, he noticed it was happening when sending in to a SharePoint list. For me, I saw it was happening when adding responses to an email using the Outlook connector.
If you haven’t ever read Ryan Maclean‘s blog, where have you been? I’m proud to call him a very good friend, but even if he wasn’t, I would still rave about his blog, it’s awesome! So check it out. Anyway, Ryan is the one who suggested and also provided the resolution so that the line breaks can be retained, I’ve just written about it below. All credit goes to Ryan! This will have so many uses, but in this scenario let us first start with our Forms Pro survey. We have a few questions on this survey, with First Name, Last Name, then the Net Promoter Score, and finally a reason for the score given. It’s question 4 that allows for multiple lines of text.
In this scenario we are going to populate a SharePoint list with the Forms Pro responses received. Make sure the multi-line text field you are going to populate has the option to ‘Use enhanced rich text’ set to Yes.
Next we need our Power Automate flow. Here is the first part. Our trigger is based on the create of a record (the Forms Pro survey response entity). Next we will get the related Survey entity. This allows us to create a Condition to check the name of the Survey and make sure it matches the one we are looking for. If you want it to run for every survey, you can leave out the 2nd & 3rd steps.
Next we need to add a step to get the response details if the survey is the correct one. Use the Microsoft Forms connector and the Get response details action. Pick the correct survey, then use the following expression in the Response id value.
Now if we move on to our next action we can use the values from the survey response and populate the various fields.
However, this is where the issue lies. Even though the survey response provided for this question can have the text in multiple paragraphs, those are stripped out, leaving us with just a mass of text without the formatting.
What we need (and thanks again Ryan!) is a compose step to sort things out and make sure the line breaks are kept. Your expression will look something like this. This is the part that will need to be replaced with whatever question response you want to pass through: body(‘Get_response_details’)?[‘r1023da0d92764a5a8bafaea47bf08572’]
uricomponenttostring(replace(uriComponent(body('Get_response_details')?['r1023da0d92764a5a8bafaea47bf08572']), '%0A', '<br/>'))
Here is how Ryan broke it down for me:
uriComponent – converts the input to it’s URI Encoded representation. In URI encoding, a Line Break is represented as %0A
replace – we’re replacing the %0A with <br/> which is the HTML encoding for a line break
uriComponentToString – converts the URI encoded representation back to a string so we can use it
Now you can use the Output from that action above and populate your Multi-line Text field in your SharePoint list (or whatever you are trying to populate).
We can see now, a nicely formatted response is added to the corresponding field in the SharePoint list.
Want to just watch how to do this? Check out the video below: