Create, Update, Delete Patterns with SharePoint Designer Workflow

SharePoint designer workflow invites, indeed, practically begs, non developers to sit down and automate a business process around custom lists and document libraries in SharePoint.

SPD (especially 2010) is an effective tool for building the typical notification, task assignment, and approval-style business solutions that are so common in departments in businesses everywhere. Given that non-developers get involved, they haven't received the classic computer science training (or learned in on the job). As a result, it's easy to forget about the "UD" in "CRUD." CRUD stands for "CReate, Update and Delete."

Even experienced developers often fall into the "create" trap. It's pretty easy to get a business process running and create all the artifacts that the process needs (like entries in custom lists, tasks and notifications), but it's rare for a "create only" business process. Most of the time, we need to allow for changes and cancellations as well. This article explains how to manage the U and D of business solutions using SharePoint designer workflow.

Model Process and Workflow

Let's consider a straight-forward model. Your company's HR department wants to build some control around the new hire process. When HR is made aware of a new hire, we want the following to happen:

• Assign a tasks to IT and Finance (for network credentials and payroll setup respectively)
  
• Update a calendar showing when new hires will join the company
  
• Send email notifications to the IT and finance department, as well as the hiring manager

Before we open up SharePoint designer, we'll create a custom list. This custom list is the core control list of the process. HR creates items here for each new hire, indicating the start date, the hiring manager and other new hire related metadata. It will also include some technical data that our workflows need in order to support the full CRUD paradigm. Specifically, it will include the ID of the actual SharePoint tasks we create with SharePoint designer activities.

It will look something like Figure 1 in SharePoint:

Figure 1: New Hire Fields and Form (SP2010)

Create

The create process is not as simple as it seems because it has to do a lot of work that's not directly related to creating at all. It has to do all the create "stuff" (create tasks, send emails), but it also has to keep track of two important pieces of data:

1. Whether it has run before (we don't want to create duplicate tasks or send duplicate emails).
2. The ID of the tasks it does create. We'll use these IDs later, when the new hire's start date changes.

Update

Give that we've done all the good setup work in the Create workflow, update is pretty straight-forward. We have a convenient reference to the IT and Payroll tasks we created, so we can easily find them and update the new hire's date on those items.

The only trick here is collecting the new hire date and avoiding over-notification.

First, we want to use an initiation parameter. When a new hire's start date changes, manually run the workflow and we'll get prompted for that field, as shown in figure 2:

With the new start date in hand, the workflow can run, locate the IT and Payroll tasks and update the associated new hire start date.

Avoid over-notification (i.e. too many emails) by comparing the new start date with the current start date. If they match, someone probably made a mistake. If emails go out, it would be confusing, so we don't want that. In this case, simply abort the workflow.

Delete

Delete is a piece of cake. As with Update, the delete work should started manually although unlike create and update, it *must* be a manual task. We can't get SPD workflows to execute in response to record deletion. We have the IDs of the tasks and so we can easily delete them. We can send emails notifying users that the new hire is not joining the company after all.

One good trick to avoid accidentally deleting is to provide a "confirm delete" workflow initiation n parameter. Ask the user to supply an answer to that question. The workflow tests for the value and if it's no good, it aborts.

Conclusion

SharePoint forces us to think a little creatively in order to comprehensively solve the CRUD problem. In a sense, we have to "plan for the worst" in our Create operations so that future Update and Delete operations have the data on hand in order to do the right thing for the business process. This solution definitely has some warts. We are forced to maintain a mix of business (e.g. new hire name) and technical information (task IDs) on one list, which is confusing to end users. We can improve the user experience somewhat by hiding the technical fields on data entry forms, but they will still be available when defining views, for instance.

That's a small price to pay, however, in order to provide the full CRUD experience that we all crave.