U2U Blog

for developers and other creative minds

Do we store relational data in SharePoint?

This week I am doing some consultancy work for a big pharmaceutical here in the UK and they asked my help to guide them building a couple of POCs illustrating to their business what they could be doing with SharePoint (both WSS and MOSS). If you know me, then you know that I have a very developer-focused vision on WSS and MOSS seeing definitely the first one as a development platform (as I like to refer to it ASP.NET++) and for me it was very hard but a good lesson to experience that companies often have another perception of what SharePoint can mean for them. The goal was to come up with a solution involving no-trivial types of business processes support and workflows on top of WSS without major customizations and no development at all.

One of the questions was: Is WSS an environment where we can store very relational type of data and enforce these relationships with what is there out of the box? Nope! WSS and MOSS are places where you can store unstructured content, collaborate on it and manage it in a decent way but no way it is going to replace a good old SQL Server database for storing this type of data. You can of course built all of this on top of the platform (and it is actually a nice opportunity to build a layer on top of WSS to enforce relationships between lists and libraries) but it will require plenty of custom development and customization.

Am I convinced that SharePoint delivers the out-of-the-box experience to support all of your business needs? Nope! Did we manage? Not really. We got about 60% of what the business wanted (remember minimum of customizations and extensions). I managed to get the relational data out of SharePoint and into a SQL Server database and squeeze in a couple of custom Web Services to interact with. Hey! I could not live without doing some C# this week smile_regular.

But what I just wanted to communicate with this post (and it is not about the company in focus this week, there are many companies thinking this way), SharePoint is a platform for delivering solutions that target information workers. I know that plenty of companies can live with the OOB-experience but do know that for many many of the requests of the business there is a certain degree of customization and development involved. My message to these companies is: if you don't get the stuff out of SharePoint OOB, there are only two options 'build or buy'.

Building SharePoint 2007 Solutions (Advanced Workshop) in Cyprus

Many of you know that my second home is the lovely island of Cyprus. I am therefore very pleased to announce that U2U is organizing the 5-day advanced workshop on building SharePoint 2007 solutions in the Holiday Inn in Limassol from 15th of October 'till the 19th.

I'll be your trainer for the 5 days and we'll go through a lot of material covering the steps and best practices to work in Visual Studio building all kinds of SharePoint solutions, making these available using Features and redistributing them with SharePoint solution packages. I recently have added plenty of new content to the course and based on the feedback from previous deliveries students really liked the approach.

So, don't hesitate. If you are living in EMEA and want to make the trip to the sunny island of Cyprus combining a good learning experience with sun and sea, reserve that seat!

Next BIWUG Meeting

Don't miss the next event of the Belgian Information Worker User Group on September 25th! Based on the feedback from the last Belgian community day back in June, a lot of people wanted to hear about guidelines, best practices and the way to go to have a succesfull deployment and use of both Windows SharePoint Services 3.0 and the Microsoft Office SharePoint Server 2007 within the company. I'll do a first session that evening covering this and don't miss out the second session too covering a product overview of the brand new version of Microsoft PerformancePoint Server (delivered by the Dolmen guys).

Here is the full agenda and the registration can be done here:

18:00 – 18:30 Registration and Welcome

18:30 – 20:15 Session 1: Guidelines and Best Practices for a Successful SharePoint Deployment within Your Organization

Join this session if you are looking for answers to questions like ‘When is it appropriate to use SharePoint within the organization?’, ‘What are the weak and strong points of SharePoint?’, ‘What are the pitfalls?’, ‘What kind of resources do I need the level of infrastructure but also people (both admins as well as designers and devs)?’, ‘What are the options to make SharePoint do what why business wants it to do? And what effort is that going to take?’. There are of course plenty of other related questions that can be discussed during this session. Patrick Tisseghem and other BIWUG board members will be more than happy to share their experience during an interactive discussion. The session is planned to be high-level and especially interesting for project managers, technical sales, architect and design folks.

20:15 – 20:30 Break

20:30 – 21:15 Session 2: Overview of Microsoft PerformancePoint Server 2007

This session will give you an overview of how Microsoft PerformancePoint Server 2007 can help you in your organization to help improve performance by integrating monitoring, analysis, and planning into a single application (of course based on WSS 3.0 and integrated with MOSS 2007). As with the first session, the level will be 100-200 with plenty of demonstrations of the product capabilities.

Two Forrester Research Studies on the 2007 Office System

Related to my previous post, here are two whitepapers published recently:

  • 2007 Office System Document: The Microsoft Office Fluent User Interface: Information Worker Perception of Productivity, Training, and Support Requirements
    Download here
  • This white paper presents the results of a Forrester Research study commissioned by Microsoft to help organizations and individuals understand the benefits of the Fluent user interface and address some of the challenges they may have in transitioning to Microsoft Office 2007.

  • 2007 Office System Document: The Microsoft Office Fluent User Interface: IT Decision-Maker Perception of Productivity, Training, and Support Requirements
    Download here
  • This white paper presents the results of a Forrester Research study commissioned by Microsoft to help IT organizations considering Microsoft Office 2007 adoptions anticipate the impact of the new Fluent user interface on their end users.

Speaking @ the UK SharePoint User Group Meeting

17th and 18th of September, I'll be in Reading (UK) delivering the Office Business Applications TTT workshop for Microsoft UK. With the upcoming release of the new version of the Visual Tools for Office ("Orcas"), the idea of building business applications upon the Office and WSS platform is going to be much more easier and appealing to .NET developers. Remember smile_regular : both are rich and powerful development platforms and that is exactly what we will cover during the workshop.

Nick Swan invited me also, while I am there, to deliver a presentation at the next UK SharePoint User Group Meeting. Being myself involved in the Belgian Information Worker User Group, I was more than happy to do this. And I'll be sure there will be a couple of beers afterwards - and this I cannot say no to!

So, if you are living in the UK (near Reading), have a look here and register for the meeting. I'll show you plenty of OBAAAAaaaa stuff!

Generate email address automatically

Today I had the question if the email address could be generated automatically in a contact list. The reason for this question is because the email address is just a concatenation of the first name and the last name and the company added to it. So in my case would this result in 'kevin.derudder@mycompany.com'.


With the calculation field this is a piece of cake. So Start by creating a contact list and add a new column to it based on a calculation field.

In the formula text field, type following formula: LOWER(CONCATENATE([First Name], ".", [Last Name], "@mycompany.com" ))


This will result in:


Create a custom workflow using SharePoint Designer

From time to time I hear from SharePoint users that they always have to wait for their developers if they want to have a workflow that is not available 'Out Of The Box'.

As a respond I ask them which workflow they want to use or have and apparently most of the workflows can be created in SharePoint Designer without writing one line of code.

In this article I'm going to create a 'Content Approval' lookalike workflow, named the 'Holiday Request Approval' workflow (Human Resource managers can thank me later :-)).

Before we open up SharePoint designer, we need a 'Task' list and a 'Holiday Requests' list, which is based on the 'Custom List' template. So the easiest way is to create a site based on a 'Team Site' and add a custom list.

1. screen_ListsinQuickLaunch

In the 'Holiday Request' list we create some holiday related columns, such as 'start date' and 'end date'.

2. screen_ColumnsInList

Now it's time to open up SharePoint designer and load the site where we can find these holiday requests:

3. screen_OpenSiteInDesigner

Once the site is open and loaded, we are going to add a new Workflow by clicking on File --> New --> Workflow button:

4. screen_NewWorkFlow

The cool thing about creating Workflows in SharePoint Designer is, that it's nothing more than running through a wizard and that you can create pretty nice Workflows without writing a line of code.

Wizard Step 1:

In this step we need to provide a title for the Workflow, in our case it is 'Holiday Request Approval'. Next you need to specify on which list or library you want to use it and when you want to start the workflow.

We are going to start it automatically when a new item is created in the 'Holiday Requests' list:

5. wizardscreen_step1

Wizard Step 2:

When an employee adds a request for a holiday, we need to create a task for the Human Resource manager who needs to approve or reject the request.

So we need to create a kind of special form where the manager can select his choice. Because I don't want to write one line of code I'm going to let SharePoint Designer create the form.

At the end, the form will look like this:

36. Whooow

So the action that we are going to use is the 'Collect Data From User' action:

6. wizardscreen_step2_CollectDataFromUser

7. wizardscreen_step2_CollectDataFromUserLine

As you can see on the screen above, you need to provide three things:

  1. The data that needs to be collected
  2. The user that needs to provide the data, the human resource manager
  3. A variable to collect, I'm coming back on this later ...



First click on the data link and a new wizard will pop up:

8. wizardscreen_step2_datawizard1

8. wizardscreen_step2_datawizard2

We need to collect 2 things from the human resource manager:

  • Can the employee go on holiday or not?
  • Some extra comments

Click on the add button to add these two questions on the page.

10. wizardscreen_step2_datawizard3

First question is the status where we are going to use a choice field so that the manager can choose between Approve or Reject:

11. wizardscreen_step2_datawizard3_1

12. wizardscreen_step2_datawizard3_11

Click Finish and add another field to make it possible for the manager to add some comments:

13. wizardscreen_step2_datawizard3_2

14. wizardscreen_step2_datawizard3_22

15. wizardscreen_step2_datawizard4

Once you've added these two fields click on the Finish button to complete the data part of the action.



The next step we need to tell who the human resource manager is so a task can be assigned on his name

(because I always wanted to approve a holiday, I will play the manager for now):

 16. wizardscreen_step2_thisuser

17. wizardscreen_step2_thisuser_1


Variable to collect:

What we've actually done is, we've created a task for the human resource manager (me) to approve or reject the holiday.

Because we want to use the outcome of this task we need to save the ID of the task that we've created. This is is the variable that we want to collect.

We are going to store the ID of the task in a new variable which we call HolidayRequestTaskID:

18. wizardscreen_step2_newvariable

19. wizardscreen_step2_newvariable_1

Once we've completed the first action we need to save the respond of the human resource manager. With respond I mean, what is the status and what is the comment that the manager gave.

To save these responds we need to create 2 variables and store the responds in these variables.

Find the Action 'Set Workflow variable'. If you don't find it click on 'More Actions...'.

20. wizardscreen_step2_NewAction

21. wizardscreen_step2_SetWorkflowVariable

22. wizardscreen_step2_SetWorkflowVariableOverview

We have to do this 2 times, once for the approval status and once for the comments status

Variable 1

First we are going to save the answer of the first question, which is the approval status, in a new variable:

23. wizardscreen_step2_SetWorkflowVariable_1

Fill in the Name of the variable and the type of content you are going to store in the variable:

24. wizardscreen_step2_SetWorkflowVariable_2

Click on OK, you should be having something like this:

25. wizardscreen_step2_SetWorkflowVariable_3

We've created the variable now, but we didn't tell the variable what he should keep. Click in the 'fx' button which will open up a new dialog:

26. wizardscreen_step2_SetWorkflowVariable_4

What do we want to collect? Well we want to collect the things that the human resource manager provided in the task that we've created earlier.

How do we know which Task that was, because there could be more than 1 task? Remember that we've saved the ID of the task in a variable, see 'Variable to collect'.

So in the Source drop down box pick 'Tasks' as the selected source. Once you've selected the task list, you have to look for the 'Holiday Request Approve Status' field which we've created earlier. In this field you find the answer of the first question.

Because there could be multiple tasks in that list you need to tell which task you would like to use. Get the task with the ID that you saved before.

27. wizardscreen_step2_SetWorkflowVariable_5

this ID is saved in the 'HolidayRequestTaskID' variable which you can load by using the 'fx' button and choose for Workflow Data.


Once you've setup all the fields click OK and do the same for the comments question.

28. wizardscreen_step2_SetWorkflowVariable_6

Wizard step 3

Next we are going to create a new Workflow step. You can do this by clicking on the 'Add workflow step' link on the right side of your screen.

29. wizardscreen_step2_AddWorkflowStep

In this step we are going to check what the manager filled in. To do so we are going to compare the answer. The condition that you need to use is the 'Compare any data source':

30. wizardscreen_step3_CompareDataSource

In the first option you are going to select the variable where you stored the answer of the first question in, which is HolidayApproved:

31. wizardscreen_step3_CompareDataSource2

and in the value part you type 'Approve':

32. wizardscreen_step3_CompareDataSource3

If this condition is true, you can do a lot of things:

  • update a field
  • add the holiday to a calendar
  • ...

We are going to keep it simple and send a mail to the requester of the holiday. So use the actions button to select 'Send an Email':

33. wizardscreen_step3_SendEmail

You want to send an email to the person who created the item. So next to the 'To' textbox, click the addressbook button. One of the users that you van pick is the 'User who created current item':

34. wizardscreen_step3_SendEmail1

Complete the other fields in the send mail dialog box:

35. wizardscreen_step3_SendEmail2

Once this is done, click OK.

Normally you will do an action if it is not approved.

Last step

Finish the creation of the Workflow and add a new item in your list, if everything went right you should have a new task created for the human resource manager that you've selected.

36. Whooow

So, I've hope that I could show how you can create some more advanced workflows in SharePoint Designer.


Change your IIS Web App in Dev Mode using SPWebConfigModification

Developers can, when they are in need to see the full ASP.NET error within the SharePoint pages,  turn on the StackTrace and the turn off the CustomErrors within the web.config of the IIS Web Application they are using to test out the work.

It is possible to programmatically do this by working with the SPWebConfigModification class within the Microsoft.SharePoint.Administration namespace. This is a very useful class if you want to dynamically (that is within code - e.g. within your Feature event receivers or custom installation applications). You have to option to add/modify/delete sections, child elements and attributes. To turn the web.config in dev mode and back you'll work with the following code:

       private void ToggleDevMode(string url, bool flag)         {             SPSite sitecol = new SPSite(url);             SPWebApplication iiswebapp = sitecol.WebApplication;              SPWebConfigModification webcnfg1 = new SPWebConfigModification();             webcnfg1.Type = SPWebConfigModification.SPWebConfigModificationType.EnsureAttribute;             webcnfg1.Path = "configuration/SharePoint/SafeMode";             webcnfg1.Name = "CallStack";             if (flag)                webcnfg1.Value = "True";             else                webcnfg1.Value = "False";              SPWebConfigModification webcnfg2 = new SPWebConfigModification();             webcnfg2.Type = SPWebConfigModification.SPWebConfigModificationType.EnsureAttribute;             webcnfg2.Path = "configuration/system.web/customErrors";             webcnfg2.Name = "mode";             if (flag)                webcnfg2.Value = "Off";             else                webcnfg2.Value = "On";              iiswebapp.WebConfigModifications.Add(webcnfg1);             iiswebapp.WebConfigModifications.Add(webcnfg2);              iiswebapp.Farm.Services.GetValue<SPWebService>().ApplyWebConfigModifications();              sitecol.Dispose();         }