This question came into the queue and I thought it was pretty interesting.

The more we explore Single Sign On in SharePoint the more ambiguous it becomes - not sure what service it actually provides. We want our users to login and to have a list of links to other applications… they click, new window opens, and they are automatically authenticated and logged in to the other system.

The more we research this, the more we are hearing it’s not possible, it’s too much work or will cost an arm and a leg. Can you shed some light on Web SSO in SharePoint? Is this doable by the common man?

This is outside the boundries of what is usually covered here, but I really like the question. If someone can provide direction and resources, I think it will be of interest to all. —  Mark

 

I guess the title says it all, right? I received an email from Raj in Shanghai last night. For some reason, EndUserSharePoint.com can no longer be accessed in China. That’s odd… the only thing I have about China on the site is from when I was teaching there in January.

Hmmm… interesting. I wonder what’s going on.

This plea for help came in earlier this morning. I hear this about once a week, so I thought I’d see how other people are selling up the food chain when it comes to telling management "We can’t do it alone!"

Please point me to the best, most persuasive article or case statement for bringing in an experienced consultant to advise us on best practices for building out first real, non-beta MOSS environment. Our executives think we can do it on our own and we DISAGREE. There is so much at stake. Help me make the case to budget some $$ for a knowledgable mentor at this crucial time. Thanks!

Response from Mark Miller:

You are not alone in your frustration in convincing upper management that SharePoint is not a simple plug ‘n play. I get requests for consulting engagements every week, helping people get on their feet because there has been no internal person trained and given hands-on experience to implement a solution of any complexity. Microsoft has done too good a good job selling the idea of self maintaining environments.

I’ll start by pointing you to two articles, one by Bob Mixon and one by Robert Bogue. These won’t help you sell the idea of hiring a consultant as much as show management how complex the process becomes and that it is imperative to have some experienced help when planning the implementation of your site:

1. SharePoint 2007 - Site Collections and When to Use Them
2. Whitepaper: Increasing SharePoint Engagement

Bob Mixon is your main resource for Information Architecture, which is critical before you even start to build your site. Robert Bogue is the author of The SharePoint Shepherd’s Guide for End Users. He has many articles on his site that might help show management this is not simply a matter of opening up a site and letting people have at it.

Anyone else? Where do you turn for resources? Have you hired a knockout consultant that really put you on track and was able to get you up to speed in the process? How did you get over the initial hump when implementing your first SharePoint site? Inquiring minds want to know…

Overview

Last time, we modified the default "Contacts List" in order to see what other functionality we could introduce that could extend its default capabilities.

This is a continuation of the previous article "" in which we created a contacts list called "Employee Contacts" - this post will be using the same list again.

We began by addressing the need for an easier method of capturing the email address from "Internal" contacts (looking at this list as an "Employee Contacts" list rather than "All Contacts") by developing a calculated column that dynamically builds the email address based on the contact’s name.

This time, we’ll be looking at an approach we can take in order to "validate" the format of items entered, specifically a phone number (a question I’ve seen asked many times).

For this initial walkthrough, we’ll be looking at two (of the numerous) standard phone number formats:

555 123 4567 (with spaces)

555-123-4567 (with dashes)

Each of these is technically a "good" phone number, but depending on your localization or "data needs", you will generally have a preference (or requirement) to format all phone numbers in a specific fashion.

For simplicities sake, we’ll use the format of "555-123-4567" as our targeted phone number format.

Getting Started

So how do we begin this? We could use "InfoPath" or a custom "coded" solution to perform the validation since we’d have the power of "Regular Expressions" at our disposal, but within SharePoint (using only the built-in tools), we have to validate list items after-the-fact.

Because of this limitation, we’re going to have to manipulate the contents of an existing column, format it as we need, then display it in a new column (essentially, a "find-and-replace" operation in the manner in which the system will allow us).

The process we’ll be using isn’t specifically a "find-and-replace", but for arguments sake, we can look at it as such in order to have a clearer understanding of just what we’re trying to accomplish.

To begin, open up Excel and enter in the following in cell A1:

Text to enter: "555 666 7777"

In cell A1, right-click and set the format as "Text" - this will make sure and keep the contents of this cell in the format we want and not treated as numbers.

This simply gets us a phone number entered in with spaces as the separator.

Next we need to decide how to "find-and-replace" each space with a dash (-).

Since the available functions for our use do not include a "FindAndReplaceEachOccuranceISpecify()" function, we’re going to have to break the string apart (similar to a "substring" operation) in order to get at each space and replace it with a dash.

In cell C1, enter in the following formula:

=TRIM(LEFT(A1,FIND(" ",A1)-1))&"-"&TRIM(MID(A1,LEN(LEFT(A1,FIND(" ",A1))),LEN(RIGHT(A1,FIND(" ",A1)))))&"-"&TRIM(RIGHT(A1,FIND(" ",A1)+1))

Once the formula is entered in, you should see a properly formatted phone number appear in cell C1:

Dissecting the formula, we can see several different functions:

3 "TRIM" functions
2 "LEFT" functions
4 "FIND" functions
1 "MID" function
2 "LEN" functions
2 "RIGHT" functions
4 "&" (Concatenate) functions

As discussed in previous articles, we will often have to use several functions in combination with each other in order to duplicate some of the functionality available in other platforms (Excel, coded solutions, etc.).

To see how this formula works, we’ll start with what each function does:

TRIM(text) - Removes all spaces from text except for single spaces between words (this is an important first step since we want to eliminate as much as possible first so our text is "clean" - this will make the "find-and-replace" more efficient).

LEFT(text, num_chars) - gets the number of characters (from the left) you specify from a given string.

FIND(find_text,within_text,start_num) - Finds the starting position of text within a string (finding where in a string a specific character or series of characters begins), and is case-sensitive.

& - Concatenates strings together (as discussed last time).

MID(text,start_num,num_chars) - Gets a specific number of characters from a string based on a specified starting point and number of characters to return.

LEN(text) - returns the number of characters in a string.

RIGHT(text,num_chars) - gets the number of characters (from the right) you specify from a given string.

So, using the above definitions, the formula steps through as:

1. TRIM(remove all spaces from text)
2. LEFT(starting from the leftmost part of the text in Cell A1)
3. FIND(get the first occurrence of a space) - 1 (the minus 1 moves us one character to the left of the space so we don’t include the space in our selection)
4. Concatenate onto the above a dash (-), followed by:
5. TRIM(remove all spaces from the text)
6. MID(get the length of the text from the leftmost part of the string to the first space, get the length of the text from the rightmost part of the string (moving backwards) to the first space - essentially just getting the innermost part of the text from the first space to the last)
7. Concatenate onto the above a dash (-), followed by:
8. TRIM(removes all spaces from the text)
9. RIGHT(starting from the rightmost part of the text in Cell A1 (moving backwards))
10. FIND(get the first occurrence of a space) +1 (the plus 1 moves us one character to the right so we don’t include the space in our selection)

Analysis

As complicated as the above may appear, all we’re doing is separating the string into three parts:

First part (LEFT) - from the left until a space is encountered (then move one character to the left so as to not include the space in the selection).

Second part (MID) - middle section of the string (including the space at beginning and end)

Third part (RIGHT) - from the right (moving backwards) until a space is encountered (then move one character to the right so as to not include the space in the selection).

On each piece we separate, we run the "TRIM" function to remove any leading and/or trailing spaces (mainly affecting the "MID" section), and then merge each piece back together with a dash in between each piece (this serves as our custom version of the "find-and-replace" operation).

So, with the string "555 123 4567", we have:

First string = "555"
Second string = " 123 " (note the space before and after the "123")
Third string = "4567"

Trimming the spaces on each (although in this case the first and third strings don’t have any extra spaces at their respective beginning or ending, it’s a good idea to still perform the operation anyways since someone may inadvertently add in an extra space in the list itself) will now make each string "clean" and prepped to be concatenated back together. Concatenating them (after "trimming") will now produce our finalized end result string of:

"555-123-4567"

To get this working on our "Employee Contacts" list in SharePoint, we need to create our calculated column and modify the formula accordingly to use the phone number field we want to format.

In SharePoint, go to the contacts list and create a new column called "Home Phone Number". Make the new column a "Calculated" type, and paste in the formula from excel making the following changes:

We’re going to be calculating the phone number entered in the "Home Phone" field, but using our new field for the view.

Formula in Excel:

=TRIM(LEFT(A1,FIND(" ",A1)-1))&"-"&TRIM(MID(A1,LEN(LEFT(A1,FIND(" ",A1))),LEN(RIGHT(A1,FIND(" ",A1)))))&"-"&TRIM(RIGHT(A1,FIND(" ",A1)+1))

Replace each occurrence of "A1" with "[Home Phone]", making sure that you do use the brackets [] around the name of the column.

Formula in SharePoint:

=TRIM(LEFT([Home Phone],FIND(" ",[Home Phone])-1))&"-"&TRIM(MID([Home Phone],LEN(LEFT([Home Phone],FIND(" ",[Home Phone]))),LEN(RIGHT([Home Phone],FIND(" ",[Home Phone])))))&"-"&TRIM(RIGHT([Home Phone],FIND(" ",[Home Phone])+1))

Hit "OK" to save the column, and then create a new item on the list:

(All you need for this test is to enter in the name of the contact and their "Home Phone" number in the format of "555 123 4567" - make sure and use spaces in the number)

Once saved, you should see our calculated column displaying the phone number in the proper format (Note - I’ve stripped down the view for readability to only have the contact’s name and phone numbers visible):

All we have to do now is modify our view to use our column "Home Phone Number" instead of the default "Home Phone" for display (modify the view and uncheck the column "Home Phone").

Editing the item will still be the same in that you’ll actually modify the "Home Phone" field, but the new column being displayed in the view will add more consistency in how items are displayed.

Summary

What we’ve done here is take in account for only one of several different phone formats commonly used, and as a result will only work if numbers are entered in using the "space" in between each set of numbers ("555 123 4567"), so this is far from a complete solution as it currently stands (we’ll remedy this in upcoming posts on this topic).

The hope is that (although incomplete in it’s current state) the process I’ve discussed here will shed some light on how to begin tackling problems such as these using nothing more than out-of-the-box functionality…and a little creativity.

Next time, I’ll be continuing on with this same topic as we introduce a second phone format (dotted separation "555.123.4567") and how to format it using a different approach that more resembles a traditional "Find-and-Replace".

Till next time…

- Dessie

Suggestions for future posts on calculated columns are always welcome, and in fact are encouraged.

Some of the best scenarios to illustrate are the “real-world” problems that we each face day to day, so if you have an example, an idea you want to explore, or a “Can this be done with a Calculated Column?” question that I can use as the topic of a future post, please submit it as a comment below and I’ll see what I can do to work up a post covering it.

Dessie Lunsford
Points-of-Sharing

One of the things covered in last week’s online workshop was the ability to dynamically filter lists and libraries using the column headers of the library. We then saved the URL string to use in emails or in lists to recreate the filtering.

Paul Galvin came up with a nice assist: "Why don’t you save it in your ‘Favorites’ list and have it available all the time?" Since dynamic sorting and filtering is saved as a GET string in the URL, this was perfect.

Take it a step farther: create a folder in your favorites and put all your personalized list filtering in the folder. Simple, easy and very convenient.

Thanks Paul. A nice little tip to add to Tricks and Traps.

 

Yesterday’s online Fundamentals of Lists and Libraries workshop  went smoothly except for a couple of audio glitches with the GoToWebinar software.

We worked in a hands-on environment, allowing everyone to participate by contributing to various lists and libraries, dynamic filtering through the browser using multiple column headers, and creating personal views against the data. We concluded each section with brief walk-throughs of case studies, showing how to use libraries and lists as a solution to common business problems.

The attendees have access to the slide decks in the Presentation Archive on the workshop site. I am in the process of encoding the recorded presentation into modules for future reference when they need a refresher.

One of the requests at the the end of the workshop was to have more information on creating dashboards. I’m going to do an article next week for the EndUserSharePoint.com Weekly Newsletter and extend that with another workshop for hands-on creation of dashboards.

This post is an area for attendees to give feedback on how the session went for them. We are planning on future workshops to include:

• Workflow with SharePoint Designer
• Configuring Search in SharePoint
• Security and Permissions for Site Managers
• Site Planning: Creating a managable content structure
• Web Parts: What is available and how to use them

I look forward to seeing the feedback from yesterday’s session and hearing of any other subjects that might make an interesting workshop.  — Mark

 

Author: Chris Quick
WSS Development
Moderator: Stump the Panel

Most End Users of SharePoint do not have the time to keep up with the latest buzz in the SharePoint world. This is because most of their time is spent getting their daily tasks done. Only the true SharePoint "junkie" will spend time outside of the job learning about the products they use on a daily basis. Let’s face it, most of us are task oriented and have hobbies that are more interesting than SharePoint (why some people don’t share the same passion for SharePoint that I do will never cease to amaze me).

However, there are times that things come along that will help to revolutionize your work experience and End Users can actually have the honor of presenting it to their IT staff. The latest update for SharePoint is one such opportunity.

Within the last two months, I have been reviewing a new update to SharePoint that extends some of the capabilities of SharePoint search. This new update introduces something called Federated Search. When I first read Paul Galvin’s post about this update and became exposed to the idea of Federated Search, I realized this will significantly change the position of SharePoint in Enterprise Search.

Imagine searching your intranet site for documents about your new product line of roller-skates. Not only are you interested in information about the roller-skates produced by your company, you want to know about the competition. So you begin the arduous task of searching the intranet for information about the skates you sell.

You find a document that lists price points for those skates. Out of curiosity, you decide to search for other competitors producing roller-skates. You open a new browser window and begin searching your other competitors from other search engines on the web. Throughout the process, you may even think, "Geez, it would be great if I could get all of this information from one place".

This is where Federated Search introduced in the new Infrastructure Update for SharePoint will become an invaluable tool to your organization. This addition to search will allow you to pull together search results from the internet with resources produced by teams within your organization into a single location!

Over the next few weeks, I’ll be investigating Federated Search and how it impacts you, the End User.

Chris Quick
WSS Development
Moderator: Stump the Panel

Author: Paul Galvin, Microsoft MVP - SharePoint
Web site: Paul Galvin’s SharePoint Space

Many business workflow scenarios call for us to send an email at different parts of the process.

Like many things with SharePoint Designer workflow, this is relatively easy to accomplish, but is sometimes harder than it would seem. In this article, I address two tricky scenarios and outline a third which I will address in a future article.

 

The Scenario

To work through these two simple issues, let’s work with a specific business scenario in mind. We are developing a workflow solution to manage the new hire process. Before a new hire walks through the door on their start date, many different business steps need to take place. For example:

• Assign an office location.
• Set up in payroll.
• Get a security badge.
• Set up network credentials and systems access.

The workflow process is fairly straight forward:

• Create an entry in a custom list where we specify:
  • Name
  • Start date
  • Direct manager
  • Comment
  • A set of status fields, including:
    • Was office location assigned?
    • Was payroll set up?
    • Was a security badge created and ready for issue?
    • Have network credentials been set up?
• When the workflow begins, send an email to several department managers who are responsible for assigning an office location, setting up payroll, etc.

I’m not going to discuss the status fields very much in this article. I include them to plant the idea that we can add these kinds of status columns to a list and then have the workflow update them. This allows us to see the current status of the workflow by simply viewing the list and looking at those columns.”

Based on that, we’ll end up with a “New Hire” form like this:

This example is a bit contrived. We would probably just assign a task to those individuals and let the system send them an email. However, let’s assume that the default task assignment email does not meet our email requirements in this case.

When we send these emails, we want them to follow this convention:

Subject:
New hire [new hire name] begins work on [start date].
Body:
Greetings, [new hire], a new hire, begins work on [start date].
Additional comment: [comment]

Note that if [comment] is blank, then we want to show “No special comment.”

This required email forces us to deal with the two issues I want to clear up in this article:

1. How do we create a dynamic email subject?
2. How do we test for an empty comment and create a dynamic message body?

I am in the process of sending out the pre-workshop logins and material for The Fundamentals of SharePoint Libraries and Lists, scheduled online for this Thursday at 1:00pm EST.

If you’ve been waiting until the last minute to register, you’ve done a good job… now get to it. Time is running out.

I just received an email from Guy Kawasaki announcing that EndUserSharePoint.com has been included in the SharePoint.AllTops.com page. My first thought was "Yeah, so what?", but when I went and looked at the page, I was impressed.

Yes, I know it’s a marketing scheme to help him generate more links, but when you look at the feeds he is pointing to as resources, there are some pretty hefty names there: Andrew Connel (MVP MOSS), Microsoft SharePoint Team Blog, Bob Mixon (SharePoint MVP), SharePoint Joel’s SharePoint Land, Microsoft SharePoint Designer Team Blog, Not Fit for Print - Robert Bogue (MVP), Heather Solomon (MVP) and more.

This isn’t just a blind scraping of links. It looks like someone really took some time to put this thing together. I think this is a valuable resource when trying to consolidate SharePoint information from the hundreds of sites available.

Am I proud of it? About the same way I was when I was included in the "Who’s Who in American Music" back in the 80’s. It’s a nice ego boost and a good resource. OK, I’ll take it.

Thanks Guy.
Mark