Never Display Lists or Libraries Directly on Public Web Sites

Most people think of SharePoint as a collaboration tool. Relatively few of these people think of using SharePoint to create their corporate web presence. Like all tools, there are pros and cons to this decision and I’m not going to debate those here. I’m just going to assume that you your organization has made the decision to create their public facing web site using SharePoint.

In fact, our organization made that decision seven years ago. Because of the size of our organization, content management was decentralized giving each department control over their own site content. Overall that decision has been good. However, there have been several issues that have come up over the years. Their solutions may also be of interest to others. Therefore, I will occasionally explore one of them as a blog topic just in case they might help others.

A common problem that occurred on our public sites when we were using SharePoint 2007 and SharePoint 2010 stemmed from anonymous users attempting to click on links in the navigation that attempted to display a list. While it is possible to display a view of a list or on a public site page (see next week’s article), that view cannot contain a link back to the properties of the list items. The problem occurs because anonymous users do not have edit rights to the contents of a public site. It does not matter whether are attempting to open a list or library directly or attempting to access the properties of an item in a list, SharePoint displays a prompt to log into the site. Since an anonymous user does not have a login, this situation is frustrating.

Lets first deal with the first problem, the one where you attempt to directly open a list or library. In this case, the content editor probably created a site but did not select the option to NOT include it in the navigation. When creating the list, the content editor simply supplied the name of the list and clicked the Create button. This resulted in SharePoint making some assumptions. The assumption of concern is the automatic inclusion of the new list in the Quick Links navigation. For a collaboration site or even an intranet site, this assumption may be exactly the choice you would want so that users could easily navigate to the list and interact with it. When these users click on the list link in Quick Links, SharePoint opens the default view of the list and allows the user to view and edit the contents of the list (assuming they have rights). However, anonymous users will never have edit rights to the lists or libraries in a public SharePoint site. But because SharePoint does not know who the anonymous user is who is attempting to open a list directly it asks the user to log in. As a public facing site, this action can be frustrating to these users because they have no way to log in.

Fortunately, there is an easy fix for this problem. In fact, the content editor only needs to click on More Options after supplying a list name instead of simply clicking on Create.

By going to More Options, the content creator has the ability to not display the list in the navigation (the Quick Launch) by selecting No in the Navigation section.

If they forget to do this, they can recover by selecting Site Settings in the Site Actions dropdown.

Then they select Navigation in the Look and Feel group of the Site Settings page.

Next, they can select the entry for Lists in the Current Navigation (which is the Quick Launch area) and click on Delete in the header options in the Navigation Editing and Sorting section.

This removes the heading entry for the list since there could be multiple lists. However, they must also go back and remove the list entry as well because an anonymous user cannot open the list directly. If they only remove the actual list, the heading entry would still remain.

After removing these entries from the Current Navigation area, the content creator must click on the OK button at the top or the button of the page or their changes will not be implemented.

Next time I will look at the similar, but related problem that occurs when inserting a view of the list on the page rather than just providing a link to the list to show how even this can cause problems with anonymous users. Of course I will explain how you can solve that issue as well.

C’ya next time.

Data Warehouse Patterns

My friend Kevin Goff is doing a 1 hour webminar on April 23rd for SQL PASS (the DBA Virtual Chapter) on Data Warehouse Patterns with the MS SQL/BI tools. To attend, go to:


You’ve read some of the content of well-known Data Warehousing books – now what? How do you take the structures and disciplines inside such books as the Ralph Kimball series and implement them? This session will take several of the key concepts in Data Warehousing and demonstrate some implementations using SQL Server Integration Services and Analysis Services. We’ll look at items such as Type 2 Changing Dimensions, Confirmed Dimensions, Dimension Outriggers, Role-Playing Relationships, Many-to-Many Bridge table relationships, Factless Fact tables, Handling of NULL foreign key values, and more.

Bio: Kevin S. Goff ( is a Microsoft SQL Server MVP Database architect/developer/speaker/author. He is a regular columnist for CoDe Magazine on .NET, SQL Server, and Business Intelligence topics. He speaks frequently at community events in the Mid-Atlantic region. He also hosts BDBI Radio (, a weekly webcast on SQL/BI topics

Freeing Up the Weekend in SharePoint, Part 2

In the last post I looked at how to validate a user input column value. In this post, I look at how to manipulate a calculated column to skip the weekends. This validation for a calculated column requires a different technique than last time. A calculated column does not have a column validation option. Rather, I must test for and adapt to different data conditions directly inside the formula for the calculated column using functions to test for valid values. As an example, suppose I created a SharePoint list to track CDs or DVDs from a borrowing library. This is a case in which I could ask the user to enter the date they borrowed the CD (or I can simply use a default value of [TODAY]. Then if I have a lending policy that lets the user borrow the CD for 5 days, I can let the list calculate the due date using a calculated column to add the lending period to the borrowed date. If my borrowing library is open 7 days a week and the standard loan period is 7 days, I could use a calculated formula as simple as:

= [Borrow Date] + 7

This formula calculates the due date by adding 7 days to the borrowed date. But what if my library was not open on weekends. Further, what if the lending period was only 5 days. Therefore, if you borrow a CD on a Friday, it would be due the following Wednesday. On the other hand, if you borrow a CD on a Monday, it would in theory be due back on Saturday, a day on which the library is closed. I might either make the due date the Friday before the Saturday or Sunday, or I could be generous and make the due date the Monday after the Saturday or Sunday.

In this case, I need a slightly more complex formula to calculate the due date to account for weekends. I’ve solved this problem by using a combination of the WEEKDAY() function which I used in the last post in the column validation example and the IF() function. I need to test whether the calculated due date falls on a Saturday and if so, add two more days to the lending period to make the CD due on Monday. On the other hand, if the due date falls on a Sunday, I only need to add a single additional day. I can achieve both results by using a nested IF() function that tests for both conditions as shown in the figure below. Note that if both tests fail, the due date remains just 5 days away.

After saving the formula for the calculated column, I can now test different borrow days. I can still include the formula from last time to make sure that the user does not borrow a book on a Saturday or Sunday as shown below.

However, if I enter a valid date such as April 4th, 2014, SharePoint calculates the due date as April 9th, just 5 days away as I would expect.

If on the other hand I borrow a CD on a Monday like April 7th, 2014, it would be due 5 days later on Saturday, April 12th. Since this is a Saturday, the formula in the calculated field adds two more days to the actual due date to make the cd due on April 14th.

In fact, you can see in the following image that different borrow dates would calculate the due date as 5 days out if that date is a weekday, but would extend the loan period one or two additional days if the initial due date calculates to a Sunday or Saturday respectively to make the new due date a Monday.

I hope these two posts give you a better idea of how to use both the column validation feature for values directly entered into columns by the user and the calculated formula feature for calculated columns to manage calculated results.

C’ya next time.

Freeing Up the Weekend in SharePoint, Part 1

A few days ago, a comment posted to this blog asked how to use a calculated field to insure that dates returned are never on a weekend. After thinking about this question for a few days, I decided to approach the answer in two steps. First, how can I make sure that the user never enters a date that is on the weekend. Second, how can I build a calculated date from the date entered and if the calculated date falls on the weekend, to move that date to the following Monday.

To answer the first question, I created a simple event list in SharePoint that would include an Event Title and an Event Date. I wanted the user to enter values into both fields, but wanted to prevent the user from entering a weekend date for the event date. So I began with a Custom List and renamed the default Title column to: Event Title.

Next I added a second column to the list called Event Date and chose Date and Time as the data type for that column. In addition to the normal properties for a date column (like the fact that I want the date only, the date cannot be blank, and the date can default to the current date), I also needed to perform column validation to insure that the date entered was not on a Saturday or Sunday.

In SharePoint 2013, this feature is at the bottom of the column property page. I must click the box to the left of Column Validation to open this property area if a plus sign appears within the small box. When you do, you see a text box area that allows you to enter a Formula. The problem is that the syntax for the formula is not well defined. Although there is a link on the Column Validation section to learn more about proper syntax for formulas, that reference does not list valid functions. I have found, largely through trial and error that the required syntax for column validation more closely corresponds to the syntax of the early DAX syntax in the original release of PowerPivot than to the function syntax in Excel alone. However, a good place to start is to check for an Excel function that meets you need ( and only if one is not found or if it generates a syntax error within SharePoint, check for a function in DAX (

Therefore, I looked for a function that would tell me the day of the week given any date. I found the WEEKDAY function which supports 2 parameters. The first parameter specifies the date to test. In this formula, that would be the current column. To specify the current column, just enter the column name. If the column name contains spaces as mine does or could be interpreted as a command, you must enclose the column name in square brackets. Otherwise the brackets are optional.

The second parameter defines how the weekdays are numbered based on the following table.

Parameter Value Definition
1 Week begins on Sunday (1) and ends on Saturday (7)
2 Week begins on Monday(1) and ends on Sunday (7)
3 Week begins on Monday(0) and ends on Sunday(6)


I chose to use the second definition. Thus my formula, as shown in the following figure, can look for days with a value of 1 through 5 and return TRUE while a ‘6’ or ‘7’ representing weekends would return ‘FALSE’.

A column value is considered valid if the formula provided here evaluates to ‘TRUE’. If the formula evaluates to ‘FALSE’, SharePoint displays the User Message beneath the date field and does not save the record.


After saving the properties, I can add events to my list. The SharePoint list accepts events that have an event date that correspond to weekdays.

However, if I attempt to enter an event date for a Saturday or a Sunday, SharePoint displays the User Message from the previous figure when the user clicks the Save button. The new item is not added to the list until I select a valid date which in this list is any day from Monday through Friday of any week.

So validating user input is a matter of developing an acceptable formula that returns either a TRUE or FALSE when evaluated with the current value. That’s great for validating input from a user, but does not answer the second case, that of validating a calculated value. I will address this issue in the next post this weekend.

Until then, C’ya.

Understanding SharePoint Metadata – Flattening The Hierarchy – Part 2

Last week I tried to explain the concept of metadata in terms of things you already know and use in Windows directories. So when it comes to storing documents in libraries in SharePoint did you expect SharePoint to be any different? The simple answer is that metadata works pretty much the same way in SharePoint as it does in a Windows directory. However, there are some additional reasons why you might want to consider minimizing or even eliminating your folders when creating SharePoint libraries.

First there is the concern that when multiple people are sharing a common library, the complexity of the tree structure creating by having folders within folders within folders can often result in the user getting lost when trying to find or save a file. The danger here is not just that a user may get lost in the forest of folders when trying to find a file and waste valuable time navigating up and down various branches to find the file they need, as bad as that may be, but the very real danger that they may save files to the wrong branch. When a user is saving a file in a library with a very complex folder structure, they may not immediately find the folder they want and assume that it does not exist. Then then create a new folder with the same name as a folder on a different branch of the library to save the file. Potentially, this can result in different versions of the same file (or files that should be related within a single folder) to be found in different branches of the library structure. If the file is actually saved in more than one location because of this error, subsequent updates to either file could occur independently from each other resulting in divergent copies of the document. When someone realizes the problem and attempts to bring the two copies of the file back together again, the process of manually merging the differences between the files can be a challenge.

The second reason however has a greater chance of causing problems. SharePoint only supports URLs with a length of about 260 characters (some say 255 or even 256, but let’s not quibble over a couple of characters.) This length typically does not include the name of the domain/server. However, there are also limits on the length of any folder name or filename of 128 characters. But hidden behind this limitation is the fact that some characters do not get passed as you and I may view them on the screen as single characters. These are the special characters, the non-alphabetic and non-numeric characters that must be passed as encoded strings so that the web engine does not incorrectly interpret them. Each of these special characters must be converted to three characters that represent the ASCII value of the character as a hexadecimal number. The following table shows some of the more common characters that must be converted to their three character hex equivalents before being passed as a URL string of which the total length cannot exceed 260 characters.


Hex Equivalent

Ampersand (&) %26
Equal (=) %3D
Period (.) %2E
Question Mark (?) %3F
Slash (/) %2F
Space ( ) %20


Thus, it is possible to have a path which may look like it has less than 260 characters when you count the characters in the path as you read it, but the length could exceed 260 characters after it has been encoded so it can be passed as a URL.

Note that this limitation does not apply to any query string appended to the end of the path to the actual file.

Perhaps even of more concern is when the site gets translated into some languages which can convert a single character consuming 1 byte as we see it into a string of up to 9 bytes.

Where do we most often see this? If a library has a complex folder structure and its users always navigate down through the structure one level at a time to the specific file they want, they many never know that they have a problem just because of the incremental way in which they are approaching the file. However if they attempt to capture the URL of a file buried deep within the structure, the resulting URL may look correct when they past it into a hyperlink or into an email message for someone else to directly view the file without navigating the folder hierarchy. However, when the person who receives the link attempts to click on it, which pastes the encoded link into the search box of their browser, the resulting encoded string may be truncated because of the extra characters added by encoding the link. This truncated string no longer points to a valid file in the library and the user will receive an error indicating that the file does not exist or that the URL is not valid.

So how do you trim your folder hierarchy and use metadata instead?

The simplest method is to first map out your hierarchy as shown in the following diagram showing only the names of the folders (symbolic folder names have been used here to keep the example as small as possible.)

Referring to the above figure that represents our folder structure within a library, notice that I color coded each level under the library’s root level. The folders immediate under the root are colored Yellow. The folders that are found in the Yellow folder level. These are colored Blue. The Blue level also has folder which are colored Green. Now converting this diagram to metadata, I can create a new library with no folders, just three new metadata columns. Each metadata column would represent one of the color bands. Within that metadata column, possible values will be the names of the folders from that level. For example, within the Yellow column, the possible values are: Folder A, Folder B, and Folder C. Next I create a Blue column with possible values of: Folder A1, Folder A2, Folder A3, Folder A4, Folder A5, and Folder A6. Finally I create a Green Column with values: Folder A1a, Folder A1b, Folder A1c.

Now when I add a file to this new library with no folders, I choose from the possible values in each of these three metadata columns to ‘place’ the file in the correct hierarchy level. If a file was in the original library’s root level, all three of these metadata columns would be blank (empty). For any file that was in the folder: Folder A, I would select the value ‘Folder A’ in the Yellow column and leave the Blue and Green columns empty. Similarly a file that was deep in the folder Folder A1b would be placed in the root of the new library with and I would select the value ‘Folder A’ for the Yellow column, ‘Folder A1’ for the Blue column and ‘Folder A1b’ for the Green column.

The net result is that I now have a flat structure for my library that makes it easy to find files because there are no folders and all files are visible in the default view. I can then use the library’s column headers to sort the files or even to filter files. For example, If I filter the Yellow column on the value, ‘Folder A’, the Blue column on the value ‘Folder A1’, and the Green column on, ‘Folder A1c’ I can quickly see only those files that would have appeared in that folder three levels down in the original structure

But more importantly, with the use of other metadata such as the Created By column which appears by default in document libraries, I can filter on all the files created by a specific person no matter which folder they were in previously to quickly find all of that person’s documents without having to navigate up and down through the previous folder hierarchy. I can also apply multiple filters such as files created by a specific person that were in Folder B or any of its subfolders.

While not shown in this diagram, it is possible to even have files that may have existed in Folder C2c without there ever having been a folder at level Folder C2. This would be very easy to represent in the metadata by providing a value for the Yellow column and the Green column while leaving the Blue column empty.

The challenge is mapping out the folder structure as shown in the earlier diagram making sure that you capture each of the current folders in the library and assign them to an appropriate level.

While this technique may appear at first to be very different from the folder within a folder approach used previously, it really is not so different at all and you should be able to get used to it very quickly. Now that the hierarchy has been flattened, you should never run into a problem right clicking on the name of a file in the library to get its shortcut.

You can then past that shortcut into a hyperlink or simply add it in the body of an email when you want someone else to open the file rather than attaching the entire file to the email. This will save space in your Outlook mailbox as well as reduce some of the strain on your Exchange server.

Hope this helps you understand how metadata can help you slay the nested folder dragon.

C’ya next time.