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.

Understanding SharePoint Metadata – It Really Isn’t Something New – Part 1

As many of you know, I have been promoting the use of metadata rather than folders as a way to organize the information you store in SharePoint libraries. Some of you have found this transition to be difficult to grasp. I am here today to assure you that you actually have been working with metadata in libraries for years, although you may not have thought about it in those terms.

First, what is metadata? Metadata is any information that helps classify an object, a file in this case. Let’s take a look at a standard Windows library, something we are all familiar with. In a standard Windows directory, you can view information such as the date the file was created or last modified. This information classifies changes to a file basically providing some insight to its origin and most recent changes. You might use this information to list all the files in a directory by the date created to help find a file you know you created last month.

You might also use the modified data to list the files in reverse chronological order to identify the files you worked on last.

You might even have used the Size column to sort the file by size to determine which ones were taking all the space on your thumb drive. On a network drive, the Authors column also may have helped identify who created or modified the file. The Type column might also have helped to group files of similar source type. But you know that there are many other columns that could be displayed for a normal Windows directory? Just right click on the directory header to display a popup of the available data.

By design, the popup displays the 10 most common/recent columns used, but these ten are not the only things tracked for files. In fact, if you click on the More… option at the bottom of the popup, a dialog appears that lets you choose which columns to display in the current library.

To select a column, simply click the checkbox to the left of the column name. You can even click on the column title itself and then use the Move Up and Move Down buttons to the right of the dialog to change the order in which the columns appear when you display the library. For the selected column, you can define the default width of the column in pixels with the option at the bottom of the dialog. Both of these last things can be changed directly in the list itself by clicking on a column header and dragging it to the right or left to change the order or by clicking on the faint line separating the column headers to change the column width of the column to the left of the line.

Perhaps you already knew that, but did you know that some of the column data applies to only certain types of files? For example, if you have a file of pictures, you may still be interested in creation dates, sizes, and authors, but you may also find columns like the Dimensions column interesting. This column will tell you the size of the picture in pixels while the Size column tells you the size of the file in KB. These two values will help you determine if you should resize a picture before uploading it to your web page to minimize the time it takes the network to transfer the image from the server to the end user.

Similarly, music libraries have their own set of default metadata specifically to help you track the order of songs from an album, the album name, the composer, the length in minutes and seconds, and other information about the songs.

Well, that is it for the introduction. Next week I will explain a few reasons why metadata is so much more important in SharePoint than it was in Windows and then explore a way to convert from a nested folder document library structure to a metadata library structure.

C’ya then.

Jargon and TLAs

Today Richard Branson of the Virgin Group posted an entry on the use of jargon. In a nutshell he says, ‘it slows things down, confuses people, and causes them to lose interest.’ I could not agree more Richard. In fact, I would also expand this to include the use of TLAs (Three Letter Abbreviations).

When I started working for the school district, everyone was referring to the DOE. I could not understand how or why they would care about the Department of Energy. You see, I had previously worked for an energy consulting company and the DOE was referenced there was the Federal level Department of Energy, not the Florida Department of Education. Just for your information, at the Federal level, it is the Education Department. Confused yet?

Perhaps not as much as I have been reading documents with TLAs or other technical jargon about education in every sentence. While technical or career related jargon may aid in communicating ideas quickly amongst people who are all familiar with the terms and abbreviations used, it leaves the rest of us mere mortals confused and wondering just what is it that they are talking about. Even worse, TLAs can lead your brain down the wrong thought process.

During class I often asked my students what they think of first when I say: CIA. Here are some of the answers:

    Central Intelligence Agency

    Culinary Institute of America

    Christians in Action

    Chief Internal Auditor

    Cheers in Advance

    Cost Impact Analysis

In fact, the web site called The Free Dictionary ( lists over a hundred meanings for the CIA TLA.

Some people use jargon so much at work that when they leave work, they continue to use jargon, not to show off or just to abbreviate ‘common’ words or phrases, but because the jargon has become so much a part of their natural way of talking that they don’t even realize that they are doing it. Of course there are those that just use jargon and TLAs to appear smarter than their listeners or at least to impress them. My fiancée calls these people ‘intellectual bullies’. After all, who would you trust more, someone who could explain things clearly to you or one who expressed their ideas in jargon that went completely over your head?

One final example are the 24/7 lectures at the Ig Noble awards ceremony held annually at Sanders Theatre, Harvard University. The 24/7 lectures consist of a complete technical description of a research topic in 24 seconds. During this time, the use of jargon and TLAs is uncontrolled but often leaves one totally confused about the point of the topic or even what the topic is. This 24 second description is then followed by a clear, accurate summary that anyone can understand in seven words which usually clears up any confusion resulting from the first part.

So the point of this blog is simply this, “Use jargon amongst colleagues, not with everyone.”

C’ya next time.

Just Give Me the Steps

“Just give me the steps I need to know to get my job done. I don’t really care about why those are the steps or what else you can do with the software/tool/machine. I don’t care about your videos or your in-class training sessions. Just give me the steps in a nice neat printed list that I can use and let me get out of here.” Does that sound like one of your training classes? While I hope not, that attitude toward training has become increasingly prevalent.

We provide SharePoint training to what I sometimes call ‘reluctant’ users. These are users that were told they had to come to class because they are now responsible for their department or project sites. They never built a web site before in their lives and their only familiarity with on-line web sites is when they go to MSN to check the latest news, visit Facebook or go download their e-mail. Their approach to learning how to build web sites with web parts, content editing, approval workflows and page layout issues is a cross between fear, dread, and loathing. But now they sit in your class with arms crossed just waiting for time to pass. You can tell the ones pretty quickly. They are on their phone, not to text back what they are learning to their staff, but rather to play Candy Crush or Flappy Bird. Sometimes they are just sitting in the back of the room talking to their co-captives about where they can go eat when the class breaks for lunch.

Of course, not everyone falls into that category. Some people come to training sessions excited about what they are about to learn and anxious to implement their new skills when they leave. But those few people who really do not want to be there tend to ‘poison the well’. I tend to favor explaining the concepts behind how different aspects of the software system work so that people can adapt those concepts to new situations, often in ways that I would not have anticipated since I do not intimately know all aspects of their jobs. And these shining stars exist and it is for them that people who do training get the most job satisfaction.

However, over the years (and I have been teaching computer classes since the days of the TRS-80 and Apple II) there have always been those who want some kind of magic list of all the steps they need to do, complete with illustrations, for the very specific task they need to accomplish at the moment. Perhaps I’m a bit old-fashioned, but I consider this approach more of a one-on-one mentoring or custom training. I have even seen computer books written like cookbooks with step-by-step instructions on how to solve specific problems. However, these books do not help much when your problem falls outside of the narrow scope of the examples covered. Therefore, it is my opinion that it is not the goal of a general training class to provide a cookbook style set of instructions to using a programming language or a very generic piece of software such as Word, Excel, or even SharePoint.

I do recognize that times have change and different people learn in different ways. To that extent, I tend to encourage the use of a combination of classroom style training along with written documentation, whitepapers, websites, and videos. I also recognize that no matter what approach is used for an individual training class, those individuals that really do not want to be there will always find fault in something about the way the training was conducted. One might say the material covered was too general while the next person may say that it was too complex. Someone else may complain that the pace of covering the material was too fast for them to keep up while someone else may comment that they only got to the ‘good’ stuff by the end of the day. Some people protest when new functionality is introduced by using written documentation only because they are visual learners. Yet others at in-person class training lament that they could have gotten the same information from written instructions and would have been done in half the time and then they would have something to go back to later. We have even had people complain that videos of the training they are currently attending and can watch over and over again do not help them learn how to do their specific job.

I suppose the comment that bugs me the most is when people complement us on our training generally, but then follow that up with a ‘but’, such as, “The training was great, but it did not show me the exact steps I needed for my job.” So I sat back last night and wondered whatever happened to the need to learn fundamentals first so that the person could apply what they learned to any situation. You learn the fundamentals on how to drive so that you can drive on any road. Similarly, learning the fundamentals of how to play an instrument allows a musician to pick up any music sheet and learn to play that song. Of course, you could argue that merely knowing the fundamentals of a sport will not make you an Olympic medal winner. Simply knowing how to drive will not land you the poll position at the next Daytona 500. Nor will your ability to read sheet music make you the next pop super star. However, in all cases, a firm grasp of the fundamentals were a necessary starting point for those who do succeed.

So will I change the way I approach training software? Probably not substantially. I believe that approaching your job, if it is knowledge based, requires more than just the ability to follow a set of pre-defined steps. There typically is not enough time or infrastructure to support building the style of ‘cookbooks’ lists for each knowledge-based task that you might find in manufacturing tasks. In additional providing a combination of in-person training along with printed documentation and videos for every possible alternative is not always feasible, especially not for small organizations or teams. Maybe we need to place a greater emphasis on learning the fundamentals so we can apply that knowledge to whatever circumstance we find ourselves in. Perhaps that should also factor into our hiring practices by looking for people who show that they have been adaptable to changes and new systems in the past and have a demonstrated willingness to learn and succeed.

C’ya later.

Monitoring the Media for the Public Good?

A group named Reporters Without Borders recently ranked the United States all the way down at 46th out of 180 countries in a ranking of press freedom. Doesn’t this seem a little odd to you? Wasn’t there something in the Constitution about freedom of the press? No wait, that was in the Bill of Rights! In fact, the first amendment goes something like this:

“Congress shall make no law respecting an establishment of religion, or prohibiting the exercise thereof; or abridging the freedom of speech, or of the press; or the right of the people peacefully to assemble, and to petition the Government for a redress of grievances.”

If you just stop to think about it, there is a lot in those 44 words. I want to focus here on an even smaller portion of those 44 words related to “freedom of speech, or of the press”. It should be noted that the “press” referenced here no longer represents just newspapers or books, but has come to encompass many other types of media including more recently the Internet and blogs. But what does freedom of the press really mean? Does it mean you can say anything you want? Does it limit the opinions you can print or just guarantee the right to publish totally unbiased facts of the daily news?

A little over a third of a century ago in 1974, the Supreme Court ruled in the case of Miami Herald Publishing Co v. Tornillo that the government may not force newspapers to publish that which they do not desire to publish. While it does not specifically state anything about preventing them from publishing anything that they want to (coming from the opposite direction), it might seem like a logical extension to most.

On the other hand, the government already regulates some of the content of the media through the FCC, specifically in relation to radio and television broadcasting to restrict what it deems to be “indecent” material. What is “indecent” I guess they want us to believe that they will know it when they see it. But again, some may say that the definition of “indecent” has certainly changed over the last century with content being aired today that would never have been allowed just a few decades ago.

On a more personal level, the Ninth Circuit Court has recently ruled (2014) that journalists and bloggers are one and the same when it comes to protections under the First Amendment. So where is this going and why should it be the topic of this week’s rant?

Last year the FCC quietly announced that it will be monitoring newsrooms under the guise of determining how stories are selected, how stations set priorities, and the percent of news dedicated to different topics. Now I don’t know about you, but my management training says that you cannot control something unless you can measure it. So does the ability to measure something mean that you have the intent to control it? Now to be clear, nothing was said about the FCC ‘adjusting’ the content on the media they control or what those adjustments might be. Maybe they just want to measure things for the sake of measurement. After all, everyone knows that some media outlets tend to be more right wing while others tend to more left wing in the news they present and often the way that news is presented. The fact that we can identify them by their extremes merely says to me that the system is working. It allows for multiple points of view and lets the listener make up their own mind which to believe or even how much of each point of view they want to believe. Isn’t that what we want?

If instead, we let the FCC or other government agency begin to control the media so as to force a ‘more balanced approach’, would we lose the ability to explore all points of view? Who will define this balanced approach? And even if it does start out being totally 100% balanced, can we guarantee that it will stay that way over time? If all the news you hear begins to slant to one side or the other, would our media, through government control and oversight, be guilty of guiding the thoughts of its citizens to a singular point of view? No wait! Isn’t that what we accused the press of the Soviet Union during the cold war (and maybe even a little bit today) of doing to their own people? If a person does not know what else is going on outside of the media news they are given, how would they develop a basis to decide whether their situation was good or bad? If media control was bad during the communist era why would we think that media control in any country today would be any different?

Does allowing even a little control open the door to additional controls being added later? Consider that a war on a political system does not necessarily require a shooting war if one can win over the minds of people a little at a time by shifting what they think or how they think. In fact, the changes can be so slow and subtle over time that most people are not even aware of them until it is too late. In the meantime, the changes disguise their real intent behind such grad sounding themes like ‘This new recommendation to the media will protect you and your children from hearing any news that may be offensive or might upset them.’ After all, who is going to argue against providing greater safety and protections to our children. Well, it is something to think about.

C’ya next time, …. Maybe.


Get every new post delivered to your Inbox.

Join 84 other followers