A Table of Contents for Your Site

Continuing on the theme from the last two weeks, I am going to take a look at another web part that is infrequently used, at least on the web sites that I work with. We use the Site Navigation feature out of the box for navigating between pages on a site and even to add custom links to pages and sites outside of our site. We also find the Site Navigation feature lets us build pseudo-hierarchies by adding header entries and links that serve as a fly-out to the header. But there is another way to provide navigation around a site. That is with the Table of Contents Web Part.

The Table of Contents web part lets me display all of the pages and sites that branch off the current site. The key advantage to this web part is that I can display up to three site levels (the current level and two nested sub-site levels) on a single page. It also gives me some built-in formatting capabilities to change the way the Table of Contents is rendered on the page ranging from vertical orientation to a horizontal orientation and several hybrid types in between. So let’s take a look at this web part.

Like other SharePoint web parts, I have to add the Table of Contents web part to a page on my site. This may mean that I have to first create the page where I want to place the Table of Contents. Then I can edit the page and going to the Insert ribbon and select the Web Part button from the Web Parts group. In SharePoint 2010, the Table of Contents web part can be found in the Default category as shown in the figure below. However, in SharePoint 2013, it was moved to the Content Rollup category which actually makes some sense.

After select the Table of Contents web part, click the Add button on the bottom right of the dialog. After adding the web part to the page, SharePoint provides a default view of the site. This view displays pages and sub-sites off the current site as shown below or it may only display sub-sites and their pages. In either case, I can easily modify the properties to control where the Table of Contents begins.

As with all web parts, I can edit the properties by hovering over the header to display the down-pointing caret on the right side of the header. This is the web part menu. I click on it to open the menu and select: Edit Web Part. The properties panel for the web part appears to the right of the page. You may need to scroll to the right and up the page to find it depending on the size of your page. The most interesting properties and the ones I will focus on appear in the top section of the panel. There are three sections: Content, Presentation, and Organization as shown in the following figure. Each has a box with a plus sign in it in. To open each section to view and modify its properties, click on the plus sign in the box to the left of the section name.

The following figure shows the properties found in the Content section. The first property identifies the site where the Table of Contents will begin. Note that you can either enter the URL or you can select it by clicking on the Browse button.

The second property identifies how many sub-site levels you want the Table of Contents to include. The maximum is three levels. There is also a check box where you can identify whether the site where you are beginning the Table of Contents should be included or whether to only include sub-sites under the current site and their pages.

Finally, this section has three check boxes where you can select whether you want to show pages (or just the sub-sites in the Table of Contents. You can also choose whether you want to display hidden pages or hidden sites. I suppose if you need to see everything in a site, you might check these two boxes, but most times I would guess that they were hidden for a reason.

The second section lets me modify some of the presentation features of the web part beginning with a header for the web part. Note that this is not the same as the web part Title that appears in the chrome of the web part. In fact, you may turn off the chrome and use this property to place a header at the top of the table of contents.

You can also modify the style of the header. The figure below shows the pre-defined styles available for the header.

You can also define the number of columns to divide the content area into. You can also modify the styles for the other levels. This will affect the sub-site entries in the Table of Contents.

The third section deals with organization of the items in the Table of Contents. By default, SharePoint orders the items in the Table of Contents to match the navigation. However, for large sites with many pages or many sub-sites, this may make it harder to find what you are looking for. Therefore, SharePoint provides a way to provide a custom ordering of sites and the pages within the sites.

If you select the custom sort option, you can sort the sites by Title, Creation Date, or Last Modified Date. You can also choose the direction of the sort as either ascending or descending. For example a descending sort on Creating Date will list the most recently created sites or pages near the top of the list making it easier to find what is new on the site.

After you have set all the properties, you can apply your changes and click OK to view your Table of Contents page.

That’s all for this week. C’ya next time.

I Like Your Moves

Moving Files between SharePoint Libraries

You may occasionally encounter the need to move a file from one SharePoint library to another. While there is the brute force method (download the file to your local machine and then upload it back to the new library which may be in a different site), there are at least two other easier methods.

For the first method, you need to know the URL of the library to where you want to move the file before you begin the process. Then follow these steps.

Step 1: Navigate to the library containing the file you want to move. For the purposes of this article, I’m going to start from a library in our team collaboration site to move a file to our intranet site. The specific starting and ending destinations are not the important issues here. Rather the technique is what is important.

Step 2: Locate the file you want to copy or move from one library to the other and hover over the right side of the document’s Title. Click the down arrow to open the document menu as shown in the following figure and select the option: Send To and then select Other Location from the secondary fly out menu.

Step 3: This opens the Copy dialog in which you need to specify the destination library or folder as well as the filename to be used in the destination library. (If you do not know the URL of the destination library, you may need to close this dialog and find that URL first. Don’t say I didn’t warn you.).

Step 4: Note that the default location is the site in which the current library resides. If you simply want to copy a file from one library to another library within the same site, you can just add the library name to this URL. If the library name has spaces, you must replace each space with the characters %20. The following figure shows that the file is being copied to a library named: “Server Training”. It also shows that the name of the file will be changed to ‘Add a new user role’. (Yes, this means that you can create a copy of a file in the same library by giving the copy a new name.)

Step 5: When you click OK, the Copy Progress dialog appears. You can review the information on the copy here before you press OK. After you click OK, the copy proceeds and if successful updates the Copy Progress by showing the text string that: The copy operation was successful.

If you wanted to move rather than copy the file, you must go back to the source library and manually delete the original copy of the document.

If the copy fails either because you entered the URL incorrectly or you do not have Add rights to the destination library or there already was a file in the destination library with the same name, you will get a message that the copy operation failed.

This method may be okay if you only have a few files to copy, but if you have a large number files, copying them one at a time from one location to another can be time consuming. A better way to transfer multiple files from one location to another may be to open the library using Explorer. To do this, follow these steps.

Step 1: Open the source library and display the Library ribbon. In the Connect & Export group, locate and click on the Open with Explorer command/icon. (I could be wrong, but I believe you must have Internet Explorer on your local machine for this to work.)

SharePoint opens an instance of Windows explorer that looks just like the display of any local folder from your desktop, but displays the contents of the SharePoint site.

Step 2: In a similar fashion, open another tab on your browser or even another browser instance and navigate to the destination library. Again open the library and then click Open with Explorer from the Connect & Export group of the Library ribbon. You should now have two separate instances of Windows Explorer open, each displaying a different SharePoint library. Now either close or minimize your browser windows to get them out of the way.

Step 3: Arrange the two Windows Explorer instances so you can see the source file as well as the destination library.

Step 4: Now simply click on the file or files you want to move and drag them from the source window to the destination library.

Note, as the image below shows, you can even copy SharePoint files back to your local computer by opening Windows Explorer from your local computer and navigating to the folder where you want to copy or move the files.

If you just click on the files and drag them from one window to another, the files will be copied. However, if you drag the files by pressing and holding down the right mouse button while you drag the files, when you release the mouse button you will be prompted with the following dialog.

In this menu Copy here is bolded because that is the default option. However, by clicking the Move here button, you can copy the files to the new location and then have the original files from their original library automatically deleted.

You can even create shortcuts to the files and place them on your desktop by pressing the right mouse button and dragging the files from their source to the Desktop within Windows Explorer and then select Create shortcuts here. Your copy of Windows Explorer may also support other options like the ability to create a zip file from the copied files and placing just the zip file in the destination folder.

That’s all for this week.

C’ya next time.


Readability, that word refers to both the visual and the content of your text. To be readable, your text must first be visually easy to read. It may surprise you know that text on a screen is more difficult to read than the same text in print. In fact, printed text often is created using a serif style font such as Times Roman. The tiny crossbars on the letters of a serif font help the eye move horizontal across a line of text aiding in the ability to read more rapidly text in paragraphs and pages. To bring emphasis to titles and headings, a sans serif font such as Arial with its simpler straight lines and curves tend to make your eyes hesitate just a bit which helps titles and headings stand out.

Surprisingly, this should not be the way you format text for display on a screen. In fact, because it is more difficult to read text on a lighted screen, it is generally better to use an Arial style sans serif font to help make the letters clearer to read. Perhaps some of this is related to perceived character spacing since the straighter characters do not quite approach each other as much as do the serifs in a font such as Times Roman.

Pay attention to text size. Text that is too small or too large can make text more difficult to read. The problem here is multiplied by the different form factors that web pages, e-books, etc. have to deal with ranging from 24-inch desktop computer screens to 5-inch smartphone screens. Of course most electronic devices will allow the user to zoom in or zoom out of the text to effectively resize the text to their liking, but you may want to start with a font size that will make your text easy to read by the most number of users without manual adjustments.

Consistency in the use of fonts is also important. Once a font style is selected, it should be used for all content on all pages of a website. It can be quite annoying to deal with a different font style as one goes from one page to another (or even worse, one paragraph to another). This detracts from the sense of continuity between the different portions of the content. Sure you can use italics or special fonts for emphasis of small text segments. However, even putting an entire paragraph in a different font style can make it look like it does not belong.

Of course, all rules have exceptions. For example, you may place material quoted from another person or source in italics, or a different font style, or even a different color to purposefully bring emphasis to it. However, be careful of using type styles such as underlined text or certain colors of text in places where the user might misinterpret the text as representing a hyperlink.

Font colors should be carefully selected considering the background color to give the reader a high contrast. The greatest contrast is black text on white background or vice versa. However even here a white background is preferred for content read in normal lighting conditions and a black background is highly preferred for text read in the dark. In fact, some programs including some e-book readers have the ability to automatically flip the text and background colors based on the ambient light level. For example, my iPad’s iBook reader does that.

On the other hand, using a light blue text on a medium blue background may not provide enough contrast. A related problem I have seen is placing darker text on a black background such as a medium to dark blue or red on black. Also, some people perceive colored text on a colored background as being smaller than the equivalent black text on a white background. This is especially true of lighter text on a darker background.

Another consideration is the width of the text column. Generally web pages use two or more columns of text so that the reader can more easily move their eyes across the line of text and then quickly find their way down to the next line. The wider the text column, the more difficult this is.

Related to this is the size of the paragraphs. Longer paragraphs can feel exhausting to read compared to shorter paragraphs. Also, the use of shorter paragraphs forces the writer to condense their ideas into smaller more easily digested content nuggets.

Long web pages should be minimized. A user should not have to scroll down the equivalent of more than two or three screens to read the entire page. I’ve seen web pages that require the user to scroll and scroll and scroll to get to the bottom. Do you really think that users will ever find the content at the bottom of that page? Of course not. There is even good argument for a single article to be continued on another page after it begins to get too long. Don’t be afraid to break articles into multiple sub-topics that are then linked through multiple pages.

In addition to all the visual aspects of making your content readable, consider also the grade level of your writing and whether it is appropriate for your audience. Microsoft Word can analyze your text and report on its readability. To turn on this feature, open the File drop-down menu and select Options. In the dialog that appears, click on Proofing in the left column. Locate the section on the right side of the dialog: When correcting spelling and grammar in Word. Click the check box: Show Readability statistics. Now when you create text in Word, you can click the icon in the lower left status bar to the right of the word count. The resulting dialog box will give you readability statistics for your text including number of sentences in a paragraph and the average number of words in each sentence. At the bottom of the dialog is the Flesch-Kincaid Grade Level. Try to keep this number between 8 and 10. This document is an 8.7.

Finally, don’t try to eliminate all white space on a page. White space gives your eyes a chance to rest. A page completely filled with text and no white space between paragraphs or columns or around images is very hard to read.

C’ya next time.


Last time we talked about following a document. While the ability to follow a document is relatively new in SharePoint, alerts have been around at least since I started using SharePoint 2007. Alerts are more narrowly focused than the social tool: Following. By that I mean, alerts are available only to track changes to documents (or items in a list). You cannot create an alert on a user or tag. However, alerts provide some additional flexibility on what types of changes you want to track and when you want to hear about them. So let’s dive into alerts.

If I were to open any library or list, I can click on the Library (List) tab or the Document (Item) tab. In both ribbons, there is an icon with the name Alert Me in a group called Share & Track. Does Microsoft duplicate this feature on both ribbons for some reason? Actually, yes. The Alert Me icon in the Library or List ribbon is meant if you want to create an alert to changes to any document in the entire library or list. This feature is useful if you need to monitor whether others are adding, editing or removing documents or list items. On the other hand the Alert Me icon in the Document or Item tab can be used to monitor individual documents or items for changes. So the first thing I have to decide is whether I want to track changes to the overall library or list or to an individual document or item.

For the balance of this discussion, I will assume that I want to monitor changes to an individual document in a library. However, the technique that I will show here is essentially the same for monitoring changes to the entire library, an entire list, or just a single item in a list. I mention the differences at the end of this article.

If I want to create an alert, I need to select the document first by either clicking on a non-hyperlinked column within the document or preferably by clicking the check box in the first column of the document row.

Note, you may think that you can create an alert on multiple documents by selecting their individual checkboxes before continuing. Unfortunately, if you select more than one document, the Alert Me option in the Document ribbon becomes disabled. You can only create alerts individually on documents.

The image below shows the Alert me icon in the Share & Track group. I have already clicked on the lower half of the icon (where the down pointing triangle appears) to open the dropdown menu of options. In this case, I want to select the first (or default) option for this icon to set an alert on the currently selected document.

Next SharePoint opens a dialog which is rather long. Therefore, I will have to explain first the top half and then the bottom half. In the following image, you see the top half of the New Alert dialog. The first thing you need to do is to create an alert title. Because you can create many alerts on different documents in different libraries in different sites, you want to carefully name your alert so that when you manage your alerts (note the second option in the above dropdown) you can easily identify the alert that you may want to change or delete.

A side note on managing your alerts. You can only manage alerts within a single site at a time. You cannot see all of your alerts within a site collection or a SharePoint farm, at least not with the built-in tools. There are some 3rd party scripts that will help you see and delete alerts across all your sites. I may discuss this in a future blog.

Although I’m not going to go into detail here about naming conventions (that is almost like talking about religion or politics), you may want to consider the following factors in defining your alert name:

  • The name of the site/library
  • The name of the document
  • The type of change you want to have the alert monitor.
  • The frequency of the alert reporting

I will show you the latter two factors when we get to the second half of the dialog in just a moment.

The second thing you may need to do is define to whom you want to send the alert. By default, most users want to receive the alert themselves. In fact, unless you have owner rights to a site (as I do), you can only create alerts that you receive and you will not be able to enter users for this property. On the other hand, site owners can create a list of users separated by semi-colons to specify who should receive an alert.

In addition, you may or may not have the ability to send the alerts by E-mail or text message depending on how your SharePoint administrator has set up your system. I’ll assume that e-mail notification will be on for most users and create my alert that way.

Moving to the second half of the dialog, the next property of the alert I can set is to define which types of changes I want to see in my alert. Read these options carefully. Only the first option, Anything Changes, will report changes that I myself made to the document along with changes from others. I may not want to see my changes since I already should remember them. Therefore, the other three options become more interesting. The second option shows me changes that anyone but me made to any document. Again, I may not care so much about changes made to other people’s documents, but only documents that I added to the library. In that case, I might select the third option. Perhaps even that is too much information. Perhaps I want to know when someone else comes in after I have made changes to a document, whether I originally created that document or not, and may have made changes on top of my changes. Then I would select the last option in this group.

The last option I can set for a document alert is when I want the alert sent. I could ask to see the alert immediately (or at least within a few seconds). Unless knowing about the change is critical, this may be more annoying that it is worth. Perhaps I would prefer a daily summary of all changes made to the document. Sure that may mean that I may not be made aware of a change for up to 24 hours after a change was made, but that may be enough. Note that if I select this option, I will have to select what time of day I want to receive that alert. In the figure above, I selected to receive the alert at 1:00 PM.

Finally, I can choose to receive a weekly summary for the alert. In that case, I must define not only the time that I want the alert sent, but also the day of the week I want. For example, I might want to see my weekly alerts first thing Monday morning.

When I click OK at the bottom of the dialog, SharePoint saves my alert definition and begins monitoring for the changes I asked for. If any changes occur to the document in the current reporting period, I will receive an e-mail detailing the changes. If no changes occur, no e-mail is sent.

There is one significant difference when creating an alert on an entire library or list, I can specify whether I only want to see:

  • when new documents are added to the library,
  • when existing documents are modified in the library,
  • when existing documents are deleted from the library,
  • or all of the above.

Note, it is not possible with a single library alert to see only additions and deletions to the library. Either I have to deal with receiving a single alert with all changes to the library or I can create two alerts, one for additions and one for deletions.

So as promised, why would I use an alert over following or vice versa? First, I like getting email alerts about changes and not having to go to my Newsfeed page. Also I like the fact that I can customize the type of change to the document or the library that I’m interested in and not have to wade through changes that do not interest me. On the other hand, alerts do not let me following people or tags to let me know what else individuals are doing or where other similar content may be. I can also follow documents and libraries without having to implement e-mail or SMS services which alerts require.

So I hope now you know how to decide whether you want to follow objects in SharePoint or to receive alerts. Each has their place if you use them wisely.

C’ya next time.

Someone Is Following Me

So last time I showed you how to share access to documents in your SharePoint document library. If you are only allowing others to view the documents, you may not have any additional concerns. However, if you grant them edit access, you might be interested in knowing if or when they make changes to one of the documents. More importantly for them, they may want to follow your document to know when you or someone else has made a change to it. One way that you (or they) can do this is to follow the document. While today’s discussion will focus on following documents, you can also follow sites, tags, and people.

If after you read this document, you do not see the buttons to follow documents (or sites or people) contact your SharePoint administrator. They may not have turned on this capability in Central Administration. Note that your SharePoint Administrator can also limit the number of people, documents, or sites that can be followed.

The following figure shows a document library with one of the documents selected by clicking on the ellipses to show the additional features dialog.

By clicking the FOLLOW command at the bottom of the dialog, I can receive notices on my Newsfeed page when this document is changed, even if only the properties of the document have been changed such as the document name or other metadata value. After clicking FOLLOW, SharePoint immediately confirms that I am following this document by temporarily displaying a message box in the upper right corner of the screen.

To check my newsfeed for updates to anything I am following, click the Newsfeed button on the My apps dialog (click the grid to the immediate left of the Office 365 banner).

In the center of the Newsfeed page, you should see a list of the most recent activity in the items that you are tracking. In this case, there is only one item showing. However, after marking several documents, people, sites, and tags to follow, this list can be rather large. Notice that in this case, the text tells me the name of the person who acted on the document, what they did, and the name of the document. If I hover over an entry, an ‘X‘ appears on the right side and clicking this icon will remove the item from my feeds list. They will also automatically be removed after several days. However, since this information is only stored in cache, if there is a reboot or an iisreset, the list can be truncated sooner.

On the right side of the Newsfeed page, you can see a summary of the number of people, sites, documents, and tags that you are following. If you click on any of these (that have a value greater than 0), SharePoint shows a list of the items you are following.

If I were to click on the number ‘2‘ above ‘documents’ above, I can see information about the two documents that I am following. I can click on a document name and SharePoint assumes that I want to open the document. If the document happens to be a Word document, Word Online opens the document in view mode. Of course, I can choose to edit the document either online or on my local machine by clicking one of the options in the Edit Document dropdown. I can also stop following a document by clicking the Stop following link beneath each document name and address. Similar options exist for displaying and following people, sites and tags.

If I were to click on the Stop following link, SharePoint gives me a chance to confirm that I really want to stop following that item or I can cancel from an accidental click on the link.

Before ending for today, I want to clear up something that may be confusing as you create your sites.  You may have created a team site to work on in SharePoint Online and you may see a web part with the title Newsfeed in the lower left of the default page. (You can see this in my January 24th 2015 post http://bit.ly/1EkTWoa).  Do not confuse this Newsfeed web part with the Newsfeed app I referenced above in your My Apps dialog.  They are not the same.  In fact, the team site Newsfeed is actually more of a traditional Internet newsfeed in which you can post information pertinent to the site and allow others to respond or comment on it.  Do not look here for references to sites, people, documents, or tags that you are following.

That’s all for this week. Next week I’ll close this mini-series with a review of using alerts as an alternative to following a document and explore some reasons why I might choose to follow some documents while preferring to receive alerts on others.


Sharing Documents in SharePoint Online

There is nothing wrong with using SharePoint along with its document libraries to simply keep all of your documents in the cloud so that you can access them from anywhere. However, a likely scenario is loading documents you want to share with other members of your work teams so they can view and edit them. By default, documents loaded into SharePoint online are not shared. That’s probably a good default. You would not want to have to remember to take away rights to a private document you uploaded. Another way to look at it is, you may need to share different documents with different groups of people.

Today we are going to start a short series of discussions on how to share documents you have added to your libraries with other members of your work team and show two ways you can track their changes. Let’s begin by going to the document library I created for use in the last few blogs.

I selected the document named: Document in the above image by clicking on the selection checkbox to the left of the filename. Notice that this highlights several of the other options at the top of the library including: edit, manage, and share. To share this document I can click the command: share.

I can also click the ellipsis button to the right of the filename and from the dialog that opens, click the SHARE command along the bottom of the dialog.

Both actions open the Share dialog shown below:

Note in the upper left that SharePoint tells you that the document is currently shared only with you. To invite other people to edit your document, make sure the Invite people menu item on the left side of the screen is selected. Then enter the names of the people with whom you want to share the document in the text box to the right. Enter the names one at a time. Because I am tied to Active Directory, SharePoint will use the characters I enter and perform a search on names that have the combination of letters that I’ve entered as shown below (ok, I blurred some of them out to protect the guilty). The more characters you enter, the more refined the search can be. However, don’t be afraid to stop before entered the person’s complete name and then select them from the dropdown list. Note that this can be very useful especially when you are not entirely sure of the correct spelling of the person’s name.

You can select more than one person to share the file by adding a semi-colon between the names. You can also select groups that your organization may have defined in Active Directory to give everyone within that group access to your file.

But what if you don’t want to give the person(s) the ability to edit your file. Maybe you only want to let them view the file. Notice the dropdown list to the right of the name textbox. Click the arrow to open the dropdown and select Can View to limit the users to viewing your document.

If you have some people who you want to be able to view the document and some who you want to edit the document, you must go through the process twice, once for those who can view only the document and once for those who can edit the document.

Because the people you are inviting to edit or view your document will receive an email to announce their permissions to your document, you can also include in the textbox immediately below the box where you entered their names text to tell them why they are getting rights to your file. In some case, you may need to ask the user to sign-in first before they get access to your site. This insures that only those you invite to view or edit your document can actually get to it even if someone else ‘finds’ the link to your library.

Finally, there is one final command that simply says, ‘SHOW OPTIONS’. Honestly, the first time I saw this, I was expected that clicking this command would open an entire dialog with other properties that I could set. Rather, I was surprised to find that it only refreshed the current screen to shown one additional option that asks whether you want to send an email invitation to the people you just selected.

Of course, you don’t need to send an email invitation. You could simply tell the people to go to your site and view or edit the document. However, if you are inviting a lot of people, sending an email is a lot more efficient. In addition, the email that is sent will include information like the link to the document so they can simply click on it to go to the file. The email they receive will look something like the following image.

Well, that’s it for this week. Next time I will look at how to get notified of changes made by people who share edits rights with your document. C’ya.

SharePoint’s Calculated Columns

In SharePoint, you can create a calculated column for any list or library. A calculated column can use values from one or more of the other columns in the list. For example, one of the first uses I found for using a calculated column was when I wanted to sort by more than two columns in my view. For example, suppose I had a library of project files that included the following fields:

Calculated Columns 01

In my view, I want to sort first by [Project_Name], then by [Year] and then by [Title]. When I attempt to create a custom view, I only get to choose two levels as shown below:

Calculated Columns 02

This would sort my files first by project, then year, but the actual documents would appear randomly by document name. The way to solve this problem is to create a calculated field to concatenate two or more of the columns that will be sorted in the same direction (either both ascending or both descending). For example, I might combine project and year into a single calculated column as shown in the following figure:

Calculated Columns 03

Now I can go back to my new definition and use [Project_Year] as my first sort and [Title] as my second sort. In the columns that I display, I can still use [Project_Name] and [Year] as separate fields and not display the calculated field [Project_Year].

Calculated Columns 04

Note the use of the ampersand operator to concatenate the two string fields. (Yes, [Year] is stored as a string in this list.) If I had used a plus sign as the operator, I would get an error because the project name field does not begin with a number even though the [Year] string can be in theory converted to a number.

The net result is a view that appears to sort my files by project first, then year, and then document Title. There are other web parts where you use a similar trick such as the Content Query web part.

When to use the ‘+’ vs. the ‘&’ operator

To illustrate the difference between the plus sign (+) operator and the ampersand (&) operator, I will use a very simple table that has several string fields and several numeric fields as shown below:

Calculated Columns 05

The columns [String] and [Number] are both stored in the list as string data (note the left justification of the numbers for the [Number] column. On the other hand, both [Number_Col] and [Number_Col_2] are numeric columns as you can discern by the right justified values.

Next I am going to create several calculated columns using the following formulas:

        Calc1 = [Number] + [String]

        Calc2 = [Number] & [String]

        Calc3 = [String] + [Number_Col]

        Calc4 = [String] & [Number_Col]

        Calc5 = [Number_Col] + [Number_Col_2]

        Calc6 = [Number_Col] & [Number_Col_2]

While SharePoint allows me to create each of these formulas, displaying the resulting values in my list shows some interesting results.

Calculated Columns 06

As you can see from the results, attempting to use a plus sign (+) when concatenating two values in which at least one is a string results in an invalid value (#VALUE!). On the other hand, concatenating a string and a number is no different than concatenating two strings or even two numbers when using the ampersand (&) operator. These results were the same no matter whether I choose the final result to be a string or a numeric value when defining the calculated column.


Next I tried to use the [TODAY] variable to see if I could create a calculated column that would add the numeric value in [Number_Col] to display a future date. When I tried to save the formula for the calculated column, I got the following error:

Calculated Columns 07

Note that this error message tells me that I cannot use volatile functions like [TODAY] or [ME] in a calculated column. However, when I tried to use the function TODAY(), the calculated column was not only accepted when I saved my column definition, but the data displayed as expected when I displayed the list.

Calculated Columns 08

I also tried many of the other Excel-like functions to work with strings and numbers and found that all of the ones I tested also worked as expected as long as the function arguments (columns) were of the correct type.

That’s it for this time. C’ya next week.

Default Column Values vs. Column Validations

Last time we talked a little about default values and showed how you can calculate the month name or day of the week from the current date using TODAY. If you added a record or two to the list using the default value, perhaps you noticed that even when you came back to the record a few days later, the default value had not changed. Why? Because the default value is defined at the time the record is created, not at some future time when someone views the record. Effectively, once defined for a new record, the value remains set unless a user manually changes the value of the field.

Perhaps you tried to create a default value using the value of another column in the list. For example, suppose you had an event list in which the past due date for any money owed to the event was seven days after the date of the event. Further suppose that you had a column named EventDate already in the list. Your first thought might be to create a column for the Past Due Date to have a default value calculated as EventDate + 7 or seven days after the event date.

Additional Column Settings

When you try to save your column definition though, you will get an error like the one shown in the next figure.

Something went wrong message

Why did SharePoint not allow the use of the value from a previous column in the default value of the current column? Simply because at the time that the default values for the new record (item) are set, which is before the user enters any values for any of the columns in the new record, the EventDate column does not yet have a value and is undefined. Therefore, SharePoint cannot use the EventDate column to calculate default value of the Past Due Date column.

So while I can take predefined variables like TODAY and use them in the calculation of a default value, I cannot use values from other columns in the list because those values are not defined at the time the default value must be calculated. That also further emphasizes why default values do not change over time, even those based on variables like TODAY, because they obtain their value at the time the default is calculated and that calculation is not performed again just because someone looked at the list record.

Let’s now take a look a column validation. This feature of lists was a welcomed addition to SharePoint 2013 and provided the ability to trap bad values at the time that data is entered. As developers know, trapping bad data through data validation at the time of data entry is far superior to running reports later to discover and fix bad data after it has been entered and the user has moved on. However column validation is often overlooked in SharePoint when defining columns. Perhaps part of the reason is that the area that defines column validation is by default closed when defining the column properties. You must click the box before the words Column Validation found at the bottom of the page to open this section. Second, it is often not clear to the user how to create a column validation expression. Let’s take a look at a simple example.

The first thing to understand is that you must create an expression that is either true or false. In the text of the Column Validation it implies you can enter an expression like [Company Name] = “MyCompany”. However, I have not found this type of expression by itself to be valid. Rather you must enter an expression that begins with an equal sign and then continue with an expression that will evaluate to true or false. Therefore, a valid version of this expression might be: = [Company Name] = “MyCompany”.

Note that the field name, even when self-referencing the current field must enclose the column name in square brackets if the name contains spaces. You can omit the square brackets if the column name does not contain spaces. Also the expression to the right of the first equal sign is not considered an assignment of a string to the column, but rather as a Boolean comparison expression. We are basically asking in this example whether the Company Name is equal to “MyCompany” , not setting the Company Name to a value of “MyCompany”

Suppose you want to validate the Event Date column of the list we previously talked about. You may want to make sure that the event date occurred in the future. Your first thought might be to create an expression that checked if the Event Date was greater than the current date as defined by the variable TODAY as shown in the following figure.

Column Validation using variable TODAY

This expression would also fail because it will try to treat the variable TODAY as a column name.

Something Went Wrong When Using Variable in Column Validation

However, there is a TODAY() function in Excel. So making the following slight change to the column validation will allow the updated column definition to be saved and will guarantee that event dates entered will always be greater than today.

Column Validation Using Function TODAY()

So to summarize, what do we know at this point?

  1. You cannot reference another column in the list within a default value expression.
  2. You can use default variables like TODAY and ME within a default value expression.
  3. You can use many (I have not yet checked them all) Excel functions within a default value expression.
  4. You cannot reference another column in the list within a column validation expression but you can reference the current column.
  5. You cannot use default variables like TODAY and ME within a column validation expression.
  6. You can use many (I have not yet checked them all) Excel functions within a column validation expression.

C’ya next time when I look at Calculated Column expressions.

Governance For All

I was thinking about SharePoint governance today and its effect on the success or failure of SharePoint. Fundamentally, SharePoint governance is a document that forms an agreement between the people who support SharePoint and rest of the organization. It then dawned on me that I could substitute SharePoint for any other system. However governance is an umbrella covering many things such as policies, rules, roles, and responsibilities. Within that umbrella of governance, I would like to think of these four areas as represented by the four legs of a chair, the chair being the system. Failure of any one leg may not make the chair crash, but it will make it less stable. Failure of more than one leg can cause the chair to collapse.

The same is true of SharePoint governance. Without adequate policies, rules, roles, and responsibilities, your SharePoint implementation may be on the verge of collapse. The hidden problem at many organizations is that even if you initially create a sound governance policy, if it is not followed and enforced, it may be as bad as not having a governance policy at all. What may begin innocently enough as a minor exception to the rules or policies here and there, can quickly slide down the slippery slope to total chaos. Stay with me a minute.

While governance may seem to be filled with a lot of rigid policies and rules, governance, whether for SharePoint or for any other major system in your organization, must be flexible. It must be almost an organic thing. By that I mean that it must be adaptable to the changing needs of the organization. A change in needs might be something major such as the addition of outward facing web sites to your existing farm of collaboration sites, or it may be something as simple as a font change used for the body of content. The thing to remember is that all change sets a precedence. The question you should ask is whether that is a good precedence that you want to set or a bad one?

Once policies and rules are established and made part of the governance, then changes or exceptions to these policies need to be very carefully vetted and then added to the governance so it does not open the door to other changes that may be less desirable. For example, if the initial governance called for Arial fonts, exceptions might be made for other similar fonts such as Calibri. However, as soon as you begin allowing Times Roman or Impact, you are on the path to allowing Broadway, Playbill, and even Comic Sans Serif. But governance can stop that that by specifying that any font from a limited list of fonts can be used, but only one font can be used per page or per subsite. The same can be said for colors, page layouts (themes), and other visual effects. In addition, the governance should define a method to request changes to policies and rules. Perhaps those requests get funneled through a single person, but more likely they should go to a committee that decides on any governance changes.

However, even if you have all the policies, rules, roles and responsibilities well defined and you have a committee to review and approve or reject any changes to the governance, if you do not have upper management’s full support of the governance, your efforts are in vain. Like a dragon without fiery breath or at least large sharp teeth, users will soon realize that they can get away with anything with no more consequences than your pitiful cry to never do that again. You must have management’s support to enforce the governance.

The problem is that maybe management just came back from a conference, or perhaps it was from a free seminar provided by an alternative vendor who provided a lunch ‘n learn at the local steak restaurant. While the product demoed does basically the same thing as your current product, marketing demos are very good (or they should be) at highlighting the best features of the product making it look like the new and most desirable product on the market. Rarely does the demo go into how much time or skill was needed to set up the demo. Of course demos are meant to look flawless and so simply that your dog could do it with one paw tied behind its back. It is your job to be able to tell the difference between a well-crafted demo and a system that is truly easy to use before your organization starts spending money on something it really does not need.

But here is another big ugly secret. No system can be successful without governance and top management support. Individuals resist change. They will refuse to learn how to use a system. They will claim a system is too hard to learn or to use. They will blame their own mistakes on the system or on the restrictive governance policies or the lack of training that fits their exact circumstance. Management must lead from the top down with strong reasons why the policies and rules will serve the organization as well as the users better. Governance gives them a way to make that happen by formalizing the policies, rules, roles, and responsibilities of any major undertaking. Governance is leadership by getting out in front of everyone and saying, ‘Follow me and be successful.’

Well I’m off to another SQL Saturday this weekend again, this time in South Florida. How does this fit into today’s mini topic? At SQL Saturday there will be hundreds of individuals who as DBAs or developers use SQL Server and have policies and rules they follow when using SQL Server. They have specific roles defining what they can and what they cannot do. They have certain responsibilities to perform specific tasks using SQL Server. What makes SQL Server work or not work within their organizations is largely dependent on how well they embrace the governance around creating, using and maintaining databases and the processes that access those databases within their organizations.

You see it is not just about SharePoint or SQL Server or any other specific product. The need for governance applies across all products and activities in the organization as does the need for management support of that activity. No organization would arbitrarily switch from using SQL Server to Hadoop to Informix to ingres to Oracle to Postgres to Access or any other database system every couple of years. Rather, they would choose a product that best fits their needs and wring out all the value they can from it by insuring that their staff is trained and supported in its use. Don’t expect a new product to somehow produce magical results. If anyone is responsible for magically results, it is an organization’s people. What does your governance say about how you use technology and your staff?

C’ya next time.

OOPS! I Didn’t Mean To Delete That

Using the Recycle Bin

If you have content management rights to your SharePoint site, you may have one time or another accidentally deleted the wrong document, list item, image, page, or even worse, the entire site. The first thing to remember when you do this is not to panic. The second thing to remember is that you need to act sooner rather than later to restore the item you deleted.

Deleting the wrong item out of a list or library is a relatively simply thing to fix. In the following image, suppose I accidentally deleted the Word Document: How to Approve a Page.

When a person deletes an item from a library or list, SharePoint puts the deleted item in the Recycle Bin. That person can open the Recycle Bin by either selecting Recycle Bin which is typically found at the bottom of the bottom of the Quick Access menu unless custom branding has moved it somewhere else.

They can also get to the recycle bin by opening the Site Actions button and clicking on View All Site Contents (or clicking on the All Site Content link in the above image) and scrolling to the bottom of the page. This view of the Recycle Bin tells you that you can restore items that you have deleted from this site.

This is an important distinction because you can only restore items that you have deleted. You cannot restore items that someone else has deleted, well most users cannot. But we will get to that later.

When you open the Recycle Bin using any of the above methods, you will see a list of items that you have deleted. You can select an item to restore by clicking on the empty checkbox on the far left of any item and then clicking the Restore Selection option. You can even select multiple items from the Recycle Bin by clicking on each of their checkboxes and restore all of them with a single click.

The Restore Selection option restores the selected item to its original library location. If you had additional metadata associated with that item, SharePoint will also attempt to recover that metadata. Be careful however, not to click the Delete Selection. If you select one or more items from the list and then click the link Delete Selection, you will delete the item from the Recycle Bin. At that point, the only way to recover the item is either by requesting a site restore from a backup or by bribing your Site Collection Administrator to restore it from the Site Collection Recycle Bin. More about that later.

This restore technique works for any item in a library and includes documents, images, pages, and even items in a list such as individual announcements from an announcement list.

You should note however that items only remain in the Recycle Bin for a maximum of 30 days. Well, it is 30 days for us. However, your SharePoint administrator can change that setting. After that point, the Recycle Bin automatically flushes the old item from the bin and it is no longer possible to restore it. The Recycle Bin may also start to delete older items if it starts to run out of available space.

Recovering Items Deleted by Others

So what happens when another user accidentally deletes something from a library but they are no longer in your department or company? There is the ability for the Site Collection administrators to restore items from the full site recycle bin. First you must find your Site Collection administrators and then request that they retrieve the item(s) from the recycle bin. You must either supply the name of the item(s) to be recovered or ask them to send you a full list of all the items in the recycle bin from which you can choose which items to recover. Note that this process can take extra time and the 30 (or whatever your number is) day restriction to recover deleted items still applies. So do not delay.

Deleting a Site

How do you accidentally delete a site? First of all, not everyone can. This is a limited feature capability that only site owners and other roles that you have given Delete Site permission to. That being said, it involves first going to the site to be deleted and then selecting Site Settings from the Site Actions dropdown. In the Site Settings page, look for the option Delete this site in the Site Actions group and click on it.

In an attempt to protect you and the site, SharePoint asks whether you really want to permanently delete the Web site and its contents as shown in the following image. You could still abort the process at this point by clicking the Cancel button, but if you click OK, SharePoint will delete the site.

In earlier versions of SharePoint such as SharePoint 2007 or WSS 3.0, deleting a site was truly permanent and the only way to recover from such an error was to restore your site or possibly site collection from a backup. However, beginning with SharePoint 2010, deleted sites are also stored in the Site Collection Recycle Bin. Unfortunately, you need to be a Site Collection administrator to access the Site Collection Recycle Bin to find the deleted Site. (Think about it. If the deleted site was only in the Site Recycle Bin, then deleting the site would also delete the Recycle Bin. Therefore, Microsoft decided to place deleted sites in their parent Site Collection Recycle Bin. That also means that if you delete the site collection, there is no safety net other than restoring the site collection from a backup.)

To recover a site if you are a site collection owner, open the Site Collection Recycle Bin which is accessed from a brief paragraph at the top of the regular Recycle Bin in any site in the same site collection.

Using the Select a View menu on the left side of the screen, the site collection administrator must select the option: Deleted from end user Recycle Bin. This gives us a clue that any file deleted from an individual’s recycle bin (document, image, page, etc.) can be recovered the same way as recovering the site by going to this option. This is apparently what the previous warning message means by saying that it is permanently deleting the site. It is by-passing the individual’s recycle bin and going directly to the site collection recycle bin.

Note: the first and default option under Select a View, End User Recycle Bin Items, lets the Site Collection Administrator recover any deleted file from any user in any site within the site collection.

To restore the site, the site collection administrator simply selects the site (or other file) to be restored by placing a check in the checkbox to the left of the appropriate site and then clicking Restore Selection. Clicking Delete Selection here will truly delete the site permanently that only a restore from backup can undo.

If you are restoring a site, SharePoint also prompts you to confirm that you want to restore the named site. You have the option to continue by clicking the OK button or to cancel the restore by clicking the Cancel button.

You can see that the site has been successfully restored by clicking on View All Site Content in the Site Actions menu and scrolling down to the Sites and Workspaces section. The restored site should be back in the list of available sites. You can navigate to the site by clicking on its name

I hope this article gives you some idea of how to recover a deleted object in SharePoint 2010 and better.

C’ya next time.