Tuesday 18 February 2020

How to append comments to a multi-line text column in a document library

I recently needed to create a Document Library in SharePoint Online with a text field that could append blocks of text against each document to track progress of several workflows. Of course, the first task is to check Google to see whether there was a workaround for not being able to select an "Append" option on the Multiple lines of text field in a Document Library. After about an hour of trying different search terms I had to conclude that there wasn't an easy, out-of-the-box way of doing this. But there's always a workaround, right?

The secret lies in workflow variables.

But first, create a Multiple lines of text field. I called mine "Actions Log", because I'm logging the workflow actions. Then you'll need to use either an existing workflow or - if append text is the only action you need to take - create a new workflow.

Within the workflow, you're going to need two variables. One to hold any existing text in your Multiple lines of text field and one to hold a copy of the original text plus any appended text.

To create a Workflow variable, you need to first select "Set Workflow Variable" from the Actions drop-down.
So create your first workflow variable ... I've put some conditions in my workflow, but of course you don't have to. Click on the Action drop-down and find Set Workflow Variable. If you can't see that option, start typing the text "set workflow variable" and it should appear.

Having selected "Set Workflow Variable" you're now prompted to create a new variable.
I'm calling my new Variable "ActionLog" (dropping the "s" so I can easily distinguish between the two), and I'm setting (content) Type to "String".

Naming the new variable and setting the content type are both done in the same dialogue box.
Next, set the Variable to hold the contents of the ActionsLog field. This will copy the existing text - if any - in the ActionsLog field to the ActionLog variable.

This step causes the value in the Action Log text column to be copied into the ActionsLog variable.
Now you're ready to create your second variable. I called this one ActionsLogAppend, also set as a string. So just repeat the above steps ...

Create a second variable called ActionsLogAppend which will hold the original Action Log text and any new appended text. 
Now you're ready to build the actual machinery of the Append Text function. In the example here, I'm having the Workflow append predetermined text into the existing Multiple Lines of Text field. And I do that by opening up the Parameter Builder to create a value for the second variable.

Click on the ellipsis to launch the String Builder window, which in turn creates the ActionLogAppend value.
Building the value for the variable is pretty simple. I wanted my appended text to look like this:

20/11/2019: 10.55 - 18 months to End of Lease acknowledged by John Smith:
[Content of first variable]

So in the String-Builder, add a lookup for the Date (and the Time, if you want it), and add the identity of the person who carried out the action (that'll be the value for ModifiedBy).

I set the Date and Time to "Short Date" and "Short Time" content types, but you may prefer a different setting.
Then I just copied the combined text of the two variables into the Actions Log variable of the Workflow.

This is what the string looks like in the String Builder window. I put a line return after the ModifiedBy call for clarity, putting the appended text on a new line ...
The final String in the String-Builder would look like this - 

[ModifiedDate]: [ModifiedTime] - 18 months to End of Lease acknowledged by [ModifiedBy]:
[Variable:ActionLog]

But if you wanted to, you could copy this combined text back into the Multiple Lines of Text field via the workflow.

POWER AUTOMATE AND SHAREPOINT ONLINE

Of course, old-school workflows don't last forever - Microsoft has probably turned off 2010 WorkFlows by the time you read this. You could just use a 2013 WorkFlow without too much hassle ... but it's better to switch to Power Automate and use a Flow - it's a steep learning curve, but not if you're already familiar with WorkFlows.

Using a Flow to append text in a document library is, if anything, even easier than messing with SharePoint Designer variables.

In the Flow that you have set up to manage changes to your library metadata, insert an Update File Properties action (if there's not one in there already), then find the Multiple Line Text field and add this to it:

The method is remarkably similar to how we achieved the WorkFlow version above, with a couple of small differences.
The "formatDateTime" expression captures the date when this latest update was made. I used this bit of code (in an Expression) to grab the date that the "Start and Wait for Approval" action earlier in the Flow was completed ... then formatted it to omit the time, which wasn't so important. Like this:

formatDateTime(outputs('Start_and_wait_for_an_approval_-_End_Date_18')?['body/completionDate'], 'dd/MM/yyyy')

Then a bit of explanatory text, then the name of the person who made the Approval ("Responder Display Name") ... then a semicolon as a separator from earlier Log Actions.

To put the earlier Log Actions on a separate line, I added a Shift-Return, then just inserted a Dynamic Content call for the previous value in the ActionsLog field from the Get Files (properties only) call at the top of the Flow.

Pretty, simple, right?

That's it ... I hope this helps someone.


No comments:

Post a Comment