EndUserSharePoint.com: Taming the Elusive “Calculated Column” – Text and Data (Part V)


Last time, we looked at 3 functions for formatting the case of text (working with uppercase and lowercase letters, as well as the "Proper" format of first letter capitalized only in a word).

In this article, we’re going to look at a few more tools we have in the "Text and Data" set of functions that can give you increased options in how to validate and transform data in your lists.

The five functions we’ll be looking at are:

*ASC - Converts "full-width" (double-wide) characters into their corresponding "half-wide" (single-byte) equivalents. Useful in language conversions where the typical set of "ASCII" characters are not enough to encompass the (possible) thousands of characters used in the language (also see "Double-Byte Character Sets in Windows"). (Example: full-width text "ファズ・ギター" converts to half-width "ファズ・ギター" equivalent - notice the size and spacing of the characters before and after conversion?)

CLEAN - Strips out all non-printable characters from text. Used to format text that may have originated from a separate program or data source that in its output includes low-level code (such as "" blocks) that can’t be printed.

EXACT - Used to compare two strings to see if they are identical. This function uses "case-sensitivity" to determine if the compared values are exactly the same and displays a simple "Yes" or "No" based on the comparison result.

REPT - Used to repeat a character (or characters) a number of times (useful in adding a series of repeated characters to an existing string.

T - Used to get the text of a given value and display it if (and only if) the value is text (will display nothing if the value is a non-text type - i.e. number or Boolean result).

*I wont be covering examples of the "ASC()" function (other than the example given in its definition) since its usage requires a system that can render the "Double-wide" characters (international settings), but suffice it to say that the function can be useful during language conversions to deal with the character sets used.

For the remaining 4 functions, let’s look at some examples of their usage:

What’s on your SharePoint bookshelf?

SharePoint Books

Notice all the copies of SharePoint for Project Management? You know what they’re for, right?

There’s one missing from here, though: The Shepherd’s Guide to SharePoint 2007. Sorry Rob, I couldn’t find a way to insert an eBook onto my shelf.

What else is everyone finding useful?

Update: Rob sent me his bookshelf. Looks like he’s heavily vested in Wrox. I especially like the Michael Kay XSLT 2.0. It reminded me of old times ‘way back’ in 2001 when I was at the first XSLT conference in Oxford, England.

This could turn out to be fun. Maybe we should do a challenge: Email me your bookshelf. The image should be 525 pixels wide, max. Let’s see what you’ve got.

Two Days Left to Get Your Free ‘SharePoint for Project Management’ Book

I met with Dux this morning to pickup the ‘SharePoint for Project Management‘ books that we will be giving away to the people with the most interesting problems and ideas on how to use SharePoint for project management.

If you haven’t placed your entry, consider doing it now. Deadline for entries is Friday night at mid-night, New York City (EST) time. Voting will begin next week and the problems with the highest number of votes receive an autographed copy of Dux’s book. And sure… I’m willing to pop for international postage.

The last time I looked, there were 34 entries and climbing.

Why are we doing this?

I’m trying to find the most common scenarios we can feature on the site. If we can analyze what people are asking for and then come up with generic solutions to those scenarios, everyone wins.

Let’s see what you’ve got… describe a problem you have with project management and let’s see if we can find a solution with Out-of-the-Box SharePoint.

EndUserSharePoint.com ScreenCast - Limit the item count in a web part display

Sometimes it’s the little things that get people excited.

I was giving an online workshop last night when the response screen started to go crazy. I couldn’t figure out what was the big deal until people responded that they never knew you could limit the number of items displayed in a web part.

Like I said, it’s the little things sometimes that will get you the best buy-in. Here’s a quick, two minute screencast to show you how simple and effective this technique can be when building your entrance page dashboards.

Please embed the screencast on your site if you think it will be of interest to your End Users.

Site Planning Worksheets: Over 800 Downloaded

Last week’s newsletter contained a download for a set of site planning worksheets. There were over 800 downloads! With that kind of response, I’m planning on an online workshop to walk people through the worksheet and get them setup with their site architecture. Leave me a quick note in the comments if that type of workshop is of interest to you.

For those who subscribed after the newsletter went out last week, this week’s newsletter has a link to all of the archived content from the previous newsletters, including the Quote of the Day web part and the Site Planning Worksheets.

The Weekly Newsletter goes out on Wednesday.

Notes of Wisdom when Talking with IT about SharePoint

Paul Grenier, moderator of Stump the Panel, has a nice give-and-take going with Brian about requesting SharePoint help from IT. Here are his little pearls of wisdom. Check out the entire exchange if this sounds familiar.

From Paul Grenier

First, a lot of IT departments are overworked and constantly responding to fires. They can’t possibly learn new technologies, like MOSS, to the depth needed for application development without dropping all maintenance issues (fires) and concentrating on MOSS. Frankly, it’s just really rare. Successful implementations understand there needs to be at least one dedicated MOSS administrator who has the time and skill to understand MOSS.

Since your IT dept probably doesn’t "know" MOSS, use terms that they do understand and frameworks that they are comfortable with.

  1. SPD=Front Page.
  2. MOSS is a configurable and customizable application.
  3. Customization can take the form of web code (HTML, javascript, XSLT), configuration (XML), or programming (C#, .NET).
  4. Web code (like modifying a web part’s XSL) uses the existing programming but changes the way data is rendered. Therefore, no security holes are created-the existing programming is secure.
  5. SharePoint alone can not change the data’s rendering pattern beyond simple table/row schemas but IT WAS DESIGNED FOR FLEXIBILITY.
  6. Nothing I described changes warranties, or support agreements.
  7. Once complete, the new web part can be placed into the web part gallery for use elsewhere-LIKE a sort of code repository (just be careful with the terminology, MOSS is not a good code repository).
  8. A "sandbox" subsite is best practice for testing local changes (like web parts). Try to get them to create one. No global settings are touched by anything I’ve described.
  9. Explain that security settings can be relaxed on a site-by-site basis to allow SPD access to the sandbox but not the larger site collection.

Submit yourself to any training they want to provide or recommend. I tell people I consult to only allow SPD access to the people who have gone through admin training. As Spider-Man would say, "With great power comes great responsibility." If your IT folks will not help you by doing these customizations, show your IT folks that you can be responsible.


Create a Master Calendar in SharePoint - Part 3 (online workshop announcement)

This is the third article in a 3 part series on creating a Master Calendar in SharePoint. The first article was a general overview of the problem. The second article was a detailed solution with a screencast. This article describes how you can participate in a live, hands-on workshop to implement the solution on your site.

If you need a Master Calendar solution without dropping the ‘big bucks’ for a third party web part, this workshop is for you.

Online Workshop

For those who need help implementing the Master Calendar solution shown in the screencast, I will be giving a 90 minute, live on line, hands-on workshop on November 11. The workshop walks you through, step-by-step, the creation of the calendar, the creation of content types, applying the content types to a master calendar and then exposing those content types as views in team subsites.

What is included in Creating a Master Calendar in SharePoint Workshop:

  • Live, hands-on instructions
  • LyteBox for exposing subsite calendar views
  • Step-by-step creation of content types
  • A SharePoint site in which to practice setting up the Master Calendar solution
  • Subsites for testing its functionality.
  • Documentation through a hierarchical diagram, Mind Map

By participating in this workshop, you will gain the tools and knowledge needed to implement a Master Calendar solution in your SharePoint site. Registration for this online workshop is limited to 20 participants.


Check out the entire agenda at the registration page for Create a Master Calendar in SharePoint or purchase tickets directly from the box office below.

Testimonials from previous EndUserSharePoint.com online workshop participants

"This is an excellent workshop. Mark answered every question and moderated with humor and patience through the usual communication hiccups with an audience that is international (Argentina, Ireland, all over the US)." — Betsy [SharePoint Dashboards, Online]

"This workshop was absolutely worth the time and money. I learned alot and will be able to easily transfer it to my daily work. I can’t wait to impress my coworkers." — Tammy [SharePoint Dashboards, Online]

"Don’t waste anymore time reading this to check out if it was worth it - stop reading and book your session!" Mick Brown [SharePoint Dashboards, Online]

"This was one of the best training sessions I have attended. It delivered a lot of powerful information yet was simple to follow and participate in. I can’t wait for the next class!" — Heidi [The Fundamentals of SharePoint Lists and Libraries, Online]

"What I got out of it the most was by far the use of Task Lists, that was exceptional ! I had no idea that they could be that useful. I’m planning on importihttps://nothingbutsharepoint.com/wp-content/themes/o4o206sb2smakbz7yz377r74961/files/2008/ng many of my spreadsheets into the site, including our business plan." — Erik [The Fundamentals of SharePoint Lists and Libraries, Online]

Create a Master Calendar in SharePoint - Part 2 (ScreenCast)

This article is Part 2 in a 3 Part series. The original article was written by Greg Maas to propose a solution to Master Calendars in SharePoint. This article will restate the problem in more specific terms and offer a detailed solution to the problem through a screencast.


Problem Statement

Create a SharePoint solution that will allow calendars in various locations in a SharePoint implementation to be aggregated into one, Master Calendar. The solution must use the Out-of-the-Box functionality provided by SharePoint, with no server programming.


A common problem with SharePoint Calendars is the inability to create a Master Calendar which pulls information from subcalendars into a central location within SharePoint. This is typically needed when groups or teams have individual calendars, but would like to aggregate them into one, master calendar at a company level. It is especially difficult because, by default, web parts in SharePoint do not see across site collections. In MOSS, the Content Query Web Part (CQWP) can access information across sites, but only exposes the information as a simple ‘title’ field. In addition, the CQWP visibility is limited to a single site collection. The CQWP is not available in WSS.


A solution to this problem is to turn the paradigm upside down: have a Master Calendar that ‘pushes’ information out to subcalendars by exposing views based upon content types. This idea was first brought to my attention by Greg Maas as a response to a question about master calendars. Creating a content type for each subcalendar makes it possible to dynamically generate views based upon those content types. The problem of seeing across sites/site collections is handled by using a LyteBox script embedded within a Content Editor Web Part (CEWP). The LyteBox opens a transparent window on top of a subsite, exposing the filtered Master Calendar. This solution will work in both WSS and MOSS. I have created a screencast to demonstrate how this can easily be accomplished. Embed the screencast on your site for easy access by your team or clients. It is especially useful when talking with upper management so that they can view the solution as part of your discussion. Use the ‘get code’ link on the menu bar beneath the viewing screen.

Create a Master Calendar in SharePoint Screencast

To be continued in Part 3 …