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.


Commuter Train Hits Some Bumps in the Tracks

Two weeks ago I talked about my experience riding the Sun Rail train to work here in Orlando, FL.  I was willing to give them a pass on the overcrowded conditions on the way home at night because we all knew that many people were just ridding the train during it’s free trial period to see what it was like and they just needed to get home for supper just like the rest of us working people.

Well that minor bump proved to be true and ridership this week is far less than it was last week. In fact, a friend of mine who got on the 5:28 northbound train from downtown said that there was still plenty of room and was able to find a seat at one of the tables on the second level. That is far better than the previous week when it was standing room only as the train stopped at the downtown station. This drop off in riders was expected. Hopefully though the number of riders will sustain the operation of the train. At least for the first few years the train is being supported with government dollars. But to be successful in the long run, it has to be able to support itself through riders.

Another bump (or perhaps two) were accidents that occurred during the first two weeks of operation. The first accident occurred when a truck pulling a trailer failed to completely clear the tracks at a road crossing. As the northbound train pulled around a curve immediately before the intersection, the train engineer saw the trailer hanging over the tracks and attempted to stop the train. However, even small commuter trains can take some distance to bring to a complete stop and the train did hit the trailer. Fortunately, no one was injured, not in the truck or the train. However, it did cause delays for people getting home as the accident was investigated. Ultimately, it was released that the engineer did everything he could to bring the train to a stop, but you cannot always avoid an accident caused by people who don’t release how long their extended vehicle with trailer really is.

Then only yesterday, Monday, there was another incident on the northbound train. This time a car apparently stalled while sitting on the tracks. Fortunately the driver was able to exit the vehicle for it was totaled by the train. However, the driver was still taken to the hospital to be checked as she fainted after seeing her Lexus destroyed (or maybe she just faintly because she realized how narrowly she escaped. I don’t know why the car was stalled over the tracks but I will say one thing. I’ve often seen people stop over railroad tracks during the peak of rush hour traffic when the traffic ahead of them stops for a red light. I guess they just don’t want to leave a space between them and the car in front of them (over the tracks) because they know someone will pull around them to fill in that space. Maybe it is because many of the crossing arms have malfunctioned in the past and have come down even when there was no train that some people tend to ignore the potential danger. However, I hope these two incidents will start people thinking twice about stopping over the tracks.

Of course the timing of the gate arms is another issue. There is a YouTube video that shows the gate arm at one downtown crossing coming down just a couple of seconds before the train goes by. That may be too short a time. On the other hand, I’ve also seen a train stopped at a station which sits in a block between two downtown streets and the gates on both roads remained down for the duration of the time the train was at the station, even when it was stopped to let passengers off and on. This led to significant car backups until the gates reopened. It even caused problems with some of the bus schedules.

While the timing of the gate issues are something that will undoubtedly work themselves out over the next several months, I’m more concerned about educating people about safety around railroad crossings. It would seem like it should be a simple thing, but too many people are in a hurry. Maybe the tracks should be above ground while in the downtown area. (Florida cannot really support a subway due to its high groundwater levels.) However, the decision to go the ‘cheaper’ route of using the old freight train tracks during the day has resulted in more trains crossing intersections than before and thus more delays and more chances to ‘catch’ vehicles extended over the tracks. This will be a more difficult problem to resolve. Perhaps if the current system is successful, they will consider an elevated track the next time they plan an upgrade through downtown or when they add additional lines. This would allow the trains to run more frequently and carry more passengers while not inconveniencing drivers. More frequent trains and perhaps a few more lines like an east/west line might encourage greater usage as well. For many today, getting to one of the current train stations is almost as difficult as driving from home to work in the first place. Therefore, it may take a combination of factors to give us a transportation system that people will really want.

In any case, I hope these few bumps in the tracks do not discourage riders early on so that public transportation as a combination of better bus and train service could make getting around town without a car feasible.

C’ya next time.

More Hidden Excel Gems

To follow up on last week’s discussion of hidden gems in Excel, this week I want to introduce to you something called Quick Analysis. According the Microsoft, Quick Analysis is a tool that lets you easily analyze your data using some of Excel’s built-in analysis tools such as charts, conditional formatting, pivot tables and sparklines. While the Quick Analysis shortcuts may not get you everything you need, they can get you started creating visualizations of your data, especially in regards to building charts.

So how do you use Quick Analysis? First you have to enter your data into a spreadsheet. Unlike last weeks tip which required that the data be converted into a ‘table’, this gem does not. In fact, after you enter the data into a spreadsheet, all you need to do is to highlight that portion of the data by dragging through the rows and columns that you want to analyze. When you have done this, an icon box for the Quick Analysis tools appears in the bottom right corner of the selected area as shown in the following figure.

This icon will appear when you release the mouse button after dragging through at least two cells. When you click on the icon, the following dialog box appears which shows five different analysis tools across the top of the dialog. Within each of these areas, you will see 1 or more analysis options in the lower portion of the dialog. The number of options will vary depending on the structure of your data and the area that you selected.

The figure below shows the options available to format the selected cells. This tool is essentially a shortcut to many of the more common features found in Conditional Formatting. For example, the first formatting tool displays data bars whose width defines a relative percentage of the total value of all the values in the selected column If you have more than one column selected, the relative width is based on the largest value in all of the columns. It is not calculated on a column by column basis. If you want to display relative widths based only on values within a column, you must select the data in each column separately and apply the Data Bars formatting tool to each column individually.

Similar rules apply to using the color scale formatting tool along with all of the others. You can apply more than one formatting to an individual range of cells. However, note that applying multiple formatting to a range of cells can lead to confusion. Therefore, the last icon (tool) in the formatting set is to clear all of the formatting from the selected cells.

Also keep in mind that even after you use one of the Quick Analysis tools, you can often go to the commands in the ribbon and provide further customizations. For example, suppose you select the Top 10% to highlight the top ten percent of the cells by value compared to the overall value. However, you really want to highlight the top 20%. These is no icon to do this in the Quick Analysis dialog. However, you can change the percent by following these steps:

From the Home ribbon, click on the Conditional Formatting button to open the dropdown menu. From this menu, select the last option in the list, Manage Rules.

This displays the Conditional Formatting Rules Manager as shown in the next image. Click on the rule that you want to modify keeping in mind that if you applied more than one formatting, each format option appears as a separate rule in this dialog. In this case, I want to click on the Top 10% rule to select it and then click Edit Rule to make changes to it.

In the Edit Formatting Rule dialog that appears next, look at the bottom half of the dialog. Note that you can change the rule to format either the top or the bottom group of values. You can also change the percent from the default 10% to 20% by simply replacing the value found in the dialog. You can even change the formatting colors if you do not like the default pink background with read characters.

After you have made your changes, click the OK button to apply them. You will be returned to the Conditional Formatting Rules Manager as shown below which now shows the title Top 20%.

The following figure shows what your data may look like with the top 20$ of the data conditionally formatted.

Another formatting tool is to display the selected data as a chart. Again, remember to select the data you want to appear in the chart first. It will show one or more possible charts it thinks are possible with the existing data. When you click on a chart type, a preview of the chart appears on the screen. The following chart shows a line graph created from US debt data (in 2005 dollars)

A slightly different grouping of the data allows more chart options as shown in the following image in which a clustered column chart was selected. Note that the preview appears automatically when you move your mouse over any of the chart options. The chart is not physically added to the spreadsheet until you click on the one of the chart options. Note also that many of the chart labels are either not defined or have been given generic names. After you add the chart to your spreadsheet, you can then select the various parts of the chart to edit what appears for example to add a chart title, x- or y- axis labels, a different legend, etc. by using the commands in the Design and Format ribbon of the Chart Tools that now becomes available at the top of the screen when the chart is selected.

You can even add sparklines to your spreadsheet. A sparkline is a miniature graph of the data and can either be a line, column or a win/loss chart. By default, Quick Analysis only creates sparklines for horizontal data series.

If you use the commands in Insert ribbon, you can create sparklines for a vertical series of data as well as shown in the image below.

Another interesting feature is the ability to create a pivot table from the data using Quick Analysis. I’m going to save a detailed discussion of this shortcut for a future blog. In the meantime, please try the different Quick Analysis tools the next time you work within an Excel Spreadsheet.

One last trick, when you select any range of cells, the average, sum and count of the values in that range will appear in the status bar at the bottom of the Excel window as shown in the following figure. So if all you need is a quick average or sum of values, there is no need to do anything other can just select the cells you want.

C’ya next time.



By sharepointmike Posted in Excel

Let Excel Parse For You

I’m a little short of time this week because I will be in Jacksonville by the time you read this presenting at the Jacksonville SQL Saturday, but I wanted to give you something to chew on this weekend. So I found this interesting feature of Excel which appears to have been available since Excel 2007, but is probably know by very few of you.

Have you ever had a list of data that you needed to parse? For example, suppose you have a list of email addresses as shown in the following figure from which you wanted to extract the person’s first name.

I suppose you could begin in the first row and enter ‘Mike’, then enter ‘Bill’ in the second row, ‘William’ in the third row, etc. to enter the first name of each person manually. You might also use a combination of Excel functions to find the period that separates the first and last names and then take all of the characters to the left of the period. To do that, you might use a formula like the following:

= LEFT(A2, SEARCH(“.”,A2)-1)

But what you may not know is that there is an easier way.

First, make sure that your data is a table with or without a header.  Then simply enter the first name of the person in the first row; ‘Mike’ and then begin to enter the first name of the person in the second row, “B”.

Notice that in ghost print (light grey), you can see that Excel has already figured out the first names of everyone in the list. Simply press the ENTER key to complete your entry (do not finish typing Bill’s name). Excel fills in the rest of the rows with the person’s first name.

Cool? What if we add another column for last name

This appears to work as well. Excel appears to be able to determine which delimiters surrounded the portion of the name we want to extract from the email address and is able to reproduce that across the rest of the rows.

Similarly, we can parse the rest of the email address to get the following columns.

That’s great, but I had a very specific format to the username. It was:

<<first name>>.<<last name>>

As you can see, the first name was separated from the last name by a period. What would happen if some of the names were separated by an underscore instead of a period? Of course I had to try this, but the initial results were not promising as you can see in the following figure.

I changed two of the addresses further down the list to use underscores instead of periods and when Excel tried to parse the first name, it picked up everything to the left of the first period which was found just before the top level domain portion of the address. My first thought was that this method may not suit my needs. So I tried a case in which the first two names used underscores between the first and last name as in:

<<First Name>>_<<Last Name>>

I was surprised that this worked for both names separated by periods and names separated by underscores (Jason) and periods (William, Sara, Fran, Annie, and Lisa).

I then tried the following case in which the first two names were separate first by an underscore and then by a period.

As you can see, the first name for Sam did not stop at the period, but continued on to an underscore I added between the domain server and the top level domain.  (Ok, don’t tell me in a comment that putting an underscore before the top level domain is not a valid format for an email address.  I know that, but was merely testing different patterns for parsing.)

Now I was confused. It appears that the pattern is based on the first row defining the separators which will have a preference for the separate used in that first row. For example, if I change the separator on this last example from an underscore to a period, Sam is parsed correctly, but Kyle is not.

This confirms to me that the separator in the first row defines a preference no matter where it is found in subsequent strings. However, if that separator is not found in the subsequent string, the first non alpha character will be used to parse the string as in the case of Jill above.

Of course you can use this technique to parse other column information. For example, I used it to parse address information as in the following figure.

However, I did have a problem pulling out the state name as you can see. I have not yet determined why yet other than the space character appears in many other places in the address and Excel may not be able to develop an appropriate formula to do this.  On the other hand, perhaps someone out there reading this can come up with an elegant solution and share it with the rest of us.

In the meantime, hope you were able to make it to Jacksonville. I’ll c’ya next week.

By sharepointmike Posted in Excel

Riding the Rails to Work

This morning I rode the new SunRail train to work. Our light rail commuter train just went into operation here in Orlando last week and for the next week or so is free. So why not try it? Right? If you have ever been to any major city with a great rail and bus system, you know the value of these types of system. For example, in New York City I’ve been able to get from the airports to downtown Manhattan and even get around town exclusively via the bus and train system. In Seattle, the light rail was able to get me from the airport to downtown where I was staying for less than $3.00. Taxi riders paid as much as $40. I even met a Microsoft employee who works in downtown Seattle who takes the light rail to and from work daily. So when they announced plans for a light rail in Orlando, I knew right from the start that I would have to try it.

Currently, each train only has two passenger cars, but each car has two levels. The lower level gets used mostly by people who bring their bikes to either get to the station from home or from the station to work. On the upper level seating is very comfortable and is arranged in groups of four seats. Some groups have a small table between them. The trains are very well lit. In fact, some people on the early morning train have asked if the lights could be dimmed so they could sleep, but that is not an option.

On the other hand, the trains all support free WiFi and even power outlets if you need them. So I pulled out my iPad and was able to get through both my work and personal email on the ride into work. You cannot do that in your car or at least you should not be doing that unless you are carpooling and someone else is driving. Another thing you cannot do in your car on the way to work is use the restroom facilities which you can do on the train (although I did not try that), but still, it is just one more reason to consider taking the train. Oh, and texting while you ride is definitely allowed on the train.

But the best reason to take the train was that: “There is no traffic!” One of the major corridors through Orlando from the north to the south is the multi-lane I-4. Even at 6 AM, the traffic can be quite heavy although it usually moves well unless there is an accident. In the evening, going home is significantly worse. But even when the traffic moves, you still have to contend with the Type A personality drivers who have hang onto your back bumper until they can find a small gap in the lane next to you to slip around and get a car or two ahead. And no longer will I have to worry about those crazy motorcyclists pulling wheelies at 65 mph going down the highway and weaving in between cars.

After the free period ends, the cost of riding the train will be $2.00 for a one way ticket plus $1.00 for every county you cross. However, there will also be round trip tickets, 7-day passes, 30-day passes, and even an annual pass. Discounts of 50% are offered to seniors, students, and disabled individuals. There is even an extra 10% bonus for buying a pre-paid card with $10 to $300 on it. You need to consider how much you spend on gas and wear and tear on your car. Maybe you even pay to park downtown. If most of your driving is to and from work, you may quality for a reduction in your car insurance by taking the train. But best of all you can arrive at work or at home at the end of day without having to deal with the stress of driving. And that last factor will only become more important when they start the new I-4 widening project later this year. Construction during the widening will make an already bad commute even worse in the interim. And for those environmentally conscious folks, every train rider is one less car on the road pumping out carbon dioxide.

Yes, I know that Orlando still does not have the infrastructure for public transportation like New York City, Philadelphia, London, and many other large cities, but this could be a step in the right direction. Kudos to Orlando for taking that step.

C’ya next time.

Lists and Libraries on Public Web Sites – Part 2


Last time we looked at the problem of referencing lists and libraries directly on a public facing web site after adding a link to the Quick Access menu when creating the list or library. However, that is not the only way you can get into trouble.

On a public facing site, it is possible to display the contents of a list or library by displaying a view of the library or list. To do this, all you need to do is open an existing page or create a new page, select a web part zone where you want to display the list or library and click Add a Web Part.

The Insert ribbon that appears includes a panel beneath the ribbon that shows the web parts by category. You can add any of these web parts to the zone.

In the first column of this panel (named Categories) select the Lists and Libraries category. This displays a list in the column Web Parts to the immediate right that shows all lists and libraries in the current site. This section supports two columns of eight items each. If you have more than sixteen lists and/or libraries in the site, navigation arrows at the bottom of this section (greyed out in the above image0 allow you to scroll right and left to view additional groups of up to 8 lists or libraries with each click. Let’s assume that I want to display the Documents library. I need only select it so it is highlighted and then click the Add button in the lower right corner. As you can see, this adds a view of the library to the page.

Once the page has been published (assuming you have publishing turned on for your public facing sites), an anonymous user might open the page, see a Microsoft Word document, as shown above, and attempt to click on it. As an anonymous user, they do not have rights to edit a document. If the library is set up to download the document to the client, SharePoint displays a security dialog such as the one in the next image prompting the user to log in before it allows the document to be downloaded.

But anonymous users do not have user names and passwords. Therefore, all they can do is cancel the action. One solution might be to also upload a PDF version of the document. This is relatively easy since all Microsoft Office applications can output a PDF version of their documents.

Even with a PDF, SharePoint needs to download that PDF to the client. But since a PDF is not considered to be editable, there is no security warning. Instead, it considers a PDF to be an executable file. Therefore, the user gets a dialog warning them that some files could harm their computer when they are executed.

Of course, if you go this route, you may want to clean up the library display to use a view that filters on only the PDF files in the library. However, even if you do that, there is still an issue with the dropdown menu associated with the item.

Clicking on the menu dropdown as an anonymous user also opens a Windows Security dialog:

So what can you do to display items in a document library that people can open? One method that I have found effective is to use the Content Query Web Part to display the items from a library. Using this web part, you can filter (such as on PDF files) and sort on one or more columns to create a list of links to your documents. But even this method could still prompt you with the message that you are downloading a potentially harmful file to your local computer.

One solution we implement to solve this problem was to install Microsoft Office Web Apps on our SharePoint servers. Then we would open the library, open the Library ribbon, and click on Library Settings. On the Library Settings page under the General Settings group, click on Advanced Settings.

Then in the Library Settings, we turned on Use the Server Default which is to open in the browser (we could also use Open in the Browser). This will force Office Web Apps to try to open the document, even Microsoft Word documents can be directly opened this way without prompting the user for a username and password. PDF files will also open in the browser without a warning about the file potentially harming your computer.

To display a list, we found a slightly different solution. The problem with lists is both one of clicking on the title (default) column of the displayed list and clicking on the dropdown menu associated with the title. However, the list problem can be solved most elegantly by simply changing the columns displayed in the list view. By default, many people select the Title column (renamed in the image below as OpenLabID) to appear in the list. Note that in parenthesis that this column is linked to the item with an edit menu.


Simply replace this column with another version of the column named Title (or OpenLabID in this example) that does not link to the edit menu.

Now when you display the list to users who do not have edit rights, the viewers can see the data without having to worry about whether they have rights or not.

Hope this gets you through a few problems.


C’ya next time.