Taming the Elusive “Calculated Column” – Referencing a Multiple Lines of Text Column With Append Text: Part 1

As a follow-up to a previous article I wrote on “Referencing Multiple Lines of Text” in a calculated column, and to address an issue brought up on the STP forums regarding how to use this technique with a “Multiple lines of Text” column that is set to “Append Changes to Existing Text” and how to view all of the versions (appended changes) in a regular list, I give you the following:

“How to reference a ‘Multiple Lines of Text’ column in a ‘Calculated Column’ to display a complete list of appended changes to all items on a list as a group – in 10 Easy Steps”.

Using the exact same technique I walked through in the previous article, we’ll create a custom list, add in a temporary column to set our calculated column reference, delete the column and recreate it as the appropriate type, then use some JavaScript to tie it all together with a custom view.

Step 1: Create your list

For this, I’ll just be using a custom list called “All Comments”. 

In order for the “Append” part to work, we need to turn on “Versioning” in the list settings (Settings > List Settings > Versioning Settings > Choose “Create a version each time you edit an item in this list”).

Step 2: Create your temporary column

In order for you to be able to reference a “Multiple Lines of Text” column from a “Calculated Column”, you must fool the system into thinking its ok to do so. 

The technique is simply to create a column with the name you intend to use in your formula making sure to leave all of its settings as default (leave it as a “Single Line of Text” type).

Create a new column called “Comments” leaving its setting as default.

Step 3: Add the Calculated Column

Since we now have our temporary column created, we can make a reference to it in our “Calculated Column” without any errors.

Create a new column called “Expanded Comments”, make it a “Calculated” type and add in the following formula:

=[Comments]

Step 4: Reset our Temporary column

Once we’ve made our reference, we can go back into the “Comments” column, delete it, and then recreate it as the type we really want to use.

Delete the “Comments” column then immediately recreate it (making sure that you are using the exact same name for the column), and this time set it as a “Multiple Lines of Text” type and choose the option to “Append Changes to Existing Text”.

Step 5: Test Round 1

To test this and make sure everything is working at this point, create a new item on the list and you should see something similar to this:

Step 6: The HTML Calculated Column

Notice that our calculated column, although displaying the correct information, is rather ugly at the moment with the added “div” tags surrounding the comment text? 

To fix this, we’ll once again tap into Christophe’s “TextToHTML” script and add it in to a CEWP on the page.

On the view page, add in a CEWP and paste in the following script into its “Source Editor”:

  /*>
  Text to HTML - version 2.1<br>
  Questions and comments: [email protected]
  */
  function TextToHTML(NodeSet,  HTMLregexp) {
  var CellContent = "";
  var i=0;
  while (i < NodeSet.length){
  try {
  CellContent = NodeSet[i].innerText || NodeSet[i].textContent;
  if (HTMLregexp.test(CellContent)) {NodeSet[i].innerHTML = CellContent;}
  }
  catch(err){}
  i=i+1;
  }
  }
  // List views
  var regexpTD = new  RegExp("^\\s*<([a-zA-Z]*)(.|\\s)*/\\1?>\\s*$");
  TextToHTML(document.getElementsByTagName("TD"),regexpTD);
  </script>

Note – although I have intentionally trimmed out some of the unused lines of script for this specific walkthrough, you can copy the entire script from Christophe’s site and use it as is – http://www.pathtosharepoint.com/HTMLcc

Immediately you should now see the comments being displayed appropriately:

Next, we need to add in a few edits to the item on the list in order to see how the system handles these “versions” of the text.

Edit the item and add in a few changes (saving after each one). 

By default, the list itself will only display the most recent edit (standard behavior of “Versioning”), but for reference sake, click on the item to view its details then click on the “Version History” link to see all of our changes.

Although we “could” use this page as our method of seeing all of the comments, it’s not practical since it only lists out the versions for an individual item, not for a group of items.

Till next time…
– Dessie

Tomorrow Dessie will show us the "Magic" URL.

Dessie Lunsford
Points-of-Sharing