Search This Blog

Tuesday, October 4, 2011

Sharepoint Part 5: Custom Project Systems on a Shoestring

Develop your own project systems using Free Tools, just add labor

So far, we have collected the requirements, created the high level design, implemented most of our custom lists, and designed the process workflows, including structures in the lists to support the state-based workflows. Now we are ready to implement the workflows.

Implementing Custom Workflows with Sharepoint Designer
First, if you don't already have it, you need to download and install Microsoft Office Sharepoint Designer (2007). After you have it installed, launch it and open your Sharepoint site URL (i.e. You will need to login with a user account that has Site Collection owner privileges. (If you don't, the workflow will not have privleges to run once it is loaded). The will bring up your website structure in Sharepoint designer. 
Note: Although there are many things you can do with Sharepoint Designer, we are going to focus only on the Workflow design aspects of it here.

Creating your first Workflow - New List Item
From the File menu, Click on New > Workflow. This will bring up the New Workflow dialog.
You will need to name your workflow. Try to choose well, as although you can rename it later, there can still be references to the old name tucked away. For this exercise, we will call it "New Project".
Next, choose the list you want to build the workflow for. You cannot change this later. We will use the Projects list we created in Part 3.
By default, new workflows are set to be manually initiated. This is helpful when you want to test a new workflow without having it run automatically until you are sure it is working properly. We'll leave it like this for now; we will go back and change it later.
Click on the "Next" button. You will now see the workflow step designer.
Name the step to be something meaningful. You can change this name at any time, and you can re-order steps if needed for your business logic. Note that each step is independent, that is, you can have many steps in a workflow, but each step will execute in sequence. You cannot, for example, skip directly from Step 2 to Step 7. However you can code your steps to only take action if/as appropriate, based on what has happened in the preceding steps. So you can code steps 3,4,5 and 6 to take no action under specific circumstances.

Each step consists of one or more Condition blocks, followed by one or more Actions in each Condition block. You can have many condition blocks in each step, structured as 

IF <condition block A evaluates to True>
THEN <action block A>
IF  <condition block B evaluates to True>
THEN <action block B>
ELSE ...etc

Note that if you always want the actions to happen, then you can have an empty condition block. This is usually the only condition block in the step (no IF THEN ELSE). Condition blocks are structured as sets of AND/OR logical evaluations, i.e. evaluate to TRUE IF a AND b AND c AND d ...etc conditions are true. (Similarly with OR).

Tip: I would recommend sticking with only one type of logical operator in each Condition block (AND, or OR) and not mixing up AND/OR evaluations in the same condition block. It can become confusing, as it may not be clear to you what the precedence order of the evaluation will be. So the easiest thing is to avoid it, and model your logic in discrete steps instead, with grouped AND conditions, grouped OR conditions, etc.

Adding the Actions
You can then add one or more actions to take place for the condition in this step. You can update items in this list, update items in another list based on values in this list (if they are linked, as we did in Part 4), and many other functions. You can also store intermediate results in variables and use them for later business logic decisions, or to update list fields in this step or in later steps.
Note: There are a number of built-in actions in Sharepoint Designer; however you may soon find them somewhat limited as I did, but you can download a number of free additional action packs from iLoveSharePoint on the Codeplex site when you begin developing more complex workflows. Ones that I recommend downloading and installing are iLoveSharepoint.DesignerActions (follow the instructions for the package in Codeplex for installation instructions):

  • Start a Workflow
  • Do calculation
  • Execute SQL
  • Get Parent Folder 
  • Other XML and string functions

Auto-numbering the List Item
For this exercise, we are going to Autonumber the list item when it is created (with our own numbering scheme), and include that number in the Title of the list item, so that it is unique, identifiable and sorts properly. We will also store the number in a hidden field in case it is needed later.

Previously, we created the Autonumber list, with the Title field, LastNumber field and a calculated field called NextNumber, which is [LastNumber]+1. The Title field names the list we are numbering.
Tip: When you first setup the list, you need to set the initial number for the list; I suggest using a high number to keep sorting simple, i.e. 10000, 100000 or higher, depending on how many items you expect to add in the lifetime of Sharepoint use, before rolling to the next higher order of digits. (i.e. if you numbered starting at 100, you could quickly roll through 999 and then on to 1000 which would mess up your text-based sort order, as you will see later on).

In our autonumber Workflow step, the Condition block will be blank (true always), and the Action block will contain the steps necessary to:

  • Read the latest number from the AutoNumber list for the "PROJECTS" record
  • Set the AutoNumber list "PROJECTS" record LastNumber to the number used
  • Assign it to the Project record
  • Update the Project record Title to include [<number>] + Title text

The workflow step Action block will end up looking like this, which we will step through:
1a) We will create a variable (called mNumber). Select "Set Workflow Variable" from the list of actions. Specify the type as Number.
1b) We will set it to the NextNumber field from the AutoNumber list, as follows, by choosing the list to lookup, and the field to pull from, where the field "Title" in the Autonumber list is "PROJECT":
2) We then set the number field (in this case, [Fwk #]) in this new Project record to the variable mNumber, using the "Set Field in Current Item" action.
3) We then update the Autonumber list ("PROJECT" record), setting the LastNumber field to the mNumber variable. Add an "Update List Item" action and complete it as follows:
4a,b) Next we will add the number to the title of the Project. We will use the Title from the current record, and prefix it by building a dynamic string using the "Build Dynamic String" action, which outputs to the mTitle text variable:
5) Finally, we store the new prefixed title, by using either the "Set Field in Current Item" or "Update List Item" action. Tip: If you want to update more than one field at a time, use "Update List Item".
Now, we save the workflow by clicking on Finish, which will make it available (and visible) in Sharepoint. You can test it first by manually initiating the workflow from a list item. 
When you are satisfied the workflow is functioning properly, go back into Sharepoint Designer, choose File > Open Workflow, choose the workflow name, and then change the start conditions from "manual" mode to "Automatically start this workflow when a new item is created".


The next workflow: Updated List Item
In this section, we will look at the logical framework for state-transition based workflow actions (i.e. change in value of fields from a previous to new value). These can be used at different levels, i.e. detect if there is a change, and if so, take specific actions, but if the transition was from A to C then behave differently than any other transition to C, etc.

Add a new Workflow, named appropriately, i.e. "<Listname> Updated". Start with it as a Manual workflow, but when you have finished testing, go back and change the trigger conditions for the workflow to be "Automatically start this workflow whenever an item is changed".

Take action based on change in Field Values
For this example, will look at the case of a Project changing status (from any prior status) to "[1] Estimating".
In this workflow step, a new Task Assignment record will be created, as long as this was a change in status:
This uses two "Compare <Listname> Field" comparisons, which can be compared to other fields in the same list, to a literal text value, or to another data source (another list, or workflow variable).

For this test, IF the prior status not the same as the current (new) status, AND the new status is "[1] Estimating" THEN the action(s) will be triggered.

The Actions
Using the same type of lookup into the Task Assignment Types setup list, above, we store the TaskType key into a variable, mTaskType for the "Estimating" task type:
Next, we will Create new list item in the Project Assignment list. We will assign values to all required fields in the Project Assignment, including the link back to the Project, the customer, other key fields and common filter fields like client ref#, customer ref#, etc.
Additional actions may be included as well, depending on your design.

The Final Step - Setting Prior Values

It is very important to make sure that you have a step at the end of the workflow that sets the "prior" values to the "current" values - for any fields that you have defined Priorxxxx fields for. This will make sure that any updates to the record during the workflow execution will not trigger an infinite loop of calling the workflow over and over again.

You will need a "blank condition" step like the following, at the very end of the workflow:
You can use the action "Set Field in Current Item" or "Update List Item", if you have more than one Prior field to update:

Breaking the Infinite Loop

As above, every step in this workflow will only cause an action when the Prior value is different from the Current value being compared. That way, if the workflow actions cause an update to the record, it will only run once more (doing nothing) then stop. 

It is also imperative that the workflow does not update any of the fields it is using to test, i.e. Status. This could also cause an infinite loop. Keep the logic clean and separated. Design it so that an "external event" (i.e. user updating the status, or project assignment completion updates the status in the Project record) is the only way those "prior/current" comparison fields are ever updated. And also ensure that you design the workflows so that updates in List A which trigger updates in List B do not cause Prior/Current field changes in List A, etc.

Next time, we will look at advanced workflow actions, using Windows PowerShell (2.0), as well as outside-of-Sharepoint batch scripting, using the .NET extensions for Sharepoint.

Coming next:
Part 6: Advanced Workflows with PowerShell
Part 7: Hosting tips, Email & Notifications
Part 8: Doing it Yourself - Recommendations
Part 9: Using what you've built - more free enhancements

Go back to:
Part 1: The Need
Part 2: Designing the Solution
Part 3: Building the Structure - Sharepoint Lists
Part 4: Process Workflow Design

For more information or questions, contact:

Gary Nelson, PMP


  1. HarePoint Workflow Extensions is similar to iLoveSharepoint.DesignerActions,

    They provide over 180 new activities for SharePoint workflow development and about 20 are freeware.

  2. Hi,

    You may also check how to send SMS from Sharepoint with Ozeki NG SMS Gateway: