Here is a business scenario for you. You have a list in SharePoint and would like to capture comments from the users. However, here is a twist. You want to capture the whole history/audit trail of all the comments users typed in, without the danger of user accidentally deleting comments left by the previous user edits. An example that could benefit from this use case is some sort of Help Desk SharePoint list or a Vacation Request Approval list where you want to capture all the comments left by all the users. You might also need this functionality on a Task List, where you want to keep all the comments related to a particular task together. Luckily, achieving above scenario in SharePoint is very easy via “Append Changes” functionality. Let me explain step by step how to make it possible.
I need to add calculated column (or something else) to my list which can discover and write, if is something in column of 'multiple lines of text' within the bounds of this list. I can do it with Calculated column for 'Single line of text', or date, etc. I've used formula: =IF(Column10,'OK'). I am using SharePoint 2010. I have a list with below columns: Column Name Data Type. 1) Description multiple lines of text. 2) Status Single line of text. I need to create a calculated column by concatenating these 2 columns. But when I try to create a calculated column, I am not able to see 'Description' column in the 'Insert Column' section.
Step 1: Enable Version history on a list
To enable versioning, follow the instructions below.
- Go to the list settings, then click on Versioning Settings
- Under Item Version History, switch Create a version each time you edit an item in this list? radio button to Yes. Click OK
Step 2: Create a new “multiple lines of text” column
- Unless you have it already, go ahead and create a new column (List Settings > Create a Column)
- In the example below, I am setting up a column on a Task List web part, so I can track Task comments. Make sure to choose Multiple lines of text for the column type
- At the bottom of the “create a column” screen, switch Append Changes to Existing Text radio button to Yes. Click OK
Step 3: Configure view
This step is optional and is more of a personal preference. If you decide to display the column you created in the view, you will notice a clickable “View Entries…” link appear next to each item. That is because your comments now are not just one entry, but rather several distinct entries in one column. So it is not practical to display all lines of comments/text on this screen. To view all of them, just click on View Entries…
So now when you either click on the item itself or View Entries…, you will be able to see the whole log of comments. Enjoy!
NOTES:
- Append Changes to Existing Text functionality is only available on the SharePoint Lists, not document libraries.
Solution: Make the new field a calculated field and enter the formula, referencing the original field in [brackets].
Obstacle: Text functions in SharePoint list columns do not work properly on multiple line text columns.
The work-around here is to trick the new SharePoint list field into thinking it is operating on a single-line text field. While it is true that the text functions don’t work perfectly for multiple line text fields, they are adequate for simple tasks such as truncation. The issue is that SharePoint validates the calculated field when saving and throws an error if the function refers to a multiple line text field, preventing the save operation.
So how do you trick it?
Sharepoint Calculated Column With Multiple Lines Of Text In Single Cell
1. Create Field1, single line text
Sharepoint Calculated Column Multi Line Text
2. Create Field2, calculated field, formula=RIGHT([Field1],LEN([Field1])-8)(this gets rid of the html tag that will appear at the beginning as an artifact of operating on a multiple line text field)
3. Create Field3, calculated field, formula=LEFT([Field2],50) & “…”
(this will display the first 50 characters of the original field follows by the ellipse)
4. To prevent validation, do this from the site settings window
a. Delete Field1
b. Create Field1, multi-line text
5. Create a custom view and hide Field1 and Field2 and set it as the default view
(they will still appear in the item view, but not in the edit window since they are calculated fields)
There you have it. Steps for tricking SharePoint into doing something it was designed to do, but prevented from by poor validation logic. Now how to we trick those MS Devs into removing that erroneous validation logic?
Enjoy.
Did this help you? If so, please leave a comment!