One of the more frustrating (albeit understandable) elements of working with calculated columns is the restrictions on what column types can be referenced in your formulas. What it all boils down to is that you can only reference fields that have a return type of "Text" and these must be in the form of a single value (i.e. "Single line of text", "Number", "date/Time", etc.). What you can’t reference is things that can have more than one value or more than one line ("Lookups", "Multiple Select" choice fields, "Multiple lines of text", etc.) …or can you?
What I’m going to show you is how to reference a "Multiple lines of text" column in a formula, then truncate it to 300 characters in order to have it display as a "Summary" field (thanks to a question asked by "ebrown" on the STP forums).
For this example, I’ll just be using a custom list with the default "Title" column.
Since we can’t "directly" reference a "Multiple lines of text" column with a calculated column formula, we need to use the trick of creating a "Temporary" column first in order to make the reference, then delete/recreate the column with the appropriate data type – this will "Fool" the system into thinking it has a good reference and allow it to pass through without throwing up an error.
To do this, create a new column called "Description" and leave all of its settings as default (leave it as a "Single line of text").
Create another column called "DescriptionCalc", make it a "Calculated" type and enter in the following formula:
This does nothing more than make it so the calculated column will equal the text column (as in, display whatever is in the text column).
Immediately after creating the calculated column (while still in the "List Settings" page), delete the "Description" column.
As soon as it’s deleted, immediately go back and recreate it, but this time make it a "Multiple lines of text" type – and make sure that you use the exact same name ("Description") for the column.
This will now give us our starting columns that will include a calculated column with a reference to a type of column it would normally not be able to have.
To test things as they are (to make sure everything is now working), create a new item on the list and add in a few lines, or paragraphs into the text field. On saving, we should see the following:
So far so good, but notice the added "<div>" tags surrounding the text displayed in the calculated column? This is just one of those things that SharePoint does in order to render content in the different fields, and since a calculated column is a "text-only" field, it renders the content of the "Description" field as literal text and not html.
So, we’re partly there, but we need a way to remove the extra tags added in the text so it appears exactly the same as the original column.
Adding the Script
Once again, we’re going to be using the “TextToHTML” script created by Christophe at http://www.pathtosharepoint.com in order to take the literal text displayed, and make it html instead.
On the view page for our list, add in a CEWP (“Content Editor Web Part”), drag it down below the list web part, then open its source editor and paste in the following (*Note – I’ve stripped down the script from Christophe’s original version in order to meet the immediate needs for this list):
The script itself, simply finds the “TD” (table cell) tags on the page that contain the text “<div”, and if so, takes it and modifies the “TD” tag’s “inner html” property to use the text instead. By doing this, the tags themselves are not rendered on the page, but rather rendered as full html tags. Also, for those of you already familiar with this script, notice how I’m searching for “<div” and not “<div>”. I’m doing it this way in order to be able to use this script with “Enhanced rich text” as well as the standard “Rich text” and “Plain text” options of the “Multiple lines of text” field type. When using the “Enhanced rich text” option, the opening “div” tag will also contain a “class” attribute before its closing “>” part. By searching for just the opening part of the div (“<div”), we can trap each formatting type.
Now we have the calculated column referencing a column it shouldn’t be and displaying its text as html.
The Summary View
The next step is to “Truncate” or trim-down the text being displayed so we can have a “Summary” view of the content in the “Description” field.
We could accomplish this by more script if we wanted, but in order to make this as simple as it can be, we’ll want to use another calculated column that will display a portion of the “DescriptionCalc” column.
First though, you may be asking “Why don’t we just modify the formula in the “DescriptionCalc” column to trim down what it’s displaying?”
We could do this, but the problem is that since we’ve fooled the system into allowing us to reference a “Multiple lines of text” field, if we were to modify and attempt to save our original formula, it would try and reconnect, or re-reference the “Description” column and would now throw an error since it’s a type that we can’t reference. To make it work, we’d have to delete the “Description” column and recreate it as a “single line of text” type, update our formula in the calculated column, the delete/recreate the “Description column again as a “Multiple lines of text” type…and we’d have to do this each and every time we wanted to make a change (a hassle in itself, but also dangerous in that it would remove any and all data we have in the “Description” column each time – no good).
The workaround to this is to create yet another calculated column, but this time make a reference to the first calculated column and use it to create our formula for trimming down the content we’ll display. Since we won’t be “directly” referencing a non-allowed field type, we can freely modify the formula as much as needed and never run into problems when saving (gotta love workarounds to workarounds).
So, create a new column called “Summary”, make it a “Calculated” type and enter in the following formula:
This formula simply starts at the far left of the text in displayed in the “DescriptionCalc” column and grabs the first 300 characters, then adds a 3 ellipse set at the end (I used “300” arbitrarily for this- in reality, you can use whatever value you want since the end result will be rendered html and not “text”).
On saving, we’ll see a trimmed down “Summary” display of the “Description” content, along with the other columns (which takes up a lot of space) so to clean up a bit, remove the “DescriptionCalc” column from the view:
Another neat test is to go back in and change the “Type of text to allow” in the “Description” field to “Enhanced rich text” and add in an image and various formatting elements:
I did have to bump up the number of characters in the “summary” formula to 400 for this (all the extra formatting adds a lot of characters to the html), but the idea is still the same – start at the left of the text displayed and grab the defined number of characters, then display just that part.
Just because the system tells you that you can’t do it, doesn’t always mean that it can’t be done…we just have to get “creative” in how we get around some of the limitations, and that’s where it can get fun.
Till next time…