Out of the box Sharepoint Workflows

As a project manager, I’m often faced with the problem of how to deal with configuration management, which is how we make sure that everybody is on the same page. I typically like to use a content management system to handle this issue. For my current project, I’m using Sharepoint, which also has some nice built in collaboration capabilities.

There are two out of the box workflows on the Document Library: “Approval” and “Collect Feedback”.

Both work pretty similarly, and for general document collaboration they work pretty well.

When you get a document to where you’d like to gather feedback, you can fire off a Review workflow by going to the workflows page for the document:

This will take you to a page that displays the available workflows (will also display any that are in progress):

Choose the “Collect feedback”  workflow and it will prompt you for who you’d like to review the document and a message that you’d like to send. You have complete control of who you pick, and you can also cc people if you just want to keep somebody in the loop:

Once you pick the people you want to review, assign a due date, and click the “Start” button, the workflow is running.  The people you assigned the task to will get a notification that asks them to review the document. It includes your message, and links to both the document and the workflow tasks (I assigned this one to myself):

As the creator of the workflow, you’ll also get a message for each reviewer you assigned that has the link to their task:

Note that the tasks are stored in a SP library called Tasks, and if you click on the “Edit this task” button, you’ll get the form that lets you provide feedback:

This is where you would give feedback that doesn’t need to go into the document. It also allows you to reassign the task if you aren’t the person who should review it (by default it will assign back to the person who assigned the task to you, but you can also use it to reassign to somebody else):

Once the workflow is running, you can see the status at a glance by going to the workflow page again:

If you click on a workflow, it will show you the current status, and you can update individual tasks:

This takes you to the Task in the Sharepoint “Tasks” library (you can get there by clicking the “Tasks” link above) and a web version of what you see when you click the “Edit This Task” from Outlook:

If you want to see all of the outstanding tasks you can go to that library by clicking on the “Tasks” link on the workflows status page (above):

Converting an Access DB to Sharepoint

I’m a big fan of making people more productive by sharing data entry. In my current project, the tool we have for collaboration is Sharepoint 2007.One of our team members is really comfortable with Access development, and built a nice database for tracking the status of our document deliverables. The problem with this is that since Access is a local sort of solution, it doesn’t allow for people to make updates as they work, so we end up spending a lot of time with the database owner updating status.

The solution (in this particular environment) is to utilize Sharepoint lists. Access 2007 has a nice wizard driven approach to building Sharepoint lists, as long as the database is built correctly.

So here are the iterations I had to go through to make this work …

First pass: Diving right in, I loaded up the database to see what it looked like (how many tables, what sort of structure was there, etc). The designer did a good job of making a lot of the fields lookups, and had named all of the reference tables with the prefix of “REF” – so far so good:

So, thinking it would all work fine, I simply start the “Move to Sharepoint” button in the “External Data” tab of the Access Ribbon:

This starts the wizard, which walks you through the process of building the lists on Sharepoint. Once the task is finished, you are left with a new Access database that is linked to the newly created Sharepoint lists.

Looking at these lists I noticed something off: it didn’t appear that any of the reference tables were being used. They were there as linked Sharepoint lists, but when you looked at the data in either SP, or Access, there was no drop-down or choice to be made, they were simply text fields.

So digging a bit, and having a discussion with the developer, I found a few issues: Most of the reference tables weren’t actually setup as relationships in Access, and some of the reference tables didn’t have an ID column (an Autonumber primary key).

The wizard requires the links between tables to be relationships on autonumber primary key fields, in order to build the field as a lookup.

Looking at the base relationships, it turns out that there was only one of the myriad of tables that actually had a relationship:

In order to get the Sharepoint lists to build fields that were lookups to the “REF” tables, I would have to create new fields that linked to the primary keys in those tables. To do that turns out to be relatively simple in this case.

First I open up the design of the main table, and look for the fields that are using lookups (in the case below, Contract_Task):

Then I simply create a lookup field that has the name of the table in question (in this case REF_Contract_Tasks). First step is to insert or append a new column :

Name the new column the same as the table in question:

Choose “Lookup Wizard …” for the Data Type:

Then complete the lookup wizard:

Choose the table you are interested in (in this case REF_Contract_Tasks):

Pick which fields you want to display (typically this will just be a name field, but you can choose whichever fields you think would be helpful):

Choose the order you want things displayed:

Format the columns for lookup display (it’s recommended not to display the ID column):

Choose a name for the field (if you already typed this in, you don’t need to change it):

The wizard now asks you if you’d like to save the changes, which you should do:

Now if I look at the relationships, and hit “show all relationships”, I see the newly created link:

Right click on the relationship line to edit the relationship:

And I typically just check the “Enforce Referential Integrity” box to make sure it is an identifying relationship:

Once that is done, I can either build an update query, or copy the contents of the original row to the new one in datasheet view. For the purposes of this database, there aren’t that many rows, so a simple cut and paste works fine:

Hit ctrl-C or choose “Copy” from the menu:

Select the new row, and paste the data there:

Repeat for all of the other rows in the table that have some query in the lookup tab …

Now in a couple of cases, I got prompted for which field to use to establish the relationship. These are the tables that didn’t have an autonumber primary key. The fix for this is relatively painless (once you discover which tables have the problem).

Just add a new field to the table that has a Data Type of Autonumber, and make it a primary key.

The trick with this is that the primary key won’t likely have the same numbers as the original ID column, and you can’t create an AutoNumber column with data in it. So in order to get the new field updated, the easiest thing to do is create an update query and edit it to use the old join values.

Do query wizard, and choose the columns in question, in this case Deliverables, and REF_Projects. Since you created a relationship with the new REF_Projects field on the Deliverables table, you should end up with something like this:

In theory you should be able to edit the relationship in the GUI by dragging it around and/or right clicking on it. I’ve actually had limited success with this, so I tend to simply add the column I want to update (REF_Projects) to the query, then hit the button on the Design tab of the ribbon that says “Update”:

Then switch to SQL view in order to modify the join properties more easily:

The query so far will be something like:

UPDATE REF_Projects
INNER JOIN Deliverables
ON REF_Projects.newID = Deliverables.REF_Projects

SET Deliverables.REF_Projects = [REF_Projects].[newID];

And you want to update it with the old columns from the lookup query that was in the original table, so in this case it becomes:

UPDATE REF_Projects
INNER JOIN Deliverables
ON REF_Projects.ID = Deliverables.Project_Level

SET Deliverables.REF_Projects = [REF_Projects].[newID];

Then run the query and the values in the two fields should look relatively the same in the datasheet view (since they are now both pointing at the same rows.

Then another one that threw me off was one field that had a different column heading than the field name. When I got to the “Doc_Type” field, I didn’t see it in the Datasheet view. Turns out it had a value in the Caption area of the field definition:

So in the datasheet view it showed as “NDCP Standard”:

This field also has another interesting challenge – the values didn’t all match up with the values in the lookup table (REF_Doc_Type), so when I did my cut-and-paste trick I got the following error:

So looking at the REF_Doc_Type table, I was able to figure out that there is no row that is named “Standard” (likely it had been changed to “Program standard” at some point, and since this column stores the actual value, there is a disconnect).

So in this case, I chose to simply update the current column values to match the one I thought it should be with an update:

After that my cut-and-paste from the “NDCP Standard” field to the “REF_Doc_Type” field went without a flaw.

This same trick needs to be applied to any of the tables that will be worked directly with (typically those that contribute to forms) in order to make sure all of the proper links are built.

Once you have the links built, you want to rename the original table and build a view (in Access this is called a Query) with the same name that has all the joins you need in place. So after you rename the table, just build a simple query with that table in place (I use the wizard and make sure I include all of the fields):