Short solution for short problems

Quality document management system (QDocs)

Working an a laboratory environment, I have a lot of experience with SOP (Standard Operating Procedures) and WKI (WorK Instructions). In the beginning my company was using a paper based system, because the lab was subjected to GLP and thus a computerized system had to be validated and was not easy to find.

Then the new QA offiver decided to create a new electronical system because the need for collecting signatures electronical was getting high since everything was done on paper.

We decided to use WSS as base system and expand it specially for the needs of the lab conform to GLP. WSS is a free system that comes with any windows server edition, so no extra cost.

The system is now going into its 3rd year and is running version 3 now. Each version contained more functionality since the system was such a succes.

In the following posts I will explain the workflows and functionalities of the system. Some technical explanation is also given.

I decided to make a complete new version with the same principles, because my company owns the initial system, I will start from scratch and improve were I can. The first improvement is that I now use content types and its possible to add new document types. 2 default types are SOP and WKI.

Here is a visual overview of the review process, the approval and publishing workflow that automatically follow after the review workflow will be explained in further posts.


So QS will initiate the workflow by creating a new Quality document. The reason why QS initiates is that they can perform a check first if the new document is relevant. Also they will decide on the author and responsible reviewer. The responsible reviewer can be the authors direct supervisor or any subject expert. When QS finishes the initiation of the document, an email is sent to the author to tell him about the creation of the document.

The author can start editing the document. When he finishes he can send it to the respobsible reviewer for review. He can approve or disapprove the review. if he disapproves the review, the document will be resended to the author. Whe the RR finishes and approves the review the document advances in the workflow. The last step in the review process is that the QA department does a last check and assignes persons or groups to be instructed in the document. then they release the document for approval. this will be described in a next post.

 The technology used to create this document management system is WSS, .Net, MS SQL 2005 or 2008 and IRM. All Microsoft based products. The custom development consists out of a Sharepoint workflow, several web parts and custom WSS site features. This makes the technology very strong and flexible for future upgrades.



Dilbert on SharePoint

You want to encourage users to check a certain WSS site, use dilbert as an attraction.

This nice enterprise cartoon changes everyday and give a laugh now and then. It generates extra motivation for users to daily check the site.

To place it on your site is really easy. Just go to this site, download the webpart and import it to your site. Thats it really.


Unlink a list item from source item

When you use the Copy method, to copy a list item from one list to another, the item is linked automatically to the source item. This enables the user to go to the source item, even if we don't like it.

the solution is to unlink the item. This can be done manually, but when you copy using coed, you can directly unlink the item like this:

SPListItem.Copy(SPContext.Current.Web.Url + "/" + _doc.Url, _publicLibUrl + _doc.Name);
SPListItem fileToUnlink = site.AllWebs[_publicSiteName].GetListItem(_publicLibUrl + _doc.Name);
if (fileToUnlink != null)

The first try I forgot to add the .Update(). It is required to to this, if not, your change is not updated and the item is not unlinked. The unlinking cannot be undone.

The data source control failed to execute the insert command

In my test evironment, just like that, the workflows on a document library sotp working. Normally it starts on creation of a new document and the other can only be started manually. None of them worked anymore.

So what I did to resolve the problem was to remove the workflow from the library together with the complete history of the workflow. Then I opened the SharePoint designer and associated the workflows again with the list. Problem solved. I don't know why it happened, but for all I can google its a bug in SharePoint.

How to hide WSS list item properties

I was working on a SharePoint project, where I need to test and set an item field, but this field should not be visible to users.

After looking in SharePoint configuration, I realized it could not be done this way. So the solution I found was to edit the tp_fields in the 'alllist' table of the content database. First you have to look for the tp_id of the list where you want to hide the property. Then you copy the contents of the tp_fields field and paste it in a sql command like this:

update alllists 
set tp_fields = '

<Field Name="_Status" Hidden="TRUE" FromBaseType="FALSE" Type="MultiChoice" 
DisplayName="Status" Required="FALSE" FillInChoice="FALSE" ID="{1dab9b48-2d1a-47b3-878c-8e84f0d211ba}" 
Version="7" StaticName="_Status" SourceID=http://schemas.microsoft.com/sharepoint/v3/fields 
ColName="ntext2" RowOrdinal="0">
    <CHOICE>Reviewed by Author</CHOICE>
    <CHOICE>Reviewed by Reviewer</CHOICE>
    <CHOICE>Reviewed by Safety</CHOICE>
    <CHOICE>Reviewed by QS</CHOICE>
    <CHOICE>Released by Reviewer</CHOICE>
    <CHOICE>Released by Sefaty</CHOICE>
    <CHOICE>Approved by Management</CHOICE>

</Field><Field Name="Author"/>'
where tp_id = '8257d5d1-789d-37cd-b4sc-bf61f4761451'

The ... replace the other fields in the list. So when you set hidden="true" the field is still accesible trough code, but not in SharePoint gui, or even SPD. If you have made a view that consited this field, it will keep showing the field. But in a new view, the field will not be available anymore. So if you still want it to appear in a view, then create the view before you hide the field or just unhide it for a moment to create a view and hide it again afterwards.

Get email from SPUser field in SharePoint

Whe you are working with WSS workflows or webparts, you will come to the point you have to send emails to users.

If you are using a single user field (not allowed to enter multiple users) the value is returned as a string. So if you choose to show the users name and you want to email this users, you cannot get his email out of this field. So how can we get the users email? Here's some functions that might help:

First I get the authors name out of a field in a list:

searchPerson = _doc["Author"].ToString().Substring(_doc["Author"].ToString().IndexOf("#") + 1);
to =  GetEmail(searchPerson);

Sometimes Sharepoint stores #? characters after the names, so multiple values are allowed. The substring will get them out and return the users name correct. E.g. The value could be Mark, Deraeve#2 and after the substring it will be: Mark, Deraeve 

The GetEmail fuction will look for the user in the rootweb, if a user is present in the current web, he will be on the rootweb. Here the code that returns the users email:

        private string GetEmail(string username)
            foreach (SPUser user in SPContext.Current.Site.RootWeb.AllUsers)
                if (user.Name == username)
                    return user.Email;
            return String.Empty;

So now you get the users email. You could prevent from looping through all users if you have the users login name, but in my case it the users full name, so I cannot use ...AllUsers[loginname] and I have to loop through the entire users collection.

If the user field allows multiple values, you can retrieve the email with the SPFieldUserValueCollection. Because it allows multiple users, it will not return a string, but a SPFieldUserValueCollection. We can use this to build up a list of email addresses:

SPFieldUserValueCollection col = (SPFieldUserValueCollection)_doc["Users"];
StringBuilder sb = new StringBuilder();
foreach (SPFieldUserValue user in col)
if (!IsGroup(user,ref sb))
            sb.Append(user.User.Email + ";");

I will add some extra code that gets the emails of the users who are in a group:

private string GetEmails(string groupName)
            foreach (SPGroup group in SPContext.Current.Site.RootWeb.Groups)
                if (group.Name == groupName)
                    StringBuilder ret = new StringBuilder();
                    foreach (SPUser user in SPContext.Current.Site.RootWeb.Groups[groupName].Users)
                        ret.Append(user.Email + ";");
                    return ret.ToString();
            return String.Empty;

I used a function IsGroup, this function simply checks if it is a user or a group and adds all the users in the group to the email addresses (ref sb):

private bool IsGroup(SPFieldUserValue userOrGroup, ref StringBuilder sb)
            SPGroup qsgroup = null;
                qsgroup = SPContext.Current.Site.AllWebs[_sopSiteName].Groups[userOrGroup.ToString()
	.Substring(userOrGroup.ToString().IndexOf("#") + 1)];
                foreach (SPUser user in qsgroup.Users)
                    sb.Append(user.Email + ";");
                return true;
                return false;

Just for fun another function I used to check if a user is a member of a particular group:

        private bool UserInGroup(string group)
            SPGroupCollection grps = CurrentUser().Groups;
            foreach (SPGroup grp in grps)
                if (grp.Name.ToLower().TrimEnd() == group.ToLower().TrimEnd())
                { return true; }
            return false;


Checkin all items in a document library

When I had a deadlock on a document in a document library, I went looking for a solution. Some suggested that you write a webpart that checks all document in a document library, which are checked in shortterm. 

SPListItemCollection docLibItems = getDocLib().Items;
Page.Response.Write("No of items: " + docLibItems.Count.ToString() +"<br>");
foreach (SPListItem docLibItem in docLibItems)
Page.Response.Write("Item: " + docLibItem.Name + " status: " +
docLibItem.File.CheckOutStatus.ToString() + "<br>");
if (docLibItem.File.CheckOutStatus == SPFile.SPCheckOutStatus.LongTerm)
            catch (Exception error)
if (docLibItem.File.CheckOutStatus == SPFile.SPCheckOutStatus.ShortTerm)
            Page.Response.Write("Checking in short term lock out!");
            catch (Exception error)

So I tried it, but it didn't helped me. So at the end I had to delete it and put it back again. Just download the document and "save as" a document on your local machine. That delete the original and upload the document again. The only disadvantage you have is that the creation date and the created by are changed. I had to remove this deadlock fast, but next time this occurs I will try to find a better solution to this problem. You can first try the code above, because in some cases it will work. I found people with the same problem and for them this code worked.

How to add a line break or new line in a SharePoint survey question

A user is trying to make a survey. Now, she needed to create a header for each new page in the survey. She tried to use the textbox and hit enter in the question field, that's what I would do to. And it appears as if the enter are accepted. But when you look at the question, you will notice that the line break disappeared. As I was looking for a solution, I found that in the previous version, this was possible but not in WSS 3.0! So after looking a little more, this site gave a good solution, I decided to use it, but with a slightly different approach.

The thing is that we will implement a javescript that will look for elements with classname : 'ms-formlabel'. Then it will replace the word linebreak with a <BR>. This solution you can find on the site I mentioned above. But they suggest to place a content editor webpart on each page that uses the survey, like the newform.aspx of each survey. Since I'm using a WSS site specially for surveys, I don't want to do this every time I create new survey. So I added the code to the default masterpage of the site. In the head section of the page I placed a function called: setLineBreaks()

<HEAD runat="server">
            <script language="javascript" type="text/javascript">
            function setLinebreaks()
                        var className;
                        className = 'ms-formlabel';
                        var elements = new Array();
                        var elements = document.getElementsByTagName('td');
                        for (var e = 0; e < elements.length; e++)
                        if (elements[e].className == className){
                        elements[e].innerHTML = elements[e].innerHTML.replace(/linebreak/g,'<br/>');}

Then I call this function on the pageload event like this (the code I added is in bold):

<BODY scroll="yes" 
onload="javascript:if (typeof(_spBodyOnLoadWrapper) != 'undefined') _spBodyOnLoadWrapper();setLinebreaks();">


So now every time you enter the word linebreak in a question a new line will be started. (It will be replaced with a <br>)



How to create site definition (template) with a custom masterpage

Today I started to investigate the site definitions again.

It has been a while since I've been there, and lots of things changed since the upgrage to WSS3.0. My first challange was to create a simple site definition. No big problem there. Now I'm configuring this site definition to match the style of the company. So the first thing I had to find out was how to implement a custom masterpage in my site definition.

The masterpages definitions are found in this directory: Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\GLOBAL. By default you will find 2 masterpages, the default.master and the mwsdefault.master. So I copied the default.master and renamed it to MRD.master. This is the first thing you do. Then change some small things in the master page, so you can see if your site template implements the correct master page. Now lets look at the changes you'll have to make in the ONET.xml of the site template.

In every configuration you want to implement the custom master page, you should have set the CustomMasterUrl attribute like this:

<Configuration ID="0" Name="Default" CustomMasterUrl="_catalogs/masterpage/mrd.master">

<Configuration ID="1" Name="Blank" CustomMasterUrl="_catalogs/masterpage/mrd.master">


So once the CustomMasterUrl attribute is set , we add the masterpage module to the modules section of each configuration where the CustomMasterUrl attribute is set.


 <Module Name="Default" />

 <Module Name="MRDMasterPage" />



The last thing we have to do is declare this module on the bottom of the onet.xml, in the modules section:



 <Module Name="MRDMasterPage" Url="_catalogs/masterpage" SetupPath="global">

  <File Url="MRD.master" Type="GhostableInLibrary" IgnoreIfAlreadyExists="TRUE" />


When you look at the ONET.xml of the MPS site template, you'll find a module called: EnsureMWSMasterPage. This has the property RootWebOnly set to false, so all subsites will have the masterpage copied to them. You can use this module simular as the one I explained on top.

The next thing I'm going to find out is how we can set the theme of the site using site templates.

By the way, changing or creating site templates is not the supported way. But sometimes the only way. Microsoft is providing all kinds of alternatives for altering the site defenitions, but still its powerfull. When you change things and implement them in production, you should have a document where the changes are described in detail. So you can reporduce them after you installed an update.