Search This Blog

Sunday, October 2, 2011

Sharepoint Part 4: Custom Project Systems on a Shoestring

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


In the previous sessions, we have (1) identified the needs & requirements for a custom Project System, (2) developed the high level design of objects and workflows, and then (3) implemented the lists [tables] and the basic relationships [lookups/keys] between them.


Process Workflow Design
In this session, we will cover the approach to detailed design of the process workflow, prior to implementing/coding it in Part 5. It is important to understand the process model and use it as a reference as you move forward, as some workflows can be quite complex when you get into the implementation. 


Note: The development tool that will be used is Microsoft Office Sharepoint Designer 2007, which is free and works with Windows Sharepoint Services (WSS) 3.0 (free) and Microsoft Office Sharepoint Services (MOSS) 2007 ($). Newer tools may apply  for Sharepoint 2010 development, but the principles will be the same.


Key Objects
In our system, although there are many lists defined, there are really two main lists that drive most of the activity, and are of central importance to running a project-based business, or groups of projects in a program, etc.


The Project
Projects are the anchor object for any given project (or deliverable, if your projects business handles high volume small deliverable-based projects). The Project will be created and maintained by the Project Manager/Coordinator. Projects will go through many different stages and status values (lookup list) throughout the lifecycle of the project. 


A key element in our workflow model is the use of status changes to trigger events, and many of these events will create items in other lists, including:

  • Create New Project Assignments
  • Update open Project Assignments tied to the project
  • Add entry to Audit Log
  • Add entry to Project Notes Log
  • Trigger billing event on Fixed Price deliverables
  • etc

Project Assignment
The project assignment is essentially a work package under the project, where one individual [or team] is responsible for delivering that component of work/service/deliverable. Project Assignments are also status-based, and workflow activities will occur based on a number of conditions when an Assignment is updated, including:

  • Project Status change on Assignment completion
  • Time entry
  • Held Time entry added if case of T&M overage
  • Notes recorded
  • etc
The Project Assignment will also be the "one stop shop" for status updates, time entry, note logging, etc for the person assigned to the activity. The Project Assignment Update workflow will automatically update information in the main project record. Depending on the status of the project at the time, different project fields will be updated based on the correct context.


Note that the Project Assignment Update workflow will also be designed to only update Project Status if the project is STILL in the same status when the Assignment was created. This helps prevent stale assignments changing state inappropriately, in case the Project Manager later needed to change the Project status manually because the team member had not closed out their assignment in a timely fashion, but the work had been completed.


Workflow Design - revisited
In Part 2 we identified the high level workflow design, in terms of state transitions on projects and what the next steps were. We will focus on one of the workflow state diagrams for our discussion. You can refer back to Part 2 for the full set of workflow diagrams as we do not repeat all of them here in this session.
In the diagram, bright green indicates when a Project Assignment needs to be created. Customer activity states are in light yellow. From our perspective, as the vendor, these indicate no current action required on our part other than monitoring vs the schedule. The customer is responsible for completing that task.


Projects have 6 distinct stages:

0 – New
1 – Requirements / Estimating [Project Initiation]
2 – Development / Delivering Service [Project Execution]
3 – Customer Testing / Revision Cycle [Project Execution]
4 – Complete to Billing (Accepted) [Project Closeout]
5 - Warranty

In order to help keep track of the activity within stages, the Project Status List values include the project stage as the first part of the name, i.e. [1] Requirements Review. Some stages will need to have similar status values (i.e. Testing), and as we are going to be designing our workflows to make decisions on the change in status values, we need to have distinct status codes by stage. This also helps avoid confusion and actually simplifies the workflow implementation. The list also includes additional fields describing the stage it applies to, and information for both workflow and documentation purposes.


Phase 1 Workflows: Project Initiation

We will use the Project Initiation stage[1] for our example. 


Each oval (except the blue one) indicates a specific project status. Transitioning to another status may trigger creation of a new Project Assignment (bright green), or another state, indicating either the customer has an activity (whose hours we do not track), or, in Phase 1, projects may be abandoned or estimates rejected by a customer.


Note that when a customer responds, the Project Manager will then set the appropriate status in the Project to trigger then next round of workflow activity within the team.


In the first stage of the Project lifecycle, every project starts out with a status of [0] New. This corresponds to the New Project workflow, where initial setup of the project, including numbering, setting prior values etc takes place. All of the business logic workflow transitions occur inside the "Updated Project" workflow, so we do not want to replicate that logic in the New Project workflow.


After the Project is created, then the appropriate status is set depending what is needed - estimating, writing requirements, delivering service, etc.


Other Types of Project Workflow Activity
  • On any change of status (or new value from prior value), the Project Audit Log will always be updated to include the change. 
  • Depending on the status transition, a new Project Assignment may be created
  • Email can be sent to the Project Manager and Project task owners on change of Project status
  • Depending on the type of project and the new status, a standard folder structure may be automatically created in the Deliverable Repository, for storing project documents and deliverables in a consistent fashion [Part 6, PowerShell]
  • When ownership of a task type in the project changes, the audit log will be updated and any open Project Assignments will be updated to move the assignments to the new person's queue. [Part 6, PowerShell]
  • Any other type of supplementary activities you need to happen when records are updated.

Workflow Design Concepts
There are some key design concepts to keep in mind when you are building Sharepoint workflows, that are a bit different than coding in other languages/tools. This is at least true of the Sharepoint Designer tool itself, as it enforces a specific approach to coding the workflows that you need to work within. Note that in Part 6, we will be able to do advanced workflow coding using PowerShell - launching out from the basic Workflow structure in Sharepoint Designer.
In Sharepoint Designer:
  1. Workflows are triggered by events:
    1. New list item
    2. Updated list item
    3. Manually triggered
  2. Each step in a SD workflow is independent. Step 1 will execute, then 2,3,4,5 etc. There is no programmatical method of branching or skipping of steps. 
  3. However, you can terminate the workflow part-way through. But do so with caution, as you will see below.
  4. Each workflow step can cause an action based on IF-THEN-ELSE type logic.
  5. You need to design in your own state change detection logic. 
  6. You need to avoid infinite loops! Workflow triggers based on "update" will continue to fire if your update triggers an update of the same record, and so on, unless you build in proper checks.

Initially, the step-by-step model can be frustrating, but it does help to keep the logic compartmentalized. It also means that when you do need to accommodate complex logic in your workflows, you need to break things up and use variables to track the results of tests/decisions from one step of the workflow to the next.


Key Design Considerations
Numbering
In our Project System, we want to make sure that key lists are numbered. Names can change, but as long as numbers remain unique, they can be very useful for communication within a project environment. Every project will need to have a unique number, and for completeness, every Project Assignment will have one too.


The numbering must be automatic, and it must be unique. We don't want to rely on anyone looking up numbers, plugging them in by hand. And why should we, when we can get the system to do it for us? So we will incorporate this into the "New Project" and "New Project Assignment" workflows when we build them in Part 5. But to prepare for this, we will need to build another custom list, to simply keep track of autonumbering of different lists. We will call this list "Autonumber" and it will contain 3 key fields:
Title: The name of the list being numbered
LastNumber: Last recorded number used in that list
NextNumber: Calculated simply as [LastNumber] + 1, which will be the next number used when we add to the list.


The New <List Item> Workflow will include logic to use the "next" number and then set the "last" number to the number being used. We will show how to code this workflow in Part 5.


Detecting Changes
Sharepoint does not provide built-in functionality for detecting changes of fields (other than simply that the record has been updated). Well, technically it does, but not in a way that is helpful to us when developing workflows. In particular, we want to be able to know if the workflow was triggered because status changed, an owner changed, or whether someone just added a line in a text box. And quite often we want to know when status changed from a specific value to another specific value, to maintain the integrity of the workflow.


The simple way to make sure you can do this, of course, is to build it in yourself. For any field that you want to be able to detect changes on, you will in fact need two fields, i.e. Status and PriorStatus. Both need to be of the same type, and you must make sure that the workflows update these properly. (This is why "Stop workflow" should not be used on key list Update workflows).


The basic design is this. For any given pair of fields, (Status, PriorStatus, or Owner, PriorOwner, etc), you need to ensure that the following always happens in the workflows:


New <List Item> workflow:
Save the main field to the Prior field, i.e. save "Status" into the "PriorStatus" field.


Updated <List Item> workflow:
Test if Status <> PriorStatus, if so then set a variable to indicate Status change
...
If Status changed then do some action or actions.
...
other workflow steps
...
At the END:

Save the main field to the Prior field, i.e. save "Status" into the "PriorStatus" field.
<repeat for any other Prior values>


Hiding columns
Ok, so now you have pairs of fields for everything you want to track changes on. You have "Field" and "PriorField" columns littering up your list. Ugly. And moreover, the "Prior" fields are all supposed to be maintained by the workflows, so you need to be able to hide them so they are not accidentally edited, right? Well the good news is that is what we are going to do next.


In your List (using an account with suitable privileges), go into the List Settings.
Under General Settings, click on Advanced Settings
At the top of the next screen, you will see a section called "Content Types". On the right side there are two radio buttons.
Choose "Yes", then click on "OK" to go back to the List Settings. You will now see a new section called "Content Types".

Click on "Item". This will bring up a screen with of all of the fields in the list. 
For each of the "PriorXXX" fields, click on the name of the field and do the following in the next screen: 
In the section "Column Settings", click on "Hidden". This will prevent the field from being visible in Edit forms, so you cannot accidentally edit it. It will also not show up in View pages. However, you can still elect to show them in custom Views if you want to.


Summary
There are many considerations in designing your workflows, what needs to be updated and when, who needs to be emailed, what needs to be created, etc. We have also set the groundwork for designing workflows based on change detection of values from prior values, which will be essential to our complex workflow development and avoiding infinite loops.


Your particular needs will likely differ from ours, however the above should cover the essentials of what you need to think about, and how the schema can be customized to support various types of workflow decisions. In the next section we will choose several of the workflows and go through step-by-step how to implement them using Sharepoint Designer.


Coming next:
Part 5: Implementing Custom Workflows with Sharepoint Designer
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


For more information or questions, contact:


Gary Nelson, PMP
www.gazzaconsulting.com