Monday, December 28, 2009

Selecting Dates from a SharePoint Dropdown List

Assume the following Scenario:
You need a week list with the week's Monday date and you want it to be a validation (dropdown) list for a column in another list. SharePoint 2007 doesn't support dates in a dropdown selection list so dates won't appear as an option for validation (dropdown).

The work around is to create the column as a single line text. In order to show the 'dates' in the right order you need to keep an exact format like 2009/10/22. You can use Excel to easily prepare such a list and copy the data into the SharePoint list opened in a datasheet mode. However, Excel treat date cells like dates so extra steps are needed:
  • Type the first Monday's date of your choice. (Let's say in A1)
  • Enter in A2: =A1+7 to calculate the next Monday.
  • Drage down cell A2 as many cells as needed to get the full week list.
  • In B1 enter: =TEXT(A1,"YYYY/MM/DD"). This will transform the date into a string with the exact format we need.
  • Drag B1 down to the last filled in row. 
  • Copy the cells in B and use Paste Spacial to paste the values. This will get rid of the formulas and leave the values in column B.
  • Copy the range and paste it into the list in SharePint (opened in a datasheet mode)
Now you can lookup at this column as a dropdown (validation) in another list.

If you need, for example, to filter the list be the year, you can create a calculated column with the formula
=LEFT([The Week of:],4)  (assuming that the column mane is 'The Week of:" and it contains 4 characters).

Friday, December 25, 2009

Accessing a SharePoint Web Part page after editing it and receiving an error message

When an error occurs after editing a web part page, for example by adding a custom WP, sometimes you can't access the page anymore and therefore you can't to fix the problem by removing that WP.

Although not well documented, there is a solution to this issue. You just need to add the suffix ?contents=1 to the page's URL. This will present the maintenance area of that page. In the maintenance page you can exclude the WP and view the page again.

For example, if your URL is www.myDomain/mySubSite/myPage.aspx, it should look like:
www.myDomain/mySubSite/myPage.aspx?contents=1.