Release Calculation

In our Department, we do a release twice a month, noted as "release a" and "release b". We have several tasks that go into each release, and several systems in the department. We need a way to track which tasks go into each release. So we have a task list.

This is where the title field becomes important. After a few releases, the lookup list becomes unbearable because several people on the team are entering releases into the release list but not creating any sort of standard title so that they are easy to find (and sorted properly) in the release lookup field in the task list.

You know how people are. People who enter information into lists sometimes have crazy ideas about how the 'title' field should be populated. Sometimes, strict standards are followed by everyone except that crazy lady who has a full jar of mayonnaise on her desk and a spoon (---shudder---), who enters crazy shit into your forms. This post will help you manage that crazyness so that some consistency is achieved.

I created a release list (just a simple custom list with a start date and a system name and a title field.) We go into the task list and create a lookup field so that we can assign each task into a release.

In the release list, I created a crazy-ass calculated field to calculate the title:

="["&System&" "&YEAR([Start Date])&"."&IF(MONTH([Start Date])<10,"0"&MONTH([Start Date]),MONTH([Start Date]))&"."&IF(DAY([Start Date])<15,"a","b")&"] "&System&" "&TEXT([Start Date],"mmm")&" "&YEAR([Start Date])&" "&IF(DAY([Start Date])<15,"First","Second")&" Release"

This gives us data that looks like this:

[Portal 2009.02.a] Portal Feb 2009 First Release
[Salesforce 2009.02.b] Salesforce Feb 2009 Second Release
[Portal 2009.02.b] Portal Feb 2009 Second Release
etc...

Now, we look at the fields from the task lookup field:
[Portal 2009.02.a] Portal Feb 2009 First Release
[Portal 2009.02.b] Portal Feb 2009 Second Release
[Salesforce 2009.02.b] Salesforce Feb 2009 Second Release
etc... all in the proper order!

Now, I create a SPD workflow that changes the title:

Attach the workflow to the release list.
Have the workflow start when a new item is created or changed.
Condition: IF TITLE IS NOT EQUAL TO CALCULATEDTITLE
VERY IMPORTANT: we don't want to create an endless loop.
Action: SET TITLE to RELEASE CALENDAR:CALCULATEDTITLE

All set!

SharePoint Limitations

When examining the SharePoint limits, you’ll see references to several terms that are specific to SharePoint.

In SharePoint, a web application refers to an Internet Information Server site that has been extended with SharePoint.

Each web application may contain one or more site collections. A site collection is typically a complete intranet, extranet, or Internet implementation.

A site collection is made up of one or more sites. A site is a dedicated section of the site collection for team collaboration, business intelligence reporting, records management, or the like.

Sites contain lists and documents and may have subsites underneath them.

The 2,000 rule: because stored procedure calls to SQL Server slow down as you reach 2,000 items, have less than that in a view on a List. Less than 200 ideally to have optimum performance.

Limits:
Site Collections in an Application:
50,000

Sites in a Site Collection:
250,000

Subsites in a Site:
2,000

Lists on a Site:
2,000

Items in a List:
10,000,000

Documents in a Library:
2,000,000

Documents in a Folder:
2,000

Maximum Document File Size:
2GB

Documents in an Index:
50,000,000

Search Scopes:
1,000

User Profiles:
5,000,000

Have less than 2000 AD users or groups in a SharePoint Group for the same reason. An AD group in a SharePoint group counts as one item, but the first time a user edits content in a site collection, they are added to the user list for that site collection. The best way to avoid this problem is to have lots of site collections so the users and groups are more spread out.

The bottom line is you can have as many readers as you like on SharePoint site collections as you like, but if you get to between 1,500 and 2,000 contributors (to reach that 64k limit) you will get errors.

More detail here: http://weblogs.asp.net/erobillard/archive/2008/09/11/sharepoint-security-hard-limits-and-recommended-practices.aspx

List all Closed Web Parts on a Page

Closed web part gettin' you down?

Append "?ToolPaneView=2" to your page URL to force "Edit Page".

Append "?ToolPaneView=3" to your page URL to force "Search Web-Part Zone".

Append "?DisplayMode=Design" to your page URL to force "Design Bar".

Append "?ControlMode=Edit" to your page URL to force "Web Part Zone Edit".

Last and most favorite: Append "?contents=1" to your page URL to force "Web Parts Maintenance". Remove the offending web parts and increase the load times of your page(s). This allows you to manage those closed web parts that should have been deleted.

Space - The final frontier

How to render a space character in XSLT:

<xsl:text xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime" ddwrt:nbsp-preserve="yes" disable-output-escaping="yes">&amp;nbsp;</xsl:text>

That is all.

Top 10 Documents

Each month, I publish usage reports. I know, I can automate these, but for now, I will assume that you are in the same boat as me (cannot do development, or use SharePoint Designer).

1. Go into the usage reports / Top Pages section of your site. (https://[YOURSERVER]/_layouts/SpUsageWebTopPages.aspx).
2. Export to Excel
3. Go through and remove all of the pages you want to exclude from your report. I exclude images and aspx pages. Assuming your URL is in column A, and Column C is blank, You can use the Excel formula in column C to sort and categorize the documents by type: "=RIGHT(A2,4)". Autofill the entire column C with the formula. This gives the last four characters of the URL. Sort by this, and remove the rows you want to exclude from the report.
4. Enter the formula into column D: =SUBSTITUTE(RIGHT(A2,LEN(A2)-33), "/", " : ") and autofill down the column. We are removing the first 33 characters of the URL; see that number 33 there, that is the name of my server and document library and "https:". We are also substituting the "/" with SPACE COLON SPACE characters to represent a friendly name for the URL folder structure. You can get really creative and put: =SUBSTITUTE(SUBSTITUTE(RIGHT(A2,LEN(A2)-33), "/", " : "), ".", " [") & "]" instead, which will have the friendly name and document type neatly bracketed for you: like this: "[doc]".
5. Now, in column E, we want to build the friendly looking URL. Enter the formula: "=HYPERLINK(A2,D2)". Autofill Column E with that URL.
6. Sort the list by the hit count, and eliminate all but the top 10.
7. Now, you can paste the URL (column E) and Hit count into your Links list using datasheet view and you will have a friendly link to the top 100 documents in your library or site.

I hope that helps you!

File and Folder Naming Rules

File Naming Rules

• Invalid characters: ” # % & * : < > ? \ / { } ~ 
• Cannot be longer than 128 characters
• Cannot use the period character consecutively in the middle of a file name
• Cannot use the period character at the end of a file name
• Cannot start a file name with the period character

Folder Naming Rules

• Cannot use the following characters in a folder or server name: ~ # % & * { } \ : < > ? / ”
• Cannot be longer than 128 characters
• Cannot use the period character consecutively in the middle of a folder name
• Cannot use the period character at the end of a folder name
• Cannot start a folder name with the period character

That is all.

Calculated Default Date with Time

The built in Date Time Column type for SharePoint 2007 lists has a problem that I was surprised to find out about.

When you create a new Date Time column, you are given the option to set the default value to Today's Date. This works fine if all you care about is the date, but if you want the time to default to the current time ... Every time you create a new list item, the time for that column will always be defaulted to 12:00 AM.

To insert a default date and time, use the TIME function in the "Default value:" "Calculated Value:" field:

To insert today's date with a 9:00 AM time:

"=Today+TIME(9,0,0)"

TIME (HOUR, MINUTE, SECOND)

Usually, I have a task list, and I have people from all over the company entering items for me to do. In order to get to them in a timely fashion, I want the default 'Start Date' to be three days in the future, at the beginning of the day so that I can look over the request and prioritize it as I see fit:

"=3+(Today+TIME(8,0,0))"

In addition, I want to default the due date to 90 days from now, at 6:00 PM (basically, End Of Day):

"=90+(Today+TIME(18,0,0))"

The TIME function is on 24-hour (or military) time. Just add 12 to all PM times, and it's pretty easy to figure out. 0,0,0 is Midnight.

This is very useful with the task-type list, because if the due date is 'today', then your task is already overdue because 'today' means 'this morning at 12:00AM!' which has already passed!

From a SharePoint Power User to a Developer

Please do not deploy random code to the server. A MOSS 2007 or WSS 3.0 box is not a plaything.

Wrap your widget up as a solution or a feature so that it can be managed and isn't site specific. Wrapping your solution up as a feature will enable you (or someone other than you) to quickly deploy your widget into multiple environments.

Also, please do not hard code user messages. Instead, deploy your widget with a standard page layout, so that the business owner can add a Content Editor Web-Part (evil) or use a field control so that the BUSINESS OWNER can decide what the text is going to be, and changes do not require developers to come in and 'fix' a typo!

Use the standard CSS classes that come with WSS 3.0. Do not hard-code inline styles in your code! We do not want to hire developers to come in and change the color of something from #c98323 to #c93! We have themes and style templates, and they all have standard CSS classes for you to use that will made your widget fit perfectly into the site theme!

Thank You!

Fiscal Year and Fiscal Quarter

We end the fiscal year on May 31. Not only is it offset by some months compared to the calendar year, the number of months isn't divisible by 3 (a quarter), so calculating which quarter of the year a particular date is in becomes tiresome.

In this post, we will create a new calculated column in a list.

Calculating the Fiscal Year is kinda easy:

=IF(MONTH([Due Date])<6,YEAR([Due Date]),YEAR([Due Date])+1)

We are using an IF statement to decide whether or not the month number is less than 6 (June), and if not, add a year to it. My fiscal year begins on June 1st.

Calculating the Fiscal Month is a little more difficult:

="FQ 0"&MOD(CEILING(22+MONTH(date)-5-1,3)/3,4)+1

I can't really explain how that formula works, I found it online:

I Love Excel!: Calculate Calendar Quarter of Fiscal Quarter From Date

Put them together, and you can get a sortable field you can use in views:

=IF(MONTH(date)<6, YEAR(date), YEAR(date)+1)&" Q
0" & MOD( CEILING(22+MONTH( date)-5-1,3)/3,4)+1

This will give you the quarter number and the year of that quarter. Substitute the "[date]" in the formula above with whichever date column you want to calculate on.

So, "Due Date", a common date field in tasks and issues lists would be calculated as:

=IF(MONTH([Due Date])<6,YEAR([Due Date]),YEAR([Due Date])+1)&" Q
0"&MOD(CEILING(22+MONTH([Due Date])-5-1,3)/3,4)+1

Please note that this formula is based on a fiscal year beginning in June. If you have a different fiscal starting month, you can replace the '5' in my formula with your fiscal month minus one. If your fiscal year begins in January, then what are you looking at this post for? Your fiscal year is the same as the calendar year!

Have fun!