Category Archives: The HTML calculated column

HTML Calculated Column + Client-Side Rendering

Posted on by

Five years after the first release, the HTML Calculated Column remains the most popular topic on this blog.

The original page has been visited more than 200,000 times. It is definitely outdated, and in recent years I have pushed several new variations of this technique. The most popular is the color coding solution posted in the SharePoint User Toolkit, backed by this tutorial.

The most frequent issue reported by users has been the upgrade from SharePoint 2007 to SharePoint 2010. This is actually all taken care of in the above links… but you need to read the fine print.

I plan to rewrite the instructions, especially as in the meantime Microsoft has pushed another version of SharePoint, and the SP 2010 update… doesn’t work with SP 2013! Well, there’s actually a simple fix for SP 2013, and “Panoone” posted it as a comment a couple days ago (@Panoone: thanks again! And let’s get in touch to discuss this further).

But that’s not all. SP 2013 brings a bunch of new client side technologies, and one of them works very well in our case: Client-Side Rendering.

What is Client-Side Rendering?

JSLinkEditYou can use Client-Side Rendering (CSR) in SharePoint to manipulate the rendering process of list views. Does this sound familiar? That’s exactly what my HTML Calculated Column has been doing for years! Except that now it is an official component integrated with list views. When you edit a Web Part, the very last option is the JS Link placeholder (see screenshot).

It will certainly take several weeks before I find time to update the SharePoint User Toolkit, so for those of you who are already familiar with both CSR and the HTML Calculated Column, let me share the code for SP 2013.

In the code, Calculated is the name of the calculated column.

var ccContext = {
  Templates: {
    Fields: {
      "Calculated": {"View": "<#=STSHtmlDecode(ctx.CurrentItem.Calculated)#>"}
    }
  }
};
SPClientTemplates.TemplateManager.RegisterTemplateOverrides(ccContext);

So easy, just 200 characters! STSHtmlDecode() is a JavaScript function provided by SharePoint that allows the conversion of the html string.

Of course, you could avoid using a calculated column, and build the html directly in the JS Link file. But then each rendering would require a different file. The beauty of the HTML Calculated Column is that one single JS Link file can support all the views in your site.

A warning!

Some time ago, I wrote about security risks when you use the HTML Calculated Column. As far as I can tell, the same warning applies to Client-Side Rendering. Handle with care!

CSR references

When Microsoft released SharePoint 2013, the documentation on Client-Side Rendering was quite poor. Fortunately SharePoint bloggers stepped in and you can find some nice posts to get familiar with CSR. Wes Preston’s blog is an excellent start.

So is the old way dead in SharePoint 2013?

Not quite yet! Not every view accepts Client-Side Rendering, and for example you’ll still find my tutorial helpful for calendar views.

Trick or Treat? Text to html, the wicked no-code way (Part II)

Posted on by

At the end of the previous post, we had this aha moment when we realized that rendering a field as rich text might not be that difficult, after all.

I’m back, after taking the time to set up a brand new demo to confirm our assumptions:

Demo

 

It works! As you guessed, just using a SharePoint Designer workflow to copy the content of the calculated column to a rich text field did the trick. For the record, here are the detailed steps:

  1. Create a list (I used the Tasks template here).
  2. Create a calculated column called cc, with the html formula. For the above demo I used calculated gradients (just did a copy/paste of the formula in the post).
  3. Create a column of type “Multiple lines of text” called HTMLcc, and specify that the type of text is “Enhanced rich text”.
  4. Create a one step workflow that copies the cc column to the HTMLcc column, every time an item is created or modified (screenshot below)
  5. We’re done!

 

The benefit of this approach is that we now have a clean field that renders our graphic – no ugly code around. I left the cc field visible for the purpose of the demo, but in production I would naturally make it a hidden field.

Although this looks great and easy, there are a couple gotchas to be aware of, if you decide to use this trick.

First, it is rather heavy: two fields and one workflow! And of course it won’t work for you if SharePoint Designer is not allowed in your environment (or you’ll need another way to copy cc to HTMLcc).

Second, it won’t help in views such as calendars, as these views don’t render rich text fields.

Third, as with any workflow action there’s a latency. The HTMLcc field will not refresh as quickly as other fields.

One last, important point: SharePoint is going to “sanitize” the html you copy to the HTMLcc field, and might decide to remove some of the content. This can actually be considered a good thing, as it addresses a security concern I reported in a previous post. On the other hand, it will block some script-based enhancements, like hover effects or countdowns.

Enjoy, and see you for next year’s trick: how to sort a list by month name. Happy Halloween!

Trick or Treat? Text to html, the wicked no-code way

Posted on by

As I am actively working on a new training program called SPELL, it recently occurred to me that I should make Halloween a special day. So let’s start the tradition, the SPELL program annual trick or treat!

I have described many times my HTML Calculated Column technique on this blog. The idea is to use a calculated column to build html content. You have certainly already tried them out, for example for color coded calendars, progress bars, or KPIs.

The catch is that SharePoint won’t naturally interpret the string as html, so extra work is needed to do the conversion – usually done using JavaScript or xslt.

Now, rendering an html string as actual html is something any rich text editor can do, why is it so complicated with SharePoint? Oh yes, here is the thing: the output of calculated columns is just plain text. Ah, if only I could change it to rich text! Or find a way to transfer the content of my calculated column to a field that understands rich text!

But…Wait…

[To be continued]

Security concerns with the HTML Calculated Column

Posted on by

The HTML Calculated Column is certainly my most popular technique. It is mainly used to color code calendars, but its scope is much larger than that. The technique is especially useful when you… actually do calculations, for example in these progress bars with color gradients. You can see some other examples in this live demo, and in many other live demos and posts in this blog. Any html tag could be added using this technique.

For the record, the original post is outdated (and so are the hundreds of articles that copied it). You’ll need to read the more recent ones for updates on SP 2007 and SP 2010.

Did I just say any html tag? mmm… so how about script tags? Does it mean that a user could push a malicious script to your computer? Well, the short answer is yes. This is something I have already discussed in the past, in private or in forums, but I never posted a detailed explanation on this blog – until today.

The question was actually raised in a recent project I worked on. I accidentally stumbled upon an experiment that the IT team ran behind my back (well, to be fair, maybe they just didn’t know I was the author of the code). Let me tell you the story.

For the sake of simplicity, I’ll use these short snippets instead of the actual code:

  • our malicious script: alert(“Boo!”)
    The script simply opens a pop-up that says Boo!, be prepared to be scared…
  • our calculated column: =”<div style=’font-weight:bold;’>”&Title&”</div>”
    This will render the Title in bold. It is actually the example used in the original post from September 2008.

For example if Title is Chandler, the value of the calculated column will be
“<div style=’font-weight:bold;’>Chandler</div>”
and the page will render it as Chandler.

Now, here is what the IT team tested: in the Title column, they entered:
<script>alert(“Boo!”);</script>

In this case, the calculated column returns:
“<div style=’font-weight:bold;’><script>alert(“Boo!”);</script></div>”

What happens when you use my technique? You might expect to get a “Boo!” alert, but in practice… nothing! The code simply doesn’t execute. This is due to the technique I use in the HTML Calculated Column: script tags are added (just like other tags), but do not run.

I guess IT was satisfied with the test, as they agreed to leave my code on the page.

What they overlooked is that there are actually other ways than script tags to add code to the page. Imagine for example that you enter the following string in the Title field:
<img src=”/_layouts/images/blank.gif” onload=”alert(‘Boo!’)” />

This time, when you use my technique the above code loads the image (blank.gif), and once it’s loaded it triggers the code in the onload event. Wicked!

As I said, there’s nothing new here. I even have a blog post from 2009 that explains it:
https://blog.pathtosharepoint.com/2009/02/26/using-calculated-columns-to-write-scripts/

So is there really a security risk here? In theory, yes. In practice, only people who have contributor access will be able to inject code, so in collaborative environments the risk should be very low. Also, you’ll be very limited in the amount of code you can put (a text column like Title only accepts up to 255 characters). However, I would advise against using the HTML Calculated Column in open environments, for example if anonymous users have write access or if your list is mail enabled (and by the way in such cases you’ll have other concerns, not just with the HTML Calculated Column).

In the next few weeks, I’ll publish two techniques that avoid the security risk described in this post.

A School Site to Track Schedules, Assignments and Grades (Part I)

Posted on by

Guest Author: Trudy Hutzler

Trudy currently works as a Senior Technical Architect for AT&T Hosting and Application Management, where she provides Windows Server and SharePoint support and administration.  She is also a contributing author for the new Mastering SharePoint Foundation 2010 book.

As a SharePoint Administrator I often get asked about adding visualizations and such to SharePoint to add that little something extra to the out of the box SharePoint experience.  As an avid follower of Path to SharePoint I find many of the solutions Christophe has provided are a great way to enhance the users SharePoint experience without requiring me, as the Administrator, to maintain a lot of code or customizations on the server.  As good as the solutions are on their own, when you begin to combine them and layer them you can create something that is user friendly and visually appealing.

To demonstrate this I am going to share with you a School Site that I created for my oldest daughter who attends school online.  Now obviously not everyone will have a need for a School Site to track classes, homework assignments, and grades, but if you replace classes with departments or projects, homework assignments with milestones or requirements, and grades with assessments or performance ratings, you can begin to see where this concept can be applied to many more business related activities.

To make this easier to follow I am going to break this down into several posts, the first few will be an overview of the site and what it looks like, and then later posts will cover how I created each part and tied them together.  When it is all said and done I hope to provide readers with a copy of my site, and all the formulas I used.  So let me start by giving you the nickel tour.

BACKGROUND

The high schools in our area are not all that great, so my oldest daughter attends a state chartered online school.  We love the way the classes themselves are conducted, but the homework and grade interface is difficult to navigate, confusing, and it is very easy to miss an important assignment simply because it is hidden deep in layers of folders.  After an entire year of missing and late assignments and a constant struggle to navigate the interface, my daughter came to me last summer and asked me to help her create a SharePoint site on my development farm I have at home that she could use instead.

What you are about to see is the current version of that site.  My daughter would like me to note that all names, classes, and grades have been changed to protect the innocent, and that no actual grades have been used in creating this demo.

This site is created on the Team Site template, and I am using SharePoint Foundation 2010, however most of the functionality will work on SharePoint 2007, with a few exceptions like calendar overlay which is only available with SharePoint 2010.

As we go through the series I will refer you back to the blog entries I used to create each feature, and make all of the formulas available.  I will also be adding them to a special document library I have added to the demo site which, with Christophe’s help, I hope to make a copy of the site available to the readers for their use.

ON THE HOME PAGE

The site itself has three main functions; track classes, track homework assignments, and track grades. In this post I will be walking through the parts of the site that we use to track classes.

For tracking classes, my daughter needed to know the days and times her classes were scheduled for and what each teachers fax number was for submitting work she was unable to submit in the drop boxes provided by the school.  She also needed to be able to keep control of her schedule by scheduling in time to do her assignments. As parents we needed to know when she was supposed to be attending her online classes, the name of her teachers and how to contact them if we had questions. We also needed to know when the grade periods started and ended, and when there was no school.  Most of all we needed a way for busy parents, as well as the student, to be able to track it all at a glance.  This is what we came up with.


 
On the home page of the school site we used Easy Tabs to create an easy way to organize and navigate through the information we wanted “at our finger tips”. Some of the views are for Mom and Dad’s information, others are for our daughter’s use, but it is all there in one spot.

This first tab “Class Calendar”, as seen above, is a view of her class schedule, but to make it even more informative we over laid her schedule and assignment calendar views to give it a more complete view.

Next we will look at the tabs pertaining to classes and schedules.  The Schedule tab, This Week tab, and the Today’s Schedule tab show a calendar view of the scheduled classes and events for the month, the week, or for the day.  This helps other family members know when she is busy attending her web classes and can’t be disturbed.  This actually comes in very handy on days when my other children have no school, like snow days, and they know when they need to be quiet and leave their Sister alone, however you can use something like this to tack meetings, or deadlines, schedule events, etc.

For this calendar we have added color coding so we can easily differentiate between scheduled classes, work time, and holidays.

The last tab is Print Preview click on one of the other tabs that you would like a printed version for, then click on the Print Preview tab and it will create a printer friendly view of that tab, click on your browsers print button to print.  Once you have printed out your page if you look at the Print Preview tab it now reads Back to Page.  Click on this tab and you will be returned to your regular view.

For us, this gives my daughter a printed copy to keep with her and refer to throughout the day, or a schedule to post of the refrigerator, or just a list of assignments to follow up on at the end of the day, but you can also use this feature to print out pages to add to a report or share with others who may not have access to your site.

LIBRARIES

We are using the Shared Documents library to keep copies of any downloaded forms, assignments, test, or home work she has written up and scanned to email to the teacher or upload to the Drop Box in the class web site.  This way if something doesn’t reach the teacher the work is handy and can be easily resubmitted.
She also has to keep an activity diary for PE, she didn’t want those getting mixed in with other assignments so we made a separate library for it.
Finally for this demonstration I added a third document library to hold text file copies of all the formulas and calculations used in creating this site.
                                             

 

LISTS

It is in the lists that the real action starts.  It is the Class Calendar, Assignments, Schedule, Contacts and Grades that are the real work horses for this site.  Since this post is all about organizing and tracking classes we will look at the Class Calendar and Contacts lists.

The Class Calendar is really just the Team Site Calendar renamed.  We added all her regularly scheduled classes in as events, and later over laid the Assignment Calendar, which is how my Daughter schedules in time to actually work on her assignments, to increase its impact and usefulness.


 
Next we needed to track information about the classes, teachers and contact information so we created a contact list.  We started with a basic contact list then removed fields that weren’t needed and were just cluttering up the place, and added a few custom fields till we got what we needed.


 
We kept the usual contact fields like First and Last Name, Phone Numbers, and such.  Then we edited the Category from your typical Work, Meeting, Phone Call, etc. to something more school oriented like Teacher, Advisor, School Staff, Student and Other.  Finally we added another Choice field and added in the name of her classes.

At this point my Daughter decided she needed a place to keep track of all her other dates and to schedule time to actual work on each assignment.  So we created another calendar called Schedule which, since it would track multiple types of events, we made into a color coded calendar.


 
This calendar color codes the entries by category.  When you create a calendar entry, along with all the usual fields like title, location, start and end time, all day event or reoccurring event check boxes you also have a category choice field.  The category you choose will determine the color of the calendar entry.


 
So now you can see how using calendar overlays, and color coded calendars, in conjunction with Easy Tabs can begin to create a very useful interface.  This same concept can easily be adapted to tracking projects, deadlines, meetings and other deliverables.  And you don’t have to be a code ninja to get the job done.

In my next post I will get into the real nitty gritty of what makes the School Site work when I show you how we are tracking, and scheduling homework and assignments.

Coming soon on Path to SharePoint

Posted on by

No post in more than a month, this had never happened since I started this blog in 2008! Yes, starting my new company, traveling across the US, and the tax return have taken their toll…

Mind you, the blog still remained very active, with more than a hundred comments posted in the past month. But now it’s about time I add new content. So what’s coming next?

My next tutorial will be about countdowns/countups. I already published a live demo in February. If you can’t wait, head out for the SharePoint User Toolkit, a first countdown-countup page is already posted there! (and remember to send me some feedback)

I am also working on the Easy Tabs v 5.1. One of the most common request is to have more branding options (colors, hover effects, etc.). I already made a leap forward between v4 and v5.0, and I hope to bring some more improvements soon. I already published an example of custom style (including a live demo), but the release of Internet Explorer 9 changes the game (for example IE 9 natively supports rounded corners, no need for workarounds).

In March, I spoke at the San Diego SharePoint User Group. After I clean up my slides, I’ll post them, along with some interesting Q&As from the session.

I also have a couple projects with my new company, User Managed Solutions LLC. In particular I am working on a new online training offer for this Fall – more details coming in May!

Last, but not least, I am honored to welcome guest blogger Trudy Hutzler for a series on creating a “school site”. Trudy is a Senior Technical Architect for AT&T Hosting and Application Management, and a contributing author for the Mastering SharePoint Foundation 2010 book. As this blog is user focused, she is not going to talk about her experience on SharePoint 2010 migrations, but about a custom site she built for her daughter who attends school online. Stay tuned!

Live demo: countdown/countup

Posted on by

Countdowns, and generally speaking comparisons with Today’s date,  have been a recurring theme on my blog.

SharePoint 2010 hasn’t brought much improvement to the “Today” issue, so the workarounds published on my blog in 2008 remain a good reference, either the one I wrote for Data View Web Parts or Alexander Bautz’ follow up article for list views.

A year ago, I blogged about two other solutions, one relying on jQuery, the other on Flash and ClockLink.com (the demos are not active anymore).

Today I am showing two new examples that are much simpler than the previous ones. The live demo is here:
http://sp2010.pathtosharepoint.com/Portfolio/Pages/Countdown.aspx

Why much simpler? Because the only thing you need in the page is my Text to HTML script – the exact same that is used for color coding calendars. The calculated column will take care of all the rest (for example the formula to calculate the difference between target date and Today, or the color selection).

Note that there’s a key difference between the two examples: the “Countdown” column relies on the local time of your computer, while the “TimeAndDate” column pulls today’s time from TimeAndDate.com. In the past, I have already highlighted this important choice (for example, don’t use the local computer time if you’re building an auctions site!).

The drawback of this new approach is redundancy – the current date is calculated for each item. So I would recommend to only use it for views with few items.

You’ll notice that the column filters also render the HTML (cf. above screenshot). That’s because the page uses a custom version of my Text to HTML. It is still work in progress, so please don’t copy this script!

My solutions spotted in the blogosphere

Posted on by

My solutions for SharePoint end users are regularly relayed on forums, twitter and SharePoint blogs. The past two months have been particularly active, and I have identified 15 posts that mention Path to SharePoint. I am listing them below, some of them are definitely worth a look.

The SharePoint User Toolkit

The SharePoint User Toolkit: a first step toward advanced SharePoint customization
Get the Point is the official blog of the Microsoft SharePoint End-User Content Team. This article written by me describes the main solutions currently available in the Toolkit, and what’ s special about each. Big thanks to Renée Smith for giving me this opportunity to spread the word!

http://sbelskiy.spaces.live.com/blog/cns!BB6AC8CA5EB9828E!4455.entry?wa=wsignin1.0&sa=88791362
In Russian, a review of the solutions available in the Toolkit.

The Easy Tabs

The popular Easy Tabs have seen a new release compatible with SP 2010 this Summer.

Easy Tabs Rock – v5 Beta is looking really good
A very well written post, by SharePoint MVP Sean Wallbridge, with lots of screenshots – definitely worth checking out! Note that v5 beta has since become the official v5.

Use Easy Tabs 5.0 to consolidate lists, Web Parts, and calendars
Obviously Renée Smith thinks that Sean’s explanations are better than mine. Oh well, she is right!

SharePoint filtering with web part connections
The Easy Tabs in context: Kerri Abraham shows how to combine Web Part connections and Easy Tabs to make content more accessible. A very useful post!

Using EasyTabs with Filtered DVWPs to Make Data Manageable
Another example from EndUserSharePoint.com, by Jim Bob Howard, where filtering and Easy Tabs combine for better readability. Note that the post is actually part of a long series on the Data View Web Part.

The HTML Calculated Column

KPI’s without SharePoint Enterprise (on a budget)
A review of KPI options for SharePoint if you don’t have MOSS Enterprise.

How to create a Gantt chart in SharePoint
Two years ago, I showed how to build simple Gantt charts using the HTML Calculated Column. Linda Chapman describes a more complete solution in her post.
This week, Ben Schlaepfer left a comment on my blog about another solution using the same method (Ben, we are looking forward to your article!).

HTML in een berekende kolom
The HTML Calculated Column explained in Dutch by Gene Vangampelaere.

http://vipnetty.wordpress.com/2010/09/03/sharepoint_list_color/
Another translation, this time in Thai (?).

Item id in display and edit form

Showing the records id on the view and edit forms
A jQuery script, by Ryan Wheeler, who only discovered afterwards the JavaScript version I wrote 18 months ago.

Other

SharePoint Kaffeetasse
SharePoint MVP Michael Greth regularly mentions my solutions in his daily SharePoint Kaffeetasse. Last time he did was… today, about my Print Preview bookmarklet.

SharePoint 2010: Recopilatorio de enlaces interesantes
Juan Carlos González Martín regularly publishes compilations of interesting SharePoint links.

http://idubbs.com/blog/?p=241
Gathered by Wes Preston, a list of useful SharePoint links, including the SharePoint User Toolkit.

http://www.janecerdenola.com/?p=86
A mention of my solutions, as workaround when you don’t have server access.