1,691 articles and 12,650 comments as of Monday, September 13th, 2010

Wednesday, June 17, 2009

Taming the Elusive “Calculated Column” – Referencing Multiple Lines of Text Column

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?

Overview

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.

Referencing Multiple Lines

The Setup

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”).

Referencing Multiple Lines

Create another column called “DescriptionCalc”, make it a “Calculated” type and enter in the following formula:

=[Description]

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.

Referencing Multiple Lines

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:

Referencing Multiple Lines

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):

<script type="text/javascript">
  var theTDs = document.getElementsByTagName("TD");
  var i=0;
  var TDContent = " ";
  while (i <theTDs.length)
  {
 try
{
TDContent = theTDs[i].innerText ||  theTDs[i].textContent;
if  (TDContent.indexOf("<div") == 0)
{
theTDs[i].innerHTML = TDContent;
}
}
catch(err){}
i=i+1;
}
</script>

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.

Referencing Multiple Lines

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:

=LEFT([DescriptionCalc],300)&"…"

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:

Referencing Multiple Lines

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:

Referencing Multiple Lines

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.

Conclusion

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…

- Dessie

Dessie LunsfordDessie Lunsford
Points-of-Sharing

View all entries in this series: Dessie Lunsford-Logic Formulas»
Entries in this series:
  1. Taming the Elusive "Calculated Column"
  2. EndUserSharePoint.com: Taming the Elusive "Calculated Column" - Customizing a Task List
  3. EndUserSharePoint.com: Taming the Elusive “Calculated Column” - Customizing a Task List (Part II)
  4. EndUserSharePoint.com: Taming the Elusive “Calculated Column” - Customizing a Task List (Part III)
  5. EndUserSharePoint.com: Taming the Elusive “Calculated Column” - Customizing a Task List (Part IV)
  6. EndUserSharePoint.com: Taming the Elusive “Calculated Column” - Text and Data
  7. EndUserSharePoint.com: Taming the Elusive "Calculated Column" - Customizing a Contacts List (Part I)
  8. EndUserSharePoint.com: Taming the Elusive "Calculated Column" - Customizing a Contacts List (Part II-I)
  9. EndUserSharePoint.com: Taming the Elusive “Calculated Column” - Customizing a Contacts List (Part II-II)
  10. EndUserSharePoint.com: Taming the Elusive “Calculated Column” - Customizing a Contacts List (Part II-III)
  11. EndUserSharePoint.com: Taming the Elusive “Calculated Column” – Text and Data (Part III)
  12. EndUserSharePoint.com: Taming the Elusive “Calculated Column” – Text and Data (Part IV)
  13. EndUserSharePoint.com: Taming the Elusive “Calculated Column” – Text and Data (Part V)
  14. EndUserSharePoint.com: Taming the Elusive “Calculated Column” – Text and Data (Part VI)
  15. Taming the Elusive “Calculated Column” – Lookup and Reference
  16. Taming the Elusive “Calculated Column” - Logic
  17. Taming the Elusive “Calculated Column” - Logic - Part 1
  18. Taming the Elusive “Calculated Column” - Logic - Part 2
  19. Taming the Elusive “Calculated Column” - Logic - Part 3
  20. Taming the Elusive “Calculated Column” - Logic - Part 4
  21. Taming the Elusive “Calculated Column” - Logic - Part 5
  22. Taming the Elusive “Calculated Column” - Logic - Part 7
  23. Taming the Elusive “Calculated Column” - Logic - Part 6
  24. Taming the Elusive “Calculated Column” - Logic - Part 8
  25. Taming the Elusive “Calculated Column” - Logic - Part 9
  26. Taming the Elusive “Calculated Column” - Logic - Part 10
  27. Taming the Elusive “Calculated Column” - Date and Time - Part 1
  28. Taming the Elusive “Calculated Column” - Date and Time - Part 2
  29. Taming the Elusive “Calculated Column” - Date and Time - Part 3
  30. Taming the Elusive “Calculated Column” - Date and Time - Part 4
  31. Taming the Elusive “Calculated Column” - Referencing Multiple Lines of Text Column
  32. Taming the Elusive “Calculated Column” – Date and Time – Part 5
  33. Taming the Elusive “Calculated Column” – Date and Time – Part 6
  34. Taming the Elusive “Calculated Column” – Date and Time – Part 7
  35. Taming the Elusive "Calculated Column" - Referencing a Multiple Lines of Text Column With Append Text: Part 1
  36. Taming the Elusive "Calculated Column" - Referencing a Multiple Lines of Text Column With Append Text: Part 2
  37. Taming the Elusive "Calculated Column" - Referencing a Multiple Lines of Text Column With Append Text: Part 3
 

Please Join the Discussion

31 Responses to “Taming the Elusive “Calculated Column” – Referencing Multiple Lines of Text Column”
  1. Excellent article! This will prove very useful.

    Thanks, Dessie!

  2. Wow! Nice one, Dessie. Will put to use immediate. — Mark

  3. Vamshi says:

    Workarounds and rounds and rounds…. lol very creative.. this applies to many situations… Thanks for the wonderful tip and happy fooling guys:-)

  4. Christophe says:

    Wicked!

    Have you tried this with lookup columns?

  5. Charlie Epes says:

    Morning Dessie:
    This is so simple, it’s brilliant! Thanks for this treat.

    I have been trying the “tricking Sharepoint” method this morning and found the following:

    1.) Calc columns using multiple choices: it works well but would need to be cleaned up. Example: I selected two departments and the result is: ;#Dept 1;#Dept 2;#. Nice thing is I can Group by this calc column!

    2.) Calc column using Person or Group results in numbers… no number I can find in AD. Not sure what this all about.

    Thanks again. I look forward to more!

    Charlie Epes
    Buffalo

  6. Christophe and Charlie,

    On “Lookup” columns (both “People and Group” and standard lookups to other lists), the returned value in the calculated column will be the “ID” of the item it’s referencing. Each “ID” will be in the standard order of assignment based on when it was created – when you add a new item to a list, it gets the next available “ID” and will no longer be available, even if the item is deleted later (the database does not return it to the list of available ID’s). The same goes for people and groups. As each new person is added to the system, either by manual operation or AD import, they will be assigned ID’s based on the order in which they get entered into the system (this “ID” is not associated with AD, it’s a SharePoint value only).

    Because of this, unfortunately there isn’t a way to reference the actual “Value” for the item on lookups, only their associated ID’s (although there might be something you could do with the ID that would make it useful – I haven’t played with it too much).

    For a “Multi-select” column, the returned values in the column have a standard “semi-colon” delimiter. When referencing it using this technique, the delimiter becomes a “semi-colon hash”, or “;#”.

    You can use a simple “replace” function to parse this out and make it a “break” or other replacement value.

    Using same script, modify it as:

    var theTDs = document.getElementsByTagName("TD");
    var i=0;
    var TDContent = " ";
    while (i < theTDs.length)
    {
      try
      {
        TDContent = theTDs[i].innerText || theTDs[i].textContent;
        if (TDContent.indexOf(";#") == 0)
        {
          theTDs[i].innerHTML = TDContent.replace(/;#/,"").replace(/;#/g,"HTML BREAK TAG HERE");
        }
      }
      catch(err){}
      i=i+1;
    }

    Note – the “replace” action occurs twice in a single nested operation. The first is so we can remove the leading delimiter and replace it with nothing, the second performs a “global” replace on the remaining delimiters and replaces them with a “break”.

    (Since these comments like to strip out html tags, replace the text “HTML BREAK TAG HERE” in the above script with an html break tag when using)

    - Dessie

  7. Chris says:

    So I have done this to create excerpts for my front page. In doing so I have created a new column called description and replaced body for blog entries. I now have a problem, when I go to the posts view. Description is no longer soon on the page where the post is supposed to be. It says the title, the permalink, etc. I go into edit, and look at the view and description is there, however it is not being displayed. Any suggestions?

  8. ebrown says:

    Hey Dessie,
    Thanks for the article it will definitely be very useful to me. Also, thanks for the reference in your article (my 15 seconds of internet fame).

  9. Christophe says:

    Thanks Dessie.

    Note that you could have kept my initial code as is (except for the upper case that needs to become lower case).

  10. Dan Wilcox says:

    Great post, Dessie. I’ve done with the [Today] column and needed your little push to get me thinking broader. Thanks for taking the time to write this down!

  11. John Hanna says:

    A problem I’ve encountered in working with calculated columns surfaces when changing a formula in a list that is already populated with items.

    I get a response “SQL Server Error – The SQL Server may not be started”. The calculated-column formula appears to end up changed as wanted, and the revised calculated values appear when a list item is next edited.

    Just wondering if this may be creating some hidden problems, and if there is a way to avoid it.

  12. Diana Munger says:

    This doesn’t work in 2007. Once you delete or rename the column used in the formula – it gets replaced with #NAME.

    If I try to change the type on the column instead – you get an unsupported field type error trying to make the update.

    Looks like they have added in the xref – so you can’t get around them

  13. Rhonda says:

    Anyone have luck using this method for the ‘append-only comments’ field?

  14. Ben says:

    This works a treat – however when I try to add too much detail in the description field(about 30 lines of text) I get a #value in the calculated columns. Is there a limitation on how much text can be used in the initial description field and if so is there a work around?

    • Mike says:

      I run into this problem too. I am not sure if its associated with the number of characters or the number of lines. I’ll do some testing to see which it is- and if so, what the limit is before it returns a #value.

      • Mike says:

        Looks like the character count maxs out at around 1,800. HTML formatting (bold, hyperlinks, and the such add to this count as well so you need to watch this.

  15. Ben says:

    Also when I go to view the item it shows the calculated fields. How can I stop them being shown?

  16. Peter says:

    The articles on referencing multiple lines of text are brilliant. Do you have a similar workaround to reference an unlisted column? I need to make a copy of the “Body” column and call it “Description”.
    Thanks!

  17. Sietse says:

    Hi there,

    This is a great solution for many things.
    I just have a question:

    Via our PR department we get a daily newsletter. The wss3 incomingmail function automatically put’s it on a announcementlist.

    It’s a pretty big newsletter. Everything works fine, but i want to cut of the last 1420 characters of this newsletter. It consist a email signature and a “i want to sign of link”.

    I tried to use your solution, but i get an error in the calculated colum. I have also tried it with a small amount of text, and this works just fine.

    Is there a maximum of charactars you can cut of with =LINKS([Kolom1];LENGTE([Kolom1])-2) a formule like this?

  18. Vishal says:

    I dont understand how the last part (summary) worked.

    I tried doing the same but since the ‘DescriptionCalc’ column contains the tags, the truncated text which we get after applying the formula for ‘Summary’ column will contain the tags and with just 300 characters it wont contain the (closing) tags. This is because the formula LEFT([DescriptionCalc],300)&”…” uses the actual text in ‘DescriptionCalc’ and not the text from the view (rendered by the script).

    Hence the Summary column still contains the html tags. This is exactly what is happeneing with me.

    Please let me know in case I have missed something.

    ————————————————————–
    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:

    =LEFT([DescriptionCalc],300)&”…”
    ————————————————————–

  19. Arkadiy Birger says:

    it doesnt work for me in 2009, gives me #Name error

  20. Arkadiy Birger says:

    sorry, correction – 2007

  21. The “#Name” error is because the formula at one point, had a reference to a column in it that no longer exists. If the column is removed, the name of it in the formula will be replaced with the “#Name” value instead.

    The reason this occurs in this example is because we’re creating the “Description” column as a temporary column in order to build our formula and have it not error because it references a column it shouldn’t be able to, and in the process we’re deleting the column. The “Trick” comes to play when we create the column, set the formula, then go back and delete/recreate the column but make it a type that normally wouldn’t be able to be used in a formula.

    The actual error you’re seeing is most likely because after you set the formula and went back to delete the original version of the “Description” column, you didnt recreate it immediately after (or skipped recreating it altogether).

    It will work, but you have to make sure and follow the steps exactly:

    1. Create a new column called “Description” and leave all of its settings as default (leave it as a “Single line of text”).

    2. Create another column called “DescriptionCalc”, make it a “Calculated” type and enter in the following formula:
    =[Description]

    3. Immediately after creating the “DescriptionCalc” calculated column (while still in the “List Settings” page), delete the “Description” column.

    4. 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.

    Give a go again to see if it works this time.
    If it doesn’t for some reason, post back and I’ll see if I can help get it sorted out.

    - Dessie

  22. Arkadiy Birger says:

    Thank you it worked. Misread on the first try to not save the second column before deleting the first.

  23. Dolphinfan says:

    Hello,

    Maybe it’s just me or my bowser, but I am unable to see that script text that you say to copy. I see this:

    “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…”

    I’m guessing there is suppose to be some script right after “…for this list):”, but I just see a couple of blank lines. Am I missing something here?

    Thanks,
    GwK

  24. Chris says:

    Just in case someone runs into this BLANK column issue.

    What I would like to show in my column list item, named appropriately [!], is an image if the column item is not blank. I have a problem, because I don’t think the item is blank or null.

    What is the value of Description or DescriptionCalc when it is blank (apears blank)? I have tried ISBLANK[DescriptionCalc] and [DescriptionCalc] IS NULL but kept getting the same one result. So I reasoned it was not blank, but why?

    So I clicked Actions | Edit in Datasheet. Lo and behold I saw %div%%/div% in the column list item. I went back to my formula and made the necessary changes.

    % = either

    =IF([!]=”%div%%/div%”,”",”%DIV%
    %img title=’In Progress’ border=’0′ alt=’In Progress’ src=’/_layouts/images/ACA16.GIF’%%/DIV%”)

    Hope this helps someone, like me, an admin with little programming (hacker) skills.

    • Chris,
      You are correct, and I should have mentioned that instance at the time.

      The “div” stuff shows up when you had added something to the field, then later removed it…its one of the characteristics of a rich text field (multiple lines of text field).

      Good catch, and excellent job on the added chage to the formula!

      - Dessie

  25. Andrew says:

    I keep running into a problem with the find and replace function in SP2007.

    When I use a choice field with checkboxes and do a calculated field for a single line of text + choice field, I get the ;#(Selection;#.

    Can someone give me more detailed steps as to how I can get rid of these?

    I’m confused by the script conversion that Dessie referenced.

  26. Sachin Dubey says:

    Hey Thanks for the post Dessie. It helped me a lot. :D

    However when I try to implement the formula under ‘Summary’ field it doesn’t shows me number characters as per my requirement. In my case I want to show only first 100 characters from the ‘DescriptionCalc’ field but its showing only 37 chars.

    Don’t know whether issue with HTML format or something else. Please can anyone help me to get this resolved…

Trackbacks

Check out what others are saying about this post...
  1. [...] 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 [...]




Notify me of comments to this article:


Speak and you will be heard.

We check comments hourly.
If you want a pic to show with your comment, go get a gravatar!