Point Deep

Mundeep's Tech Blog

Sharepoint 2007 Calculated Fields

Posted by mundeep on October 27, 2010

This is just a quick brain dump of some research into Calculated Field formulas for MOSS that i’ve had sitting as a Draft for a while.

UPDATE: This appears to be the most comprehensive offical guide on Formulas and Functions that can be used:


Posted in Sharepoint | Leave a Comment »

Updating the Created Date of a Document or List Item Programmatically

Posted by mundeep on May 22, 2009

A common tasks developers are required to do with Sharepoint is migrated documents from either legacy systems or older Sharepoint sites into a document library. There are many ways to do this documented out there (most commonly using the Files.Add method of the API), but one of the common requirements during this ‘migration’ is to retain the Created Date or Created By fields. Sowmyan’s blog has a good description of how to set the Created By/Modified By user fields here.

Updating the Created Date of a document or list item in Sharepoint is even easier, simply set the “Created” field of the item to the value you wish to set. For example if using the Files.Add methods in the API (as described by Dave Hunter) then the following code snippet will update the Created Date:

// add the file   
SPFile file = docLib.RootFolder.Files.Add(newFileName, inputStream);   
// get the list item for that file   
SPItem item = file.Item;   
//Set the Created Date
item["Created"] = "2009-02-26 15:00:00";

Posted in .NET, Sharepoint | Tagged: , , , | Leave a Comment »

InfoPath 2007 Custom Field Validation (Programmatic)

Posted by mundeep on May 20, 2009

Adding custom (programmatic) field validation to a form in InfoPath 2007 is quite easy. Firstly make sure you have set your programming language of choice by going to the Tools menu then “Form Options” -> “Programming”, and then selecting your preferred language under “Form template code language”. For this example i am going to be using C#.

Back in your form design window right-click on the field you wish to validate, and select “Programming” -> “Validating Event”. This will open a “Visual Studio Tools for Applications” (VSTA) window, and generate the following code snippet:

        public void myFieldName_Validating(object sender, XmlValidatingEventArgs e)
            // Write your code here.

Obviously this code is not doing any validation yet, we just need to insert our validation code in place of the “your code here” comment. Let us try a simple validation of ensure the data entered in the field is at least 3 characters long with the following code.

public void myFieldName_Validating(object sender, XmlValidatingEventArgs e) {
//Check the Length of the NewValue of the field
if (e.NewValue.Length < 3) { //Report the validation error back to the client on the form e.ReportError(e.Site, false, "Please Enter a valid MyField"); } } [/sourcecode] As mentioned in the comments e.NewValue retrieves the 'New' value that is being entered/inserted into the field before it has been set and e.ReportError sends any validation errors back to the form for the client (read the MSDN article for details on the parameters of ReportError).

Unfortunately there is a minor issue with this basic piece of code and that is due to the fact that the validating event also gets fired during the loading of the form and as shown in this kb article we need to check the XmlOperation that is being performed when the validation is fired. This means updating our code to the following.

public void myFieldName_Validating(object sender, XmlValidatingEventArgs e) {
//Ensure validation is only done when inserting or changing a value
if (e.Operation == XmlOperation.Insert || e.Operation == XmlOperation.ValueChange) {
//Check the Length of the NewValue of the field
if (e.NewValue.Length < 3) { //Report the validation error back to the client on the form e.ReportError(e.Site, false, "Please Enter a valid MyField"); } } } [/sourcecode] Save your code changes and while still in VSTA build the project to ensure you don't have any coding errors (Ctrl+Shift+B, or from the menu "Build" -> "Build 'MyFormName'"). If you get a "Build succeeded" message in the status bar you can close the VSTA window and then preview your form to see your validation in action.

Posted in .NET, Office | Tagged: , , , , | 1 Comment »

Checking if a SPFolder Exists

Posted by mundeep on February 24, 2009

Ran into a colleague’s code that was incorrectly trying to check if a folder existed. It was something like:

private bool CheckFolderExists(SPWeb parentWeb, string folderName) {
    SPFolder folder = parentWeb.GetFolder(folderName);
    if (folder == null) {
        return false;
    else {
         return true;

however this always returns an actual SPFolder object, and the correct way is to check the Exists property of the returned object ie:

private bool CheckFolderExists(SPWeb parentWeb, string folderName) {
    SPFolder folder = parentWeb.GetFolder(folderName);
    return folder.Exists;

NB: Yes, this post is almost identical to my earlier one about checking if an SPWeb object exists.

Posted in .NET, Sharepoint | Tagged: , , | 10 Comments »

InfoPath Forms Services is not turned on

Posted by mundeep on February 17, 2009

While trying to deploy an InfoPath Form to sharepoint via stsadm commands i received the error:

InfoPath Forms Services is not turned on.

Initially thinking that the Enterprise features where not enabled in the Site Collection i was deploying to i tried activating the “Office SharePoint Server Enterprise Site Collection features”, however that did not make a difference.

Turns out the problem was that the account i was trying to deploy under (my own) did not have sufficient permissions (to what i am not sure – but i am guessing the configuration database), running the deployment under an account with more priveleges solved the problem.

Summary: If you get the above error make sure to try your deployment/stsadm commands under an account with higher priveleges (most likely access to the config database).

Posted in Sharepoint | Tagged: , , , | Leave a Comment »

Converting InfoPath Forms into Visual Studio Projects

Posted by mundeep on February 16, 2009

I was recently trying to converting some InfoPath froms that had been created as per default in the InfoPath client application (and using Visual Studio Tools for Applications for Code-Behind) into a Visual Studio project to be opened in Visual Stdio 2005 as per the MSDN HowTo. However when i reached the last step and opened the masnifest.xsf it would just me show me the xml contents of the file and not the visual form editing GUI that you see in the InfoPath client. Solution was to install Visual Studio 2005 Tools for Office Second Edition (VSTO 2005 SE) and then reopen the project.

Posted in .NET, Sharepoint | Tagged: , , , , | Leave a Comment »

sgen.exe exited with code 1

Posted by mundeep on February 12, 2009

While developing a Sharepoint solution using WSPBuilder and Visual Studio 2005 i noticed that i would get the following error when trying to compile a Release build (on further examination i noticed that it would occur whenever i switched between release & debug builds):

“sgen.exe exited with code 1”

After doing some research i found a several different suggested solutions:

  1. Remove and re-add the project from the solution as per this forum post.
  2. Turn off the “Generate serialization assembly” option in the Build tab of Project Properties see this blogpost and this one.
  3. Remove the previous version of the assembly from the GAC as per this blogpost.

To determine which solution i should use required some more research on sgen.exe does. As per the MSDN article:

When the XML Serializer Generator is not used, a XmlSerializer generates serialization code and a serialization assembly for each type every time an application is run. To improve the performance of XML serialization startup, use the Sgen.exe tool to generate those assemblies the assemblies in advance. These assemblies can then be deployed with the application.

The XML Serializer Generator can also improve the performance of clients that use XML Web service proxies to communicate with servers because the serialization process will not incur a performance hit when the type is loaded the first time.

These generated assemblies cannot be used on the server side of a Web service. This tool is only for Web service clients and manual serialization scenarios.

If the assembly containing the type to serialize is named MyType.dll, then the associated serialization assembly will be named MyType.XmlSerializers.dll.

This means that as well as being impractical the first solution doesn’t really solve the problem.

The second solution ‘solves’ the probelm, however based on the MSDN article it may result in a minor performance hit if the serialization class is required.

In my case i was performing some manual serialization and thus decided to live with the minor annoyance of soltuion #3 and removing the existing assemblies from the GAC whenever i ran into the error.

Posted in .NET, Sharepoint | Tagged: , , , , | Leave a Comment »

Retrieving unique values from a column or list in Excel 2007

Posted by mundeep on February 10, 2009

To generate a list of unique values from a column/list in excel with repeating values it is possible to use the “Advanced Filtering” functionality. To demonstrate let us look at an example where you have some de-normalised data such as a list of countries and colours on their flag you can retrieve both a list of the countries and a list of unique colours used.

Here is a list of our initial data:

Sample Denormalised Data

Figure 1) Sample Denormalised Data

From this we want to return a list of (a) unique countries & (b) unique colours used.

1) In the menu click on Data and then in the Ribbon click on “Advanced” in the filter section.

Figure 2) Advanced Filter Button

Figure 2) Advanced Filter Button

2) If you get a warning about not being able to determine which row contains column labels click on OK (this will assume the first row in your data selection is the column header).

Figure 3) Column Label Warning

Figure 3) Column Label Warning

3) In the advanced filter dialog:

  • Select “Copy to another location”.
  • In the ‘List Range’ select a column of data (eg. the country column).
  • Leave criteria range blank.
  • In the ‘Copy To’ select a single cell in the same sheet to copy the data to.
  • Ensure the “Unique records only” checkbox is ticked.
  • Click OK
Figure 4) Advanced Filter Dialog

Figure 4) Advanced Filter Dialog

4) Repeat for the ‘Colour’ column.

Figure 5) Filter Colour Column

Figure 5) Filter Colour Column

5) You should now have a unique list of countries & colours as follows (note the column headers for the unique lists was changed).

Figure 6) Final Result

Figure 6) Final Result

Posted in Office | Tagged: , , , | 10 Comments »

stsadm runs slowy

Posted by mundeep on October 21, 2008

Ran into problems in a virtualised (vmware) sharepoint environment where stsadm commands would take a long time to run (upto 30 seconds for a simple stsadm -?) when running under my account but it would run fine (couple of seconds) under a different user account.

After scratching our heads and trying to determine if any security groups where missing from my account (and the other people this would happen to) we  narrowed it down to occuring only on machines where we did not perform the sharepoint install.

This didn’t really help solve the problem but some googling bought me to Mark Rhode’s blog where he explains that the problem is usually due to stsadm trying to query crl.microsoft.com before executing – hence our issue being that internet proxies where not correctly setup for some users.

The simplest solution to the problem is to correct the proxy so the environment has internet access. However for those that don’t want to (or can’t) open their virtual environments to the internet, Mark has a couple of other suggestions (such as adding a host entry for crl.microsoft.com pointed to  Paul Horsfall also explains how he found the problem using Ethereal and then used proxycfg.exe to setup his proxy.

Posted in Sharepoint | Tagged: , , | Leave a Comment »

Hiding Mapped Property Images in User Profiles

Posted by mundeep on September 9, 2008

While working with Sharepoint User Profiles I have had to perform a hack to remove Mapped property images or Warning property images from the Sharepoint User Profiles edit page. Using the default ShowMappedIcon or ShowWarningIcon properties of the “UserProfileEditor” control easily allow you to hide the warnings and mapping information however it destroys the layout of the rest of the editing page by missing lots of table cells and results in an ugly looking page.

As a result i had to instead leave ShowMappedIcon=”true” and then use the following javascript to hide all the mapping images and wording:

/// <summary>
/// Hide mapping text and images.
/// </summary>
function clearMappingDisplay() {
    try {
        //Hide Mapping Images
        //Find all images on page
        var images = document.getElementsByTagName('IMG');
        for (var i=0; i < images.length; i++) {
            var curImage = images&#91;i&#93;;
            //If image is for "Mapped" property
            if(curImage.alt == "Mapped") {
                //Replace image with blank.gif & hide image
                curImage.src = curImage.src.replace("mapped", "blank");
                curImage.style.visibility = "hidden";
        //Hide mapping text
        //Get tblMain container
        var mainTable = document.getElementById('tblMain');
        //Get all tables within main table
        var subTables = mainTable.getElementsByTagName('TABLE');
        for (var i=0; i < subTables.length; i++) {
            var curTable = subTables&#91;i&#93;;
            //Find the table that includes the mapping text
            if(curTable.innerText.indexOf("mapped for profile import") > -1) {
                for (var k=0; k < curTable.cells.length; k++) {
                    var cell = curTable.cells&#91;k&#93;;
                    //Find the cell that contains the mapping text
                    if(cell.innerText.indexOf("mapped for profile import") > -1) {
                        //Clear the mapping text
                        cell.innerText = "";
    catch (ex) {
        //TODO: Handle error.
//Ensure sharepoint runs the clearMappingDisplay method on Page (Body) Load

Posted in Sharepoint | Tagged: , | 2 Comments »