Problem statement:
We had a requirement where we need to get values of multiline text field from SharePoint list and use it in one of the action in Power Automate. The real twister was when we found that multiline text column was setup with Append changes to existing text option. The problem was we were getting blank/null response when we try to access that field directly in Email body action using Power Automate.
Analysis & Findings:
We dig out the issue and found that when we set multiline text field with append changes option, and add any value in that field, it stores the value as separate version of an item and not storing directly to that field.
Solution:
We need to get those values from the version history of an item but there is no direct way for it. So, we need to follow below steps to get values from multiline text field in power automate.
First, we need to initialize below variables that will be used to get version history of an item in Power Automate.
- Add an action name “Initialize Variables”.
- It will ask following information.
- Now, let’s create first variable name “AppendComment”.
- Name AppendComment
- Type String
- Value <div class="ReactFieldEditor-AppendedData"><div>
- In a similar way, we need to create a variable - “ColumnName”.
- Name ColumnName
- Type String
- Value Column name from the list. Use the internal name. [Multiline Text Column]
- And third variable will be “ListName”.
- Name ListName
- Type String
- Value Your list display name. [ SharePoint Custom List Name]
- Now, add an action for “Send an HTTP request to SharePoint”.
- Add the following information in that action:
- SiteAddress - URL of your SharePoint Site
- Method - GET
- Uri - /_api/web/lists/GetByTitle('@{variables('ListName')}')/Items(@{triggerBody()?['ID']})/Versions
- Headers
- Accept (should be added as key)
- application/json; odata=nometadata (should be added as value)
- Now add an action called - “Apply to each”.
- And set below value in that action.
- body('Send_an_HTTP_request_to_SharePoint' )['value']
- Now add "compose" action inside Apply to each action.
- Add below value in Inputs textbox.
- formatdatetime(items('Apply_to_each')['Modified'],'MM/dd/yyyy hh:mm tt')
- Then we need to add Condition action.
- Add below expression in First section:
- items('Apply_to_each')[variables('ColumnName')]
- Then select "is not equal to" in second section.
- And then in third section, add null as value in expression as shown below.
- In YES side condition, add an action “Append to string variable”.
- Please set below expression and click ok
- Name - select AppendComment
- Value - In expression, add below value:
- concat('<div>',items('Apply_to_each')['Editor']['LookupValue'],' (<span>',outputs('Compose'),'</span>)',': ',items('Apply_to_each')[variables('ColumnName')],'</div>')
- Finally, Value is set in AppendComment variable. And we can use "AppendComment" variable in Send an email action.
- By following all above steps, finally we got correct output:
