425-882-1032 info@3sharp.com

This is the final installment in a four-part series about the time-off request solution, an end-to-end Microsoft Office SharePoint Server 2007 solution that uses Microsoft Office Word 2007 as a form application. In the previous post I added a custom document information panel to the Time-Off Request site content type. This post focuses on the development of Microsoft Office SharePoint Designer 2007 workflows that will route task assignments and update time-off balances accordingly.

Office SharePoint Designer 2007 enables you to create rules-based workflow logic for any SharePoint list. For this scenario, I will create two workflows, one each for the Time-Off Request and Time-Off Request Status document libraries. The workflow that is attached to the Time-Off Request document library will copy a new time-off request document to the Time-Off Request Status document library and notify the employee’s manager that there is a request to review. The workflow that is attached to the Time-Off Request Status document library will update the time-off balance information in the Time-Off Balances custom list and notify the employee if the request was approved or rejected. To get started with the Workflow Designer in Office SharePoint Designer 2007:

  1. Click File | Open Site.
  2. In the Open Site dialog box, in the Site name box, type the URL for the site collection (in this case, the Human Resources site collection) and click Open.
  3. Click File | New | Workflow.

In the initial page of the Workflow Designer, you can provide a name for the workflow, choose the list to which you want to attach the workflow, and select the start options. For this scenario, the workflow that is attached to the Time-Off Request document library will start automatically when a new item is created.

New Request Workflow Options

After clicking Next, you can create the rules-based logic in the Workflow Designer. Office SharePoint Designer 2007 workflows are composed of steps, comprising sets of conditions and actions. Each workflow step is processed in the order listed in the Workflow Steps task pane. Sets of conditions and actions are also processed in the order listed within each step. Processing within a step stops when one set of conditions evaluates to true. For this scenario, when the employee submits a new time-off request to the Time-Off Request document library, four actions will occur:

  • The TimeOffStatus value in the document changes from blank to Open. If you recall from the previous post, we added conditional formatting in the document information panel to prevent users from editing the Status, Reason (if “Rejected”), and Total Business Days fields when TimeOffStatus is blank.
  • The document is copied to the Time-Off Request Status document library. Managers use this library to review time-off requests.
  • The document is deleted from the Time-Off Request document library.
  • An email message is sent to the employee’s manager about the new time-off request.

These actions can be added to a single step. No conditions are necessary, since the actions will always occur for each new time-off request. To create the step logic and save the workflow:

  1. Click Actions | Set Field in Current Item. NOTE: If the Set Field in Current Item action or any other action referenced in this post does not appear in the list, click More Actions to find it.
  2. Click field and click TimeOffStatus.
  3. Click value and click Open.
  4. Click Actions | Copy List Item.
  5. Click the first this list link and, in the Choose List Item dialog box, click OK.
  6. Click the other this list link and click Time-Off Request Status.
  7. Click Actions | Delete Item.
  8. Click this list and, in the Choose List Item dialog box, click OK.
  9. Click Actions | Send an Email.
  10. Click this message.
  11. In the Define E-mail Message dialog box, click the lookup button located to the right of the To box.
  12. In the Select Users dialog box, double-click Workflow Lookup.
  13. In the Define Workflow Lookup dialog box, click the Field list, click ManagerEmail, and click OK.
  14. In the Select Users dialog box, click OK.
  15. In the Subject box, type New Time-Off Request.
  16. Click within the body field and click Add Lookup to Body.
  17. In the Define Workflow Lookup dialog box, click the Field list, click ManagerName, and click OK.
  18. Complete the rest of the email message by providing a link to the Time-Off Request Status document library.
    New Request Email Message
  19. In the Define E-mail Message dialog box, click OK.
    New Request Action Set
  20. In the Workflow Designer, click Finish.

As mentioned, a second workflow attached to the Time-Off Request Status document library updates balances in the Time-Off Balances custom list and notifies the employee of the request status. In Office SharePoint Designer 2007, start the Workflow Designer again (File | New | Workflow), and set the start options so that this workflow is triggered only when an item is changed.

Request Status Workflow Options

Click Next to create the step logic. For this workflow, you will need a step to update the Time-Off Balances custom list when the TimeOffStatus value equals Approved and another step to send email notification to the employee regardless of whether the request was approved or not. For the step relating to time-off balances, you will need two sets of conditions and actions: one to update the Vacation Taken column if the TimeOffType value equals Vacation and another to update the Floaters Taken column if the TimeOffType value equals Floating Holiday.

  1. Click Conditions | Compare Time-Off Request Status field.
  2. Click field and click TimeOffStatus.
  3. Click value and click Approved.
  4. Click Conditions | Compare Time-Off Request Status field.
  5. Click field and click TimeOffType.
  6. Click value and click Vacation.
  7. Click Add ‘Else If’ Conditional Branch.
  8. In the new set of conditions and actions, click Conditions | Compare Time-Off Request Status field.
  9. Click field and click TimeOffStatus.
  10. Click value and click Approved.
  11. Click Conditions | Compare Time-Off Request Status field.
  12. Click field and click TimeOffType.
  13. Click value and click Floating Holiday.
    Time-Off Balance Conditions

The actions associated with these conditions require a calculation to be performed. You will need to save to a variable the sum of the TimeOffDays value and the corresponding column in the Time-Off Balances custom list. That variable can then be used to update the correct column in the Time-Off Balances custom list. You can find the specific item to update in the Time-Off Balances custom list by matching the employee names. To create the actions for these conditions:

  1. In the first set of conditions and actions, click Actions | Do Calculation.
  2. Click the first value link and then click the data binding button that appears to the right of that field.
  3. In the Define Workflow Lookup dialog box, click the Field list, click TimeOffDays, and click OK.
  4. Click the other value link and then click the data binding button that appears to the right of that field.
  5. In the Define Workflow Lookup dialog box, click the Source list and click Time-Off Balances.
  6. In the Lookup Details section, click the Field list and click Vacation Taken.
  7. In the Find the List Item section, click the Field list and click Time-Off Balances:Employee.
  8. Click the data binding button to the far right of the Value box.
  9. In the Define Workflow Lookup dialog box, click the Field list, click EmployeeName, and click OK.
    Vacation Lookup
  10. In the Define Workflow Lookup dialog box, click OK.
  11. In the Microsoft Office SharePoint Designer dialog box, click Yes.
  12. Within the same set of conditions and actions, click Actions | Update List Item.
  13. Click this list.
  14. In the Update List Item dialog box, click the List list and click Time-Off Balances.
  15. In the Find the List Item section, click the Field list and click Time-Off Balances:Employee.
  16. Click the data binding button to the far right of the Value box.
  17. In the Define Workflow Lookup dialog box, click the Field list, click EmployeeName, and click OK.
  18. Click Add.
  19. In the Value Assignment dialog box, click the Set this field list and click Vacation Taken.
  20. Click the data binding button to the right of the To this value box.
  21. In the Define Workflow Lookup dialog box, click the Source list and click Workflow Data.
  22. Click the Field list, click Variable: calc, and click OK.
  23. In the Value Assignment dialog box, click OK.
  24. In the Update List Item dialog box, click OK.
  25. In the Microsoft Office SharePoint Designer dialog box, click Yes.
    Vacation Taken Actions
  26. In the second set of conditions and actions, repeat Steps 1-25. In Steps 6 and 19, replace Vacation Taken with Floaters Taken. In Step 22, replace Variable: calc with the name of the variable associated with the second set of conditions and actions (probably Variable: calc1).
    First Workflow Step

The second workflow step simply sends an email message to the employee after the employee’s manager reviews and updates the time-off request. You can send different messages for approvals and rejections. To create the second step and complete the workflow:

  1. In the Workflow Steps task pane, click Add workflow step.
  2. Click Conditions | Compare Time-Off Request Status field.
  3. Click field and click TimeOffStatus.
  4. Click value and click Approved.
  5. Click Actions | Send an Email.
  6. Click this message.
  7. In the Define E-mail Message dialog box, click the lookup button located to the right of the To box.
  8. In the Select Users dialog box, double-click Workflow Lookup.
  9. In the Define Workflow Lookup dialog box, click the Field list, click EmployeeEmail, and click OK.
  10. In the Select Users dialog box, click OK.
  11. In the Define E-mail Message dialog box, in the Subject box, type Time-Off Request Approved.
  12. In the body field, use the Add Lookup to Body button to compose a dynamic message for the employee.
    Request Approved Message
  13. In the Define E-mail Message dialog box, click OK.
  14. Click Add ‘Else If’ Conditional Branch.
  15. In the second set of conditions and actions, repeat Steps 2-13. In Step 4, replace Approved with Rejected. In Steps 11 and 12, change the subject and body to include the fact that the request was rejected.
    Request Rejected Message
  16. In the Workflow Designer, click Finish.

This post concludes my description of the time-off request solution. Keep in mind that I used no code throughout the four parts in this series. Certainly, the solution could be made more robust with add-ins or a Microsoft Visual Studio 2005 workflow. But the point of this series was to show an easy form-development experience for Office Word 2007, an application familiar to many users.