Microsoft Access Workflow Designer Document-Library Sample Template

David Johnson and Doug Yoder
Microsoft Corporation

January 3, 1999

Click here to open or copy the utilities discussed in this technical article.

Summary: This article describes the Document Library sample template for the Microsoft Access Workflow Designer for SQL Server 7.0. (18 printed pages)

Introduction

Managing and reviewing documents can be an important part of the interaction within a department. Frequently, when several people in a department must review a document, the document is e-mailed to the reviewers with a request that the review be completed by a certain date. Many times, document feedback and overall approval/rejection is requested. Other times, the document is printed out and passed manually in sequence to each reviewer for comments and approval.

Unfortunately, these methods can cause a breakdown in the review process. Reviewers might not return their review comments in a timely manner, or the printed copy of the document with the comments and approvals can be misplaced or lost. In addition, the document owner does not know the state of the review process until the final deadline. The owner might find out on the due date that only one person has reviewed the document and marked the approval, causing a significant delay in the review process.

What is needed is a solution that tracks the review process, ensuring each reviewer completes the review in a timely manner. Also, the solution must make it possible for the document owner to view the state of the review at any time during the process.

The Microsoft® Access Workflow Designer for Microsoft SQL Server™ makes it possible for developers to build Web-based workflow and tracking solutions. The Document Library, developed using the Access Workflow Designer, provides a way for a department to organize documents and automate the document review processes.

Organize Documents

Documents are added to the library by providing a reference to the document on a network file share or using a hyperlink (URL) to the document. The documents are not moved physically into the Document Library but remain in their original locations. The Document Library provides a way to reference the documents in their original locations.

When documents are added to the library, an owner of the document is specified, along with a category and sub-category. The category and sub-category items are useful for organizing related documents together.

The Document Library provides several different pages that list the documents grouped in different manners, such as by owner, category, and status. These pages make it easy to find specific documents quickly.

Document Review Process

The Document Library uses the Access Workflow Designer to provide an automated document review process. Document owners can start a review for a document, pick a list of reviewers and a due date, and specify instructions for the reviewers. In addition, they can specify whether review tasks should be routed to each reviewer in sequence or all at the same time. They can also choose from several criteria that determine whether the document should be approved, rejected, or revised.

Installing and Working with the Template

First, the Document Library template, MOD_DocumentLibrarySample.tpl, must be imported into the template library on a server that has the Access Workflow Designer server components. It can then be used to create instances of Document Library solutions. Import the template into the template library using the Team Solutions Manager. Create new solutions from the template using the New Team Solution wizard. For details about using templates with Access Workflow Designer, consult the Access Workflow Designer Administrator’s Guide online Help.

This sample uses the Microsoft Office 2000 Workflow Toolbar control. Pages that use this control must be edited to correct the toolbar control database connection string; otherwise, the Document Library will not function correctly. To correct this problem, you must edit each HTML page that uses the toolbar with the Access 2000 data access page designer. The following pages in the Document Library solution’s Web site use the toolbar: DocDetail.htm, ReviewDetail.htm, and ReviewItem.htm. Opening each page in the designer in Design view and saving it will correct the problem.

Document Library Home Page

When the Document Library is started, the home page is displayed. The home page provides summary information of the documents in the Document Library. In addition, it provides navigational access to different views of the documents.

Welcome Text

The welcome text informs you to which roles you belong. This information is important, because the role you are a member of defines the tasks you have permissions to perform. For a complete description of the roles and the permissions associated with each of them, see Roles.

Reviews and Documents

While there might be a large number of documents in the Document Library, you do not want to have to sift through tons of documents to find those you must review or modify. By glancing at the Reviews and Documents section, you can see quickly what documents are important to you.

The Reviews section shows you the number of documents, if any, that you must review. To list the documents you must review, click the hyperlink listing the number of documents. In addition, this section shows you the number of High, Normal, and Low priority reviews that are assigned to you. To see only the high priority reviews, click the high priority hyperlink.

The Documents section lists the number of documents you own in the Document Library. To see these documents, click the hyperlink listing the number of documents. This provides a fast way to find documents on which you might be currently working.

Status of All Documents

To see the document review status of all the documents in the Document Library, the last section on the home page provides a graphical chart that summarizes the statuses of the documents in the library.

Go To List

The Go To list in the upper-right corner of the home page is the main navigational control. By using the Go To list, you can navigate quickly to any of the different pages that list the documents in the Document Library. In addition, the Go To list makes it possible for you to return to the home page. To use the control, select the desired view from the list, and you will be navigated to that page.

Figure 1. Go To list

Figure 2. Home page

Submitting a Document

A document must be submitted before it can be listed in the Document Library. Submitting a document defines a reference in the Document Library to a document that is physically in another location, such as a network file share or a hyperlink (URL) location. When you submit a document, you also specify the owner of the document and what category to associate with the document.

When a document first is submitted in the Document Library, it will be in the Unreviewed state automatically. If you are in the ContentManager role, you can approve or reject the document immediately without starting a review process.

When submitting a document, the Document Details page is used to enter document information. Along with the standard Go To list, the Document Details Web page contains a toolbar directly above the document information that lists each of the different states in which a document can be. This functionality is provided by the Microsoft Office Developer Workflow Toolbar control. The Workflow Toolbar control, included with the Access Workflow Designer, provides an easy-to-use interface for users of the application without requiring any extra coding.

Figure 3. Document Details page

To submit a document

  1. On the Document Library home page, click Submit a new document.

    The Document Details page, which is used to add and to modify details of the document, is displayed.

  2. Enter the document details.

    Note   The URL field can contain a reference to an internal network share, such as \\hrServer\Handbook\Employee.doc, in addition to a standard Web page URL.

  3. Click the Save button (shown below) on the Record Navigation control at the bottom of the page.

If you belong to the ContentManager role, the Workflow Toolbar control is enabled to make it possible for you to change the status of the document immediately. Otherwise, a review must be started before the status of a document can be changed.

From the Document Details page, you also can start a new review of the newly entered document by clicking Start a new review.

Starting a Document Review

After a document has been submitted to the Document Library, you can start an automated document review process for that document. When starting a new document review, you first specify a due date and include any instructions for the reviews. Next, you specify the timing of the document review and finally the list of people in your department who should review the document.

Figure 4. Starting a document review

To start a document review

  1. From the home page, click Start a new review. The New Review page appears.

  2. Select the document to review from the Document list.

    Note   The documents listed will be only those documents on which you have permissions to begin a review.

  3. Enter the date you want the review to be completed in the Due Date field.

    The Due Date entered is the date you expect the reviewers to complete the review. If a reviewer does not complete the review by this date, the review task is marked incomplete, and, if the review is in sequence, the document is routed to the next reviewer.

  4. Enter a subject of the review in the Subject of this review field.

  5. Select a priority from the Priority list.

  6. Enter in any comments or instructions you want to give to the reviewers in the Comment or instructions for reviewers field.

  7. Specify when you want the document to be marked approved in the Mark document approved if list. The following options are available:
  8. Select End review when criteria is met if you want the document review to be stopped as soon as the criteria specified previously is met. Otherwise, if this is not selected, the document is marked as approved when the criteria is met, but the document review continues until all reviewers have completed the review.

  9. Specify when you want the document sent to each reviewer by selecting one of the following options:
  10. Click Next to continue to the next page.

  11. Select a reviewer to add to the process in the Pick one or more reviewers list. Then, click Add to add the reviewer as a required or optional reviewer.

    Figure 5. Selecting reviewers

  12. If the review is to be completed in sequence by the reviewers, use the Move Up and Move Down buttons to change the order of the reviewers. The required reviewers are always first in sequence, followed by the optional reviewers.

  13. Click Finish to start the review.

Reviewing a Document

After reviewers have been assigned to review a document, they receive an e-mail message that contains a link to the document to review and a link to a reviewer’s form that must be filled out when the review is finished. To review the document, the reviewer clicks the link to the document.

Alternatively, the reviewer can go to the home page of the Document Library solution and select my current review tasks from the Go To list, which displays the Current Review Tasks page. To review a document, click the icon next to the document title.

Figure 6. Current Review Tasks page

Reviewers can comment on a document in several ways. If your server has the Office Server Extensions installed, users can use Web discussions. If they have editing permissions on the document, they can put comments or changes directly in the document.

Completing a Document Review

When the reviewer has completed reviewing the document, the reviewer must approve the document, reject the document, or request changes be made. In addition, the reviewer can attach comments to the document. After a document review has been completed, the document owner and the person who started the review receive an e-mail notification, and the document’s status is updated to reflect the outcome of the review.

Note   If multiple reviews on the same document are in progress at the same time, the last review finished determines the document’s status.

If the reviewer does not complete the review by the due date, then the review task is marked as incomplete automatically, and, if the review is a sequence of reviewers, the document is routed to the next reviewer.

To complete a document review

  1. Display the Current Review Tasks page by selecting my current review tasks from the Go To list on the home page. Next, click the title of the document for which you want to complete the review.

    -or-

    From the e-mail you received, click the link at the top of the e-mail for filling out the reviewer form.

  2. Click Approve Document, Request Changes, or Reject Document on the Workflow Toolbar control.

  3. To attach comments to the document, enter them in the Reviewer’s comments for this document field.

  4. Click Save on the Record Navigational control.

Canceling a Document Review

After a review has been started, the person who started the review or the document owner can cancel it. The review must be in-progress before it can be canceled.

To cancel a document review

  1. Select the in-progress document review to be canceled. See Listing Documents for details.

  2. Click the review subject.

  3. On the Workflow Toolbar control, click CancelReview.

  4. Click Save on the Record Navigational control.

Deleting a Document Review

When a review has been completed, the document owner can delete the review.

To delete a document review

  1. Select the completed document review to be deleted. See Listing Documents for details.

  2. Click the review subject.

  3. On the Workflow Toolbar control, click Delete.

  4. Click Save on the Record Navigational control.

  5. Click OK to verify the deletion.

Listing Documents

The documents that exist in the Document Library can be viewed in several different ways, including sorting by Category, Owner, and Status. No matter how the documents are listed, the view makes it possible for you to see the document reviews associated with each document.

Figure 7. Document list

To list documents

  1. From the Document Library home page, select the desired page from the Go To list.

    The available pages include: All documents, All documents grouped by status, All documents grouped by category, All documents grouped by owner, My documents, My documents grouped by status, My documents grouped by category, and My documents grouped by owner.

  2. The documents are listed alphabetically (within each group, if a grouped page was selected).

  3. To view any document reviews associated with a document, click the expand (plus/minus) button next to the document.

Conclusion

The Document Library solution provides a great solution for organizing documents within a department, as well as automating document review processes. The solution offers users in a department an easy way to reference documents in a central location, while keeping the documents in their original locations. In addition, using the automated document review processes, document reviews can be completed in a timely manner following the proper process before completion.

For More Information

For the latest information about Microsoft Access Workflow Designer for SQL Server, see the Microsoft Office Developer Web site at http://msdn.microsoft.com/OfficeDev/.

To access Knowledge Base information, consult the Product Support section of the Office Developer Web site.

For additional information about developing team solutions, consult the Access Workflow Designer Developer’s Guide online documentation.

Appendix—How the Solution Was Created

This section describes the technical details of the Document Library solution.

Schema and Workflow

The Document Library has a single main table—Documents. This table stores the list of documents registered in the Document Library. Associated with Documents is a detail table called Reviews, which stores a list of reviews for each document. The Reviews table has a detail table called ReviewItems, which stores a list of review tasks—one task per user for each review.

Each of these tables has an associated workflow process. The processes were developed using the Access Workflow Designer for SQL Server 7.0. The Documents workflow tracks the document from when it is registered in the library through revision and approval or rejection. The Reviews workflow tracks a review process from beginning to end, and the ReviewItems workflow tracks the review task assigned to each reviewer.

When a review is started, a single row is created in the Reviews table, and a row for each reviewer is created in the ReviewItems table. As each ReviewItem workflow completes, the review’s approval criteria are checked against the status of the ReviewItems. When the criteria are met, the row in the Review table is updated to reflect the outcome of the review, and the document status is updated to reflect the outcome of the review, unless other reviews for the same document are still in progress.

The Document Library conducts reviews using e-mail. When users are assigned a document to review, they receive a review task e-mail message that contains a link to the document and a reviewer’s form they should fill out when finished. When a review has been completed, the document owner and the person who started the review receive e-mail notifications, and the document’s status is updated to reflect the outcome of the review. If multiple reviews on the same document are in progress at the same time, the last review that finishes determines the document’s status.

The Document Library uses the Microsoft Windows NT® 4.0 Simple Mail Transfer Protocol (SMTP) Services to send e-mail messages. This service must be properly configured and running for e-mail notifications to be delivered.

Reviewers can comment on a document in several ways. They can use the Web discussions feature of the Office 2000 Server Extensions. Or, if they have editing permissions on the document, they can put comments or changes directly into the document. In addition, reviewers can add a comment to the Document Library reviewer’s form.

When a review is started, the user specifies a due date when the review must be completed. If the document is routed to each reviewer in sequence, this time is divided evenly among all reviewers. If one reviewer does not finish on time, then his or her review task is marked incomplete, and the document is routed to the next reviewer. If the document is routed to all reviewers at the same time, then everyone has the same due date, and reviewers who do not finish on time will have their review tasks marked incomplete.

User Interface

The user interface for this sample is based on data access pages and was developed using Access 2000 and the Microsoft Script Editor.

Roles

Each user who connects to the library is a member of one or more roles. The actions that users can perform depend on the roles to which they belong. The Document Library home page shows a list of roles to which the current user belongs. The roles are described here:

Public—Members can view information about documents and reviews but cannot submit or review documents.

Contributor—Members can submit documents into the library and start or cancel reviews on their documents.

Reviewer—Members can review documents that have been submitted to the library.

ContentManager—Members can approve or reject any document without requiring a review, and they can start or cancel reviews on all documents.

DB_Owner—Members have full administrative capability over the Document Library database and Web site.

Customizing the Document Library

The Document Library provides a configuration page that makes it possible for db_owners to customize the category and subcategories. To make other changes to the library, such as adding or removing users, changing the database schema, and customizing Web pages, you can use Access 2000 or other database and Web design tools. You can use Access Workflow Designer for SQL Server 7.0 to customize the workflow that drives the review process.

File List

File Description
alldocs.htm Data access page that lists documents sorted by title.
categories.htm Data access page that makes customization of categories possible.
categories_files\filelist.xml File list for categories.htm.
currevitems.htm Data access page that lists the user’s review items.
currevitems_files\filelist.xml File list for currevitems.htm.
default.htm Document Library home page.
default_files\filelist.xml File list for default.htm.
docdetail.htm Data access page for viewing or editing document details.
docsbycat.htm Data access page that lists documents grouped by category.
docsbyowner.htm Data access page that lists documents grouped by owner.
docsbystat.htm Data access page that lists documents grouped by status.
GetReviewInfo.htm Data access page for the first screen of the new review wizard.
GetReviewInfo_files\filelist.xml File list for getreviewinfo.htm.
intro.htm HTML page that provides users and administrators information about the Document Library.
modConStr.vbs Script file automatically created when the solution is created that stores the SQL Server database name.
modwc.cab A .cab file that contains the Workflow Toolbar control.
NewReview.htm HTML page that loads the new review wizard pages in frames.
newreviewdone.htm Final screen of the new review wizard.
PickReviewers.htm Second screen of the new review wizard.
PickReviewers_files\filelist.xml File list for pickreviewers.htm.
Review.htm HTML page that loads the review and review item detail pages in frames.
reviewdetail.htm Data access page for viewing or editing review details.
reviewdetail_files\filelist.xml File list for reviewdetail.htm.
ReviewItem.htm Data access page for viewing or editing review item details.
reviewitems.htm Data access page for viewing or editing all review item details for a particular review.
reviewitems_files\filelist.xml File list for reviewitems.htm.
ReviewItem_files\filelist.xml File list for reviewitem.htm.
subcategories.htm Data access page that makes customization of subcategories possible.
subcategories_files\filelist.xml File list for subcategories.htm.
themes Folder containing theme style-sheet and image files.
images Folder containing miscellaneous image files.