Export (0) Print
Expand All
Expand Minimize

Extending SharePoint Team Services with Workflow

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
 

Koji Kato, Theresa Venhuis, Amit Chopra
Microsoft Corporation

April 2001

Applies to:
   Microsoft® Office XP Developer

Summary: The SharePoint™ team Web sites in Office XP provide a place on the Web where your team can communicate, share documents, and work together on a project. This article describes how to enhance the SharePoint team Web sites by enforcing business rules through workflow processes. Advanced issues, such as security and image integration (signatures, endorsement stamps), also are described in this article. (14 printed pages)

Download Spclb.exe.


Contents

Introduction
Adding Workflow to a SharePoint List
Enhancing Workflow with Notifications
Enhancing Workflow with Security
Enhancing Workflow with Image Integration

Introduction

This article focuses on enhancing List and Document Library features in the SharePoint team Web site. Lists and document libraries in SharePoint team Web sites are stored physically as tables on Microsoft® SQL Server™ and both will be referred to as SharePoint lists hereafter. Microsoft Office XP Developer provides the Workflow Designer for SQL Server, which is used to workflow enable tables in Microsoft SQL Server.

Readers are assumed to have familiarity with SharePoint team Web sites, Microsoft SQL Server management, Visual Basic® Scripting Edition (VBScript), and ActiveX® Data Objects (ADO). Microsoft SQL Server 2000 must be installed on the computer where the SharePoint team Web sites will be created. This requirement comes from Microsoft Office XP Developer prerequisites. The recommended operating system for this article is Microsoft® Windows® 2000.

This article assumes the following:

  • A standard SharePoint team Web site has been established for the Default Web site on the computer where SharePoint team Web sites will be created.
  • The database for the SharePoint team Web site will be on the same computer.
  • You are working on the computer where SharePoint team Web sites will be created with Microsoft Office XP Developer fully installed including Workflow Services for SQL Server.
  • The SMTP service is installed correctly and configured to send e-mail.

The sample code provided is designed to work only when following all the steps provided in sequential order in this article, and cannot be guaranteed to run correctly in another solution.

Adding Workflow to a SharePoint List

The following are the five steps to add workflow to SharePoint lists:

  1. Create a SharePoint list.
  2. Add workflow.
  3. Customize the SharePoint team Web site.
  4. Change the table design.
  5. Confirm enforcement of business rules.

Creating a SharePoint List

A SharePoint list can be created with a browser.

  1. Navigate to your SharePoint team Web site.
  2. Select Lists on the top of the page to navigate to the Lists page.
  3. Select New List to navigate to the Create page.
  4. Select Custom List to navigate to the New List page.
    • Enter WFL in the Name field.
    • Enter List with Workflow in the Description field.
    • Select Yes to display this list on the Quick Launch bar.
  5. Select Create.

Adding Workflow

Workflow can be added to the SharePoint list from the Workflow Designer for SQL Server.

  1. From the Start menu, select Programs, Microsoft Office XP Developer, and then Microsoft Development Environment.
  2. From the File menu, select New, and select then Project, to open the New Project dialog box.
    • Select Office Developer Projects in the set of Project Types icons.
    • Select SQL Server Workflow Project in the set of Templates icons.
    • Enter SharePoint in the Name text box.
    • Leave the location of the project as the default.
  3. Click OK, to open the Create a new SQL Server Workflow Project dialog box.
    • Select the computer on which your SharePoint team Web site resides in the Select a Server drop-down list box.
    • Select the database computer _LM_W3SVC_1_Collab for your SharePoint team Web site.
  4. Click OK to open the Database Registration dialog box.
  5. Click OK to proceed with adding workflow to your SharePoint database.
  6. Right-click SharePoint in the Solution Explorer, and select Add New Item to open the Add New Item dialog box.
    • Select SQL Workflow Items in the set of Categories icons.
    • Select Add a Workflow Process in the set of Templates icons.
  7. Click Open to open the Add Workflow Process dialog box.
    • Select ows_Project1_u_WFL in the Main or detail user table drop-down list box.
  8. Click OK to open a new workflow process for editing.
  9. Select the State shape in the Toolbox, and click the design surface (where Item Created and Item Deleted are placed by default) to create the following states.
    • State1
    • State2
    • State3
    • State4
  10. Modify the Name and Caption state properties in the Properties Window while selecting each state on the design surface, as follows:
    State
    Change Name to
    Change Caption to
    State1
    Draft
    Draft
    State2
    Reviewer1
    Reviewer1
    State3
    Approved
    Approved
    State4
    Rejected
    Rejected
  11. Click the Transition shape in the Toolbox, and select the states you want to connect (From, To) on the design surface to create the following transitions:
    From
    To
    Transition
    Item Created
    Draft
    Create1
    Draft
    Reviewer1
    Change1
    Reviewer1
    Approved
    Change2
    Reviewer1
    Rejected
    Change3
    Draft
    Item Deleted
    Delete1
    Approved
    Item Deleted
    Delete2
    Rejected
    Item Deleted
    Delete3
  12. Click the Transition Within shape in the Toolbox, and click the Draft state to create transition Change4.
  13. Right-click the design surface, and from Arrange Diagram, select Vertical.
  14. Modify the Name and Caption transition properties in the Properties Window while selecting each transition on the design surface, as follows:
    Transition
    Change Name to
    Change Caption to
    Create1
    Create
    Create
    Change1
    Submit
    Submit
    Change2
    Approve
    Approve
    Change3
    Reject
    Reject
    Change4
    Edit
    Edit
  15. Select ows_Project1_u_WFLWorkflow in the Solution Explorer, and make sure the EnableWorkflow property is True in the Properties Window. If it is False, select True from the drop-down list box.
  16. Select ows_Project1_u_WFDWorkflow in the Solution Explorer, and make sure the DeletedRowsAvailable property is True in the Properties Window. If False, select True from the drop-down list box.
  17. From the File menu, select Save All.

Customizing the SharePoint Team Web Site

The customization features built-in to SharePoint are used to create user interface elements with which users interact with workflow.

  1. Navigate to your SharePoint team Web site.
  2. Select Lists on the top of the page to navigate to the Lists page.
  3. Select New List to navigate to the Create page.
  4. Select Custom List to navigate to the New List page.
    • Enter State for WFL in the Name field.
    • Enter Lookup list for states in the Workflow in the Description field.
    • Select No to not display this list on the Quick Launch bar.
  5. Select Create to navigate to the State for WFL page.
  6. Select New Item to navigate to the New Item page, and create entries with the following titles by hitting Save and Close. The entries must be created in the following order:
    • Draft
    • Reviewer1
    • Approved
    • Rejected
  7. Select Lists on the top of the page to navigate to the Lists page.
  8. Select WFL to navigate to the previously created list.
  9. Select Modify settings and columns to navigate to the Customize page.
  10. Select Add a new column in the Columns section of the Customize page to navigate to the Add Column page.
    • Enter State in the Column name field.
    • Select Lookup as the type of information.
    • Select State for WFL in the Get information from drop-down list box.
    • Click OK to navigate back to the Customize page.
  11. Select Add a new column in the Columns section of the Customize page to navigate to the Add Column page.
    • Enter Reviewer1 in the Column name field.
    • Select Lookup as the type of information.
    • Accept the default User Information for Get information from drop-down list box.
    • Click OK to navigate back to the Customize page.

Changing the Table Design

A minor change in the table design is required to hook up the user interface elements that were created using the built-in customization features of SharePoint to the workflow.

  1. From the Start menu, select Programs, Microsoft SQL Server, and then Enterprise Manager.
  2. Expand the following nodes: Console Root | Microsoft SQL Server | SQL Server Group | computer | Databases | computer _LM_W3SVC_1_Collab | Tables.
  3. Right-click ows_Project1_u_WFL, and select Design Table.
  4. Select the column named modStateId, and enter the following formula in the formula property.
    ([tp_State])
    
  5. Click Yes on the deleting relationship warning dialog box to override the warning.
  6. Click the Save icon to apply the design change.
  7. Click Yes in the Save dialog box to confirm the changes.

Confirming Enforcement of Business Rules

Now you have completed adding workflow to a SharePoint list. To confirm the enforcement of business rules:

  1. Select New Item, and create an item with the title Enforcement1 and the state (None) by clicking Save and Close. Confirm failure in creating a record (the state is not valid).
  2. Select New Item, and create an item with the title Enforcement2 and the state Draft by clicking Save and Close. Confirm success in creating a record.
  3. Select Enforcement2 to open the list item. Select Edit Item to make the list item editable, change the state to Approved, and save by clicking Save and Close. Confirm failure in updating the record (this is not a valid state transition).
  4. Select Enforcement2 to open the list item. Select Edit Item to make the list item editable, change the state to Reviewer1, and save by clicking Save and Close. Confirm success in updating the record (this is a valid state transition).
  5. Select Enforcement2 to open the list item. Select Edit Item to make the list item editable, change the state to Approved, and save by clicking Save and Close. Confirm success in updating the record (this is a valid state transition).

Enhancing Workflow with Notifications

Notifications coordinate the interaction of reviewers. This article goes on to add notifications to the workflow defined for the SharePoint list.

This article will address workflow specific user management issues, and give instructions to add notifications to the workflow.

Workflow User Management

The Workflow Designer for SQL Server adds a user management table to the SQL Server independently. This user management table is generated from SQL Server Login properties periodically.

To add notifications to the SharePoint list, this user management table must have information for all members in the SharePoint user list.

The following steps set SQL Server Login properties.

  1. From the Start menu, select Programs, Microsoft SQL Server, and then Enterprise Manager.
  2. Expand the following nodes: Console Root | Microsoft SQL Server | SQL Server Group | computer | Security.
  3. Right-click Logins, and select New Login, to open the SQL Server Login Properties – New Login dialog box.
  4. Select Windows Authentication, and then select the user's domain from the Domain drop-down list box.
  5. Enter the NT account of the user in the Name text box. If you must search for users, select to open a SQL Server Login Properties – New Login dialog box where you can look up users.
  6. Select the Database Access tab.
  7. Select computer _LM_W3SVC_1_Collab. This grants access to the SharePoint database.
  8. Click OK to return to Enterprise Manager.
  9. Repeat steps 3 through 8 to add all members.
  10. After all members have been added, launch Workflow Manager for SQL Server from Start menu, select Programs, Microsoft Office XP Developer, and then select Workflow Manager for SQL Server.
  11. Select the User Information tab.
  12. Select Synchronization to open the User Directory Synchronization Settings dialog box. If an Exchange 5.5 Server is running on your network, please select Synchronize with Exchange 5.5, and specify the server name in the Exchange Server text box. If an Exchange 2000 Server is running on your network or Active Directory is setup, select Synchronize with Exchange 2000 or Active Directory. If neither Exchange 5.5 nor Exchange 2000 / Active Directory is running on your network, select Synchronize without Directory Services.
  13. Click OK to return to Workflow Manager for SQL Server.
  14. Select Synchronize Now to update the user management table.
    Note   If synchronization fails, try rechoosing Synchronize without Directory Services in step 12.
  15. If you chose Synchronize without Directory Services in step 12, select each user and select Properties to open the User Properties dialog box.
    • Enter the email address in the E-mail text box.
    • Specify the manager of the user in the Manager text box.
    • Click OK.

Adding Notifications

This article provides a code librarian database with helper functions to workflow enable SharePoint lists.

The following steps add code to send notifications to the specified reviewer when an item is submitted to Reviewer1 state; to the author when an item is approved to Approved state; or to the author when an item is rejected to Rejected state

  1. From the Start menu, select Programs, Microsoft Office XP Developer, and then select Microsoft Development Environment.
  2. From the File menu, select Recent Projects to open the SharePoint solution.
  3. Double-click ows_Project1_u_WFLWorkflow in the Solution Explorer to open the workflow process.
  4. From the View menu, select Code Librarian.
  5. From the Code Librarian menu, select Add, and then select Existing Library to launch the Open dialog box.
  6. Specify SharePoint.CLB in the sample, and select Open to open the SharePoint code library.
  7. Expand the SharePoint.CLB node in Code Librarian.
  8. Select Common in Code Librarian.
  9. From the Edit menu, select Copy.
  10. Right-click the design surface, and select View Code.
  11. From the Edit menu, select Paste to paste the helper functions into the script editor.
  12. Create the following transition event handlers.
    Note   spSendUserInfoMail is a helper function that accepts a column name with an index value of which user to e-mail, a string for the e-mail subject, and a string for the e-mail body. The index value is a foreign key into the SharePoint user table.
    Sub Submit_OnChange
       spSendUserInfoMail "tp_Reviewer1", "Submission", "An item is awaiting your approval." 
    End Sub
    
    Sub Approve_OnChange
       spSendUserInfoMail "tp_Author", "Approval", "Your item has been approved." 
    End Sub
    
    Sub Reject_OnChange
       spSendUserInfoMail "tp_Author", "Rejection", "Your item has been rejected." 
    End Sub
    
  13. From the File menu, select Save All to save the workflow.

Confirming Notifications

Now, you have completed adding notifications to the SharePoint list. You can confirm this after navigating to the created list by the following:

  1. Select New Item, and create an item with the title Enforcement3 and the state Draft by clicking Save and Close. Confirm success in creating a record.
  2. Select Enforcement3 to open the list item. Select Edit Item to make the list item editable, change the state to Reviewer1, change reviewer1 to yourself, and save by clicking Save and Close. Confirm a notification being received.
  3. Select Enforcement3 to open the list item. Select Edit Item to make the list item editable, change the state to Approved, and save by clicking Save and Close. Confirm a notification being received.

Enhancing Workflow with Security

A key aspect of workflow is identity. Identity can be important in cases such as when a list item should only be editable by the current reviewer, and only the author can submit the list item to a workflow.

Note   SharePoint also provides security features, but more granular security (for example, dependent on current state) is required in workflow.

The following is a brief overview of SharePoint behavior and instructions to add security to the workflow.

SharePoint Behavior

SharePoint accesses its database using an agent account set in the administration pages. From a database perspective, the NT account with which updates are executed is fixed always, and the current user updating the database is not identifiable from standard SQL Server security features.

The identity of users updating the database is available as a field value in SharePoint lists. Column tp_Author holds a foreign key value into the SharePoint user list for the author of the list item, and tp_Editor holds a foreign key value into the SharePoint user list for the last user who edited the list item.

This identity information can be leveraged to write constraints in the workflow to set security.

Adding Security to Workflow

Security helper functions are among the code that was copied from SharePoint.CLB.

The following steps add code to constrain database updates when:

  • A list item is submitted for review, and the current user is not the author.
  • A list item is approved, and the current user is not the reviewer.
  • A list item is rejected, and the current user is not the reviewer.

Notifications are sent to the current user in all cases, indicating that the update has failed.

  1. From the Start menu, select Programs, Microsoft Office XP Developer, and then select Microsoft Development Environment.
  2. From the File menu, select Recent Projects to open the SharePoint solution.
  3. Double click ows_Project1_u_WFLWorkflow in the Solution Explorer to open the workflow process.
  4. Right-click the design surface, and select View Code.
  5. Create the following transition event handlers.
    Note   spUserInfoValidate is a helper function that validates if the current user is the user specified in a SharePoint list field and returns a Boolean result. If the result is false, a notification is sent with the specified title and body. spNextReviewerValidate is a helper function that validates if a SharePoint list field holds a valid foreign key to the SharePoint user list and returns a Boolean result. If the result is false, a notification is sent with the specified title and body.
    Function Submit_OnChangeValidate 
       Submit_OnChangeValidate = spUserInfoValidate("tp_
    Author", "Submission Failure", "The item could not be submitted.") and spNextReviewerValidate("tp_
    Reviewer1", "Submission Failure", "The item could not be submitted since a valid reviewer is not specified.") 
    End Function
    
    Function Approve_OnChangeValidate 
       Approve_OnChangeValidate = spUserInfoValidate("tp_
    Reviewer1", "Approval Failure","The item could not be approved.")
    End Function
    
    Function Reject_OnChangeValidate 
       Reject_OnChangeValidate = spUserInfoValidate("tp_
    Reviewer1", "Rejection Failure", "The item could not be rejected.") 
    End Function
    
  6. From the File menu, select Save All to save the workflow.

Confirming security

Now, you have completed adding security to the SharePoint list. You can confirm this by navigating to the created list and following these steps.

  1. Logon as some user (user A), and select New Item to create an item with the title Enforcement4 and the state Draft by clicking Save and Close. Confirm success in creating a record.
  2. Logon as some user (user B), and navigate to the created list. Select Enforcement4 to open the list item. Select Edit Item to make the list item editable, and change the state to Reviewer1, change reviewer1 to user A, and save by clicking Save and Close. Confirm failure in updating the record and notification being received by user B that states submission has failed.
  3. Logon as user A, and navigate to the created list. Select Enforcement4 to open the list item. Select Edit Item to make the list item editable, and change the state to Reviewer1, change reviewer1 to user B, and save by clicking Save and Close. Confirm success in updating the record and notification being received by user B that states submission has succeeded.
  4. Logon as user A, and navigate to the created list. Select Enforcement4 to open the list item. Select Edit Item to make the list item editable, change the state to Approved, and save by clicking Save and Close. Confirm failure in updating the record and notification being received by user A that states approval has failed.
  5. Logon as user B, and navigate to the created list. Select Enforcement4 to open the list item. Select Edit Item to make the list item editable, change the state to Approved, and save by clicking Save and Close. Confirm success in updating the record and notification being received by user A that states approval has succeeded.

Enhancing Workflow with Image Integration

There are certain scenarios where visual representation of approval is preferred (for example, signature, endorsement stamps) in workflow. This article goes on to describe one approach to add such functionality to SharePoint lists.

Creating an Image Registry

The definition of an image registry here is a list of images that users commonly use. An image registry can easily be created as a SharePoint list.

  1. Navigate to your SharePoint team Web site.
  2. Select Lists on the top of the page to navigate to the Lists page.
  3. Select New List to navigate to the Create page.
  4. Select Custom List to navigate to the New List page.
    • Enter Image Registry in the Name field.
    • Enter List of images in the Description field.
    • Click Yes to show this list on the Quick Launch bar.
  5. Select Create to navigate to the Image Registry page.
  6. Select Modify settings and columns to navigate to the Customize page.
  7. Select Add a new column in the Columns section of the Customize page to navigate to the Add Column page.
    • Enter Image in the Column name field.
    • Select Hyperlink or Picture as the type of information.
    • Select Picture for Format URL as the drop-down list box.
    • Click OK, to navigate back to the Customize page.
  8. Select Go back to "Image Registry" to return to the Image Registry page.

Now that the image registry has been created, follow these steps to add images that will be used.

  1. Navigate to your SharePoint team Web site.
  2. Select Lists on the top of the page to navigate to the Lists page.
  3. Select Image Registry to navigate to the previously created list.
  4. Select New Item to navigate to the New Item page.
    • Enter a title for the image in the Title field.
    • Enter a URL to the image in the Image field.
    • Click Save and Close to navigate back to the Image Registry page.
  5. Repeat step 4 for all images. Users can choose how their approval should be visualized from this list of images. Scanned images of signatures or endorsement stamps should be added here.
    Note   Images must be located where HTTP access is possible in order to be added to the image registry.

Creating Fields that Work in Conjunction with the Image Registry

These steps have two purposes: to create a field with which users can choose from the image registry (a drop-down list box) and to create a field with which users can see the images and add some code to the workflow that updates the field for showing the image whenever the field for choosing the image changes.

Adding fields

  1. Navigate to your SharePoint team Web site.
  2. Select Lists on the top of the page to navigate to the Lists page.
  3. Select WFL to navigate to the previously created list.
  4. Select Modify settings and columns to navigate to the Customize page.
  5. Select Add a new column in the Columns section of the Customize page to navigate to the Add Column page.
    • Enter Select1 in the Column name field.
    • Select LookUp as the type of information.
    • Select Image Registry for the Get information from the drop-down list box.
    • Select Add to default view.
    • Click OK to navigate back to the Customize page.
  6. Select Add a new column in the Columns section of the Customize page to navigate to the Add Column page.
    • Enter Image1 in the Column name field.
    • Select Hyperlink or Picture as the type of information.
    • Select Picture for the Format URL as drop-down list box.
    • Click OK, to navigate back to the Customize page.
  7. Select Go back to WFL to return to the WFL page.

Adding code to workflow

  1. From the Start menu, select Programs, Microsoft Office XP Developer, and then select Microsoft Development Environment.
  2. From the File menu, select Recent Projects to open the SharePoint solution.
  3. Double click ows_Project1_u_WFLWorkflow in the Solution Explorer to open the workflow process.
  4. Right-click the design surface, and select View Code.
  5. A procedure that updates the field for showing the images based on the field for selection is added.
    Sub ProgrammaticUpdate
          modPropSet "tp_Image1", spTableLookup("ows_
    Project1_u_ImageRegistry", "[tp_
    ID] = " & modProp("tp_Select1"), "tp_Image")
          modPropUpdate
    End Sub
    
  6. This procedure must be called when an item is approved or rejected, thus the transition event handlers require modification.
    Sub Approve_OnChange
       ProgrammaticUpdate
       spSendUserInfoMail "tp_Author", "Approval", "Your item has been approved." 
    End Sub
    
    Sub Reject_OnChange
       ProgrammaticUpdate
       spSendUserInfoMail "tp_Author", "Rejection", "Your item has been rejected." 
    End Sub
    
  7. From the File menu, select Save All to save the workflow.

Confirming Image Integration

Now, you have completed integrating images to the SharePoint list. This can be confirmed by the following steps:

  1. Select New Item to create an item with the title Enforcement5 and the state Draft by clicking Save and Close. Confirm success in creating a record.
  2. Select Enforcement5 to open the list item. Select Edit Item to make the list item editable, change the state to Reviewer1, change reviewer1 to yourself, and save by clicking Save and Close. Confirm success in updating the record.
  3. Select Enforcement5 to open the list item. Select Edit Item to make the list item editable, change the state to Approved, change select1 to some image, and save by clicking Save and Close. Confirm success in updating the record and the selected image showing on the list.
Show:
© 2014 Microsoft