SharePoint List Data Calculations

SharePoint offers column calculations. These can be complex or simple.

Microsoft has an explanation on Data Calculations here*.*
Microsoft also has a list of Formulas and Functions here*.*
Microsoft also has a list of how to use a calculated list value here*.*
Here is an EXTENSIVE list of functions that can be used

Here is an example of a formula that WILL NOT work:
=IF(Column1<=Column2, "OK", "Not OK")

Here is an example of a formula that WILL work:
=IF(Column1<=Column2*;* "OK"; "Not OK") (Note the semicolons instead of the commas!)

Testing Multiple-Choice Value

In this example, I wanted to test a column that was of type multiple-choice. If it was one of two specific values, I wanted this field to be equal to 1. If it was another choices, I wanted it to be equal to 0:
=IF(OR([Column]="Choice 1", [Column] ="Choice 2"), 1, 0)

In this example, I wanted to test options for a column, setting the calculated value based on options:
=IF([VARIABLE]="Open","Open",IF([VARIABLE]="Unresolved","Substantially Complete", IF([VARIABLE]="Resolved and Closed","Substantially Complete","Work in Progress")))
This basically says, take the Variable drop-down I am working with and,

• If it says "Open", set this field to "Open"
• If it says "Unresolved", set this field to "Substantially Complete"
• If it says "Resolved and Closed", set this field to Substantially Complete
• If it has any other value, set it to "Work in Progress"

Calculated Field for Dates

I had a need to group a list by dates. I came up with 3 options... here's what I did. Oh, and you needf to replace "VARIABLE" with the actual date field you want to calculate off of:

Show the week in the year - “114”

=INT((VARIABLE-DATE(2007,1,1))/7)

Show the year and month - “2009 – 03 (March 2009)”

=TEXT(VARIABLE,"yyyy - ")&TEXT(VARIABLE,"mm")&TEXT(Date," (mmmm")&TEXT(VARIABLE," yyyy)")

Show the year, month and week in that month - “2009 – 03 (March wk 01)”

=TEXT(VARIABLE,"yyyy - ")&TEXT(VARIABLE,"mm")&TEXT(VARIABLE," (mmmm wk ")& IF(TEXT(VARIABLE,"dd")<"08","1)",IF(TEXT(VARIABLE,"dd")<"15","2)", IF(TEXT(VARIABLE,"dd")<"22","3)",”4)”)))

Calculated Field for Text

Suppose you have a column of text called "My Column" and you wanted to pull the first letter of the value for that field. You can do that by creating a calculated column with this as the calculation:

=Left(My Column,1)

IE Crashes When Opening Office Docs

Found a user's machine where whenever they opened any Office document IE crashed. This seemed to work:

(not specifically Word) Resolution:
Step 1) Close all Internet Explorer and Office programs/windows
Step 2) Delete this file: C:\Program Files\Microsoft Office\Office 12\OWSSUPP.DLL
Step 3) Run the Microsoft Office Diagnostic located in Start --> Programs --> Microsoft Office --> Microsoft Office Tools --> Microsoft Office Diagnostic

Step 4) It should find 1 error and have fixed it. Now you can open your programs back up and try the SharePoint site. This should fix the problem.

Add or remove the View All Site Content link

Because of customizations to our master page and theme our "View All Site Content" does not show up any more. This is not a problem for Publishing sites since they automatically add it to the "Site Actions" drop-down. However, this was a problem for non-Publishing pages.

I used this add-on to add it back in for the collaboration sites

Allow more than 50 pages to show up in left-hand Navigation

We are using Publishing in our MOSS environment for our Intranet. Some of our groups are creating manuals using the publishing pages and have found that the Navigation option of automatically show all pages has a limit of 50. After the 51st page has been created and published they will not show up. We wanted to increase this number.

As described here, hereand here, I edited the web.config file on the FRONT END web server. Once saved, the changes took effect immediately. Add the part in bold below:

 <siteMap ...>  
 <providers>  
  <add name="GlobalNavSiteMapProvider" description="CMS provider for Global navigation" type="Microsoft.SharePoint.Publishing.Navigation.PortalSiteMapProvider, Microsoft.SharePoint.Publishing, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" NavigationType="Global" EncodeOutput="true" DynamicChildLimit="0"  
  />  
  <add name="CombinedNavSiteMapProvider" description="CMS provider for Combined navigation" type="Microsoft.SharePoint.Publishing.Navigation.PortalSiteMapProvider, Microsoft.SharePoint.Publishing, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" NavigationType="Combined" EncodeOutput="true" DynamicChildLimit="0"  
  />  
  <add name="CurrentNavSiteMapProvider" description="CMS provider for Current navigation" type="Microsoft.SharePoint.Publishing.Navigation.PortalSiteMapProvider, Microsoft.SharePoint.Publishing, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" NavigationType="Current" EncodeOutput="true" DynamicChildLimit="0"  
  />  
  <add name="CurrentNavSiteMapProviderNoEncode" description="CMS provider for Current navigation, no encoding of output" type="Microsoft.SharePoint.Publishing.Navigation.PortalSiteMapProvider, Microsoft.SharePoint.Publishing, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" NavigationType="Current" EncodeOutput="false" DynamicChildLimit="0"  
  />  
 </providers>  
 </siteMap>  

Remove warning / yellow exclamation on profile pages

For our application we didn't want the warning / yellow exclamation to appear on user Profile Pages. As described on this page, to remove the exclamation you need to,

1. Log into the front-end server
2. Navigate to C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\TEMPLATE\LAYOUTS\
3. Backup the editprofile.aspx
4. Edit the editprofile.aspx and do a search for SPSWC:ProfileEditor id="ProfileEditor" id="ProfileEditor" runat="server"
5. You need to add ShowWarningIcon="False" into that sepcific tag so that it looks like this: SPSWC:ProfileEditor id="ProfileEditor" id="ProfileEditor" ShowWarningIcon="False" runat="server"
6. Save

Removing / hiding the "workspace" option from Calendar

Currently, I have disabled the "Workspace" check box from all Calendar events within SharePoint. I did this by doing the following:

• On the server, navigate to C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\FEATURES\EventsList\Events\
• Create a backup of schema.xml (I backed it up as schema.xml.backup)
• Open schema.xml and search for the first of two sections: Name="WorkspaceLink". Within the section that looks like similar to this:

Field ID="{881eac4a-55a5-48b6-a28e-8329d7486120}" Type="URL" Name="Workspace" DisplayName="$Resources:core,WorkspaceUrl;" Hidden="TRUE" Sealed="TRUE" .............

• Add ShowInNewForm="FALSE" ShowInEditForm="FALSE" after Hidden="TRUE"
• Find the next section by searching for Name="Workspace. Within the section that looks similar to this:

Field ID="{881eac4a-55a5-48b6-a28e-8329d7486120}" Type="URL" Name="Workspace" DisplayName="$Resources:core,WorkspaceUrl;" Hidden="TRUE" Sealed="TRUE" .............

• Add ShowInNewForm="FALSE" ShowInEditForm="FALSE"after Hidden="TRUE"
• Save the file.
• Perform an IIS restart by clicking the Start menu, click Run, type CMD. This will bring up the command-line window. Type iisreset and press Enter

Access Checker

I downloaded and installed this from CodePlex. The direct site to the install is here: http://www.codeplex.com/AccessChecker

This is a great tool that easily lets you check on the access for a specific users.