Share via

Walkthrough: Developing a Workflow Application Using Workflow Designer for SQL Server

  • Article

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.

In this walkthrough, you will be guided through the process of creating a workflow application using the Workflow Designer for SQL Server, which is included in Microsoft® Office XP Developer. The easiest way to create a powerful application is by taking advantage of the database interface elements in Microsoft® Access, so the steps in this example include creating an Access data project, building and relating the necessary tables, producing the workflow objects, and building the user interface.

Introduction

An Access data project makes it possible for you to use familiar Access tools to build Microsoft® SQL Server™ databases. When the Workflow Services for SQL Server components are installed, a data project can be enhanced with workflow processes that create and enforce business rules.

A user interface made up of data access pages can be used to create Web views of your database in minutes. The Office Developer Workflow Toolbar control can be added to the page to provide access to the workflow steps.

In this walkthrough, you create a basic application using Access and the Workflow Designer. This application will include a relational database called ProjectTracker, business rules, permissions, and a user interface comprised of data access pages.

Setup Requirements

For this walkthrough, you must have a server and a development/client computer. If you are a system administrator on the server, you are not required to install the Workflow Services components on the server. With system administrator permissions, you can set the workflow application User Directory synchronization options using your development computer.

To develop applications, you must be a member of the Windows group modAppOwners. This group is created during the installation of the Workflow Services. You must be administrator on your development computer, and you minimally must be a server administrator on your local copy of SQL Server or Microsoft® SQL Server™ 2000 Desktop Engine.

For this walkthrough, you must create two additional Microsoft® Windows® user accounts with SQL Server logins. Two users, user1 and user2, can be individuals in your organization who already have Windows accounts and SQL Server logins, or you can use test accounts which your system administrator creates for you.

Building a Workflow Application

There are two ways to build a workflow application — by adding workflow to an existing SQL Server database or by using a database template as a boilerplate for a new application. In this walkthrough, you will add workflow to a database. When you have created your database, you can turn it into an application using the Workflow Designer.

This walkthrough takes you through the following general steps that outline the basic procedure for creating a workflow application:

  1. Create a SQL Server database, and open it in Office XP Developer.
  2. Create a table hierarchy consisting of main, detail, and lookup tables. The table hierarchy makes it possible for you to indicate the tables involved in a workflow process and displays a visual representation of your table relationships.
  3. Create a basic workflow process diagram.
  4. Test your workflow.
  5. Enhance the workflow process by adding additional states and transitions from the Workflow toolbox to the diagram.
  6. Add database users, and create roles. Create role permissions, and assign users to roles.
  7. Define workflow permissions, and add script to provide added functionality to workflow events.
  8. Create a Web-based user interface in Microsoft® Access using data access pages. Use the Office Developer Workflow Toolbar control to add workflow functionality to the pages.
  9. Create a template for your application.

After following these steps, your final application includes the following objects:

  • A Web site
  • An Access data project based on a SQL Server database
  • The workflow objects, including a table hierarchy and one or more workflow processes
  • Data access pages, designed in Access and stored in the Web site

Step 1: Create a SQL Server Database

Creating the SQL Server Database Using Access

When you create an Access data project (*.adp), the Access Database window includes objects, such as views, database diagrams, and stored procedures, that you do not see when you create an .mdb. These objects are native to SQL Server.

To create a SQL Server database using Access

  1. Start Microsoft Access.

  2. On the New File list, click Project (New Data).

  3. Give your project a name, ProjectTracker, and click Create.

    **Note   **You can save this project in your personal folder or any alternate location. The location is not critical.

Logging into SQL Server

The first step to creating a SQL Server database using Access is to connect to an existing SQL Server and provide a name for the new SQL Server database. To do this you must be a member of the modAppOwners Windows Group on your server. This group has a SQL Server login with database creation privileges.

To log in to SQL Server

  1. Open your new Access project (ProjectTracker.adp). A SQL Server wizard opens to help you connect to SQL Server and specify a name for the SQL Server database.

  2. Specify the name of the SQL Server you want to use, or select a server from the list.

  3. Select Use Trusted Connection to use Windows authentication.

  4. Provide a database name of ProjectTrackerSQL.

  5. Click Next, and click Finish to complete the wizard with the defaults.

    **Note   **A SQL Server progress meter message is displayed. This process might take a few minutes.

When processing is completed, the Access database window is active. Now, you can design your SQL Server schema using Access.

Creating SQL Server Database Tables

In this walkthrough, you are going to create three tables:

  • tblProjects
  • tblProjectTasks
  • tblProjectTaskAssignments

To create the tblProjects table

  1. In the Access Database window, select Tables from the Objects list. Double-click Create table in Design view.
  2. Create the first column ProjectID as an int datatype (the length defaults to 4). Clear the Allow Nulls column, and set the Identity to Yes. Click the Primary Key button to make this column a primary key.
  3. Create a second column ProjectName as a datatype of nvarchar, with length of 50.
  4. Create a third column ProjectDescription as datatype of nvarchar, with length of 75.
  5. Create a fourth column ProjectBeginDate as a datatype datetime, with length of 8.
  6. Create a fifth column ProjectEndDate as a datatype datetime, with length of 8.
  7. Create a sixth column Completed as a datatype bit, with length of 1, and set a default value of (0), which is False.
  8. Create a seventh column ModifiedBy as a datatype nvarchar, with length of 65.
  9. Click Save, and name this table tblProjects.
  10. Close the table designer.

To create the tblProjectTasks table

  1. In the Access Database window, select Tables from the Objects list. Double-click Create table in Design view.

  2. Create the first column ProjectTaskID as an int datatype (the length defaults to 4). Clear the Allow Nulls column, and set the Identity to Yes. Click the Primary Key button to make this column a primary key.

  3. Create a second column ProjectID as an int datatype, with length of 4.

  4. Create a third column TaskName as datatype of nvarchar, with length of 50.

  5. Create a fourth column TaskDescription as datatype of nvarchar, with length of 75.

  6. Create a fifth column TaskStartDate as a datatype datetime, with length of 8.

    Note   If you want to set a default for the current date, use the following syntax:

    (convert(varchar,getdate(),1))

    .

  7. Create a sixth column TaskEndDate as a datatype datetime, with length of 8.

  8. Click Save, and name this table tblProjectTasks.

  9. Close the table designer.

To create the tblProjectTaskAssignments table

  1. In the Access Database window, select Tables from the Objects list. Double-click Create table in Design view.

  2. Create the first column ProjectTaskAssignmentID as an int datatype (the length defaults to 4). Clear the Allow Nulls column, and set the Identity to Yes. Click the Primary Key button to make this column a primary key.

  3. Create a second column ProjectTaskID as an int datatype, with length of 4.

  4. Create a third column EmployeeID as datatype of nvarchar, with length of 65. You use this column to link to the application user directory.

  5. Create a fourth column DateAssigned as a datatype datetime, with length of 8.

    Note   If you want to set a default for the current date, use the following syntax:

    (convert(varchar,getdate(),1))

    .

  6. Create a fifth column DateCompleted as a datatype datetime, with length of 8.

  7. Click Save, and name this table tblProjectTaskAssignments.

  8. Close the table designer.

Creating Relationships in the Access Database Diagram Designer

When you create a table hierarchy in the Workflow Designer, it examines the table relationships to determine how to create the hierarchy. Therefore, it is important to create your table relationships before registering your application or attempting to set up a table hierarchy. To create relationships in the Access data project, you use a database diagram.

To create table relationships

  1. In the Access Database window, select Database Diagrams in the Objects list.

  2. Double-click the Create database diagram in designer option.

  3. Add all three tables from the Add Tables list to the diagram window, and click Close.

  4. To create a relationship between tblProjects and tblProjectTasks, drag from the primary key, ProjectID, in tblProjects to the foreign key, ProjectID, in tblProjectTasks.

  5. When you release the mouse button, the Create Relationship dialog box appears. Verify that it lists the correct fields, and click OK.

    **Note   **Unlike the Relationships feature in an *.mdb, the relationship lines do not necessarily line up primary key to foreign key.

  6. To create a relationship between tblProjectTasks and tblProjectTaskAssignments, drag from the primary key, ProjectTaskID, in tblProjectTasks to the foreign key, ProjectTaskID, in tblProjectTaskAssignments.

  7. When you release the mouse button, the Create Relationship dialog box appears. Verify that it lists the correct fields, and click OK.

  8. Save the diagram, and name the diagram ProjectTracker. Click OK.

  9. Click Yes when prompted to save all tables.

  10. Close the Database Diagram.

Registering Your Database as an Application

After you have created your SQL Server database, you can begin to add workflow using the Workflow Designer.

To enable workflow on a SQL Server database

  1. From the Start menu, point to Programs, then Microsoft Office XP Developer, and then click Microsoft Development Environment.
  2. On the File menu, point to New, and click Project.
  3. The New Project dialog box is displayed. Under Project Types, select the Office Developer Projects folder.
  4. Under Templates, several icons are displayed.
  5. Give your project the name ProjectTracker, and double-click the SQL Server Workflow Project icon.
  6. Select your server and the database ProjectTrackerSQL from the lists. The default Web site URL is displayed: http://<Server Name>/ProjectTrackerSQL.
  7. Click OK, and then click OK on the message that informs you that tables will be added to your database.
  8. Type in the file name TrackingTools, and click Save. The database is registered in the Workflow Designer and is ready for you to add a workflow process.

Step 2: Create a Table Hierarchy

Typically, your database includes a combination of main tables, detail tables, and lookup tables. The advantage of this hierarchy is that detail and lookup tables inherit permissions and properties from the parent main table.

To set up your table hierarchy

  1. Select the Project node (ProjectTracker) in the Solution Explorer.
  2. Open the Project menu, and click Add New Item. The Add New Item dialog box is displayed.
  3. Double-click the Add a Main Table icon.
  4. Select tblProjects as your main table. Click OK.
  5. To view the table hierarchy, expand the ProjectTracker node in the Solution Explorer, and then expand the Tables folder.

Now, the table hierarchy is created, and you can create a workflow process using the main table, tblProjects.

Step 3: Create a Workflow Process

To design a workflow process for a database, you drag a series of states and transitions from the Workflow toolbox to the workflow design surface. Behind the scenes, the Workflow Designer adds a column to your main table called modStateID. This has a foreign key relationship with a table called <main table>StateLookup that is created during registration. This lookup table contains the ID number that is assigned to each workflow state as it is added to the diagram.

Before starting to work on your workflow process, determine the type of states you might want to set. For instance, in a payroll application, you might want to create submit, review, and approved workflow states. For this Project Tracker application, we are creating six states: Analyze, Design, Test, Release, Reanalyze, and Redesign.

To add workflow to your application

  1. Select the Project node (ProjectTracker) in the Solution Explorer.
  2. Open the Project menu, and click Add New Item. The Add New Item dialog box is displayed.
  3. Double-click the Add a Workflow Process icon.
  4. Select tblProjects from the list, and click OK. Item Created and Item Deleted shapes are added to the design surface.
  5. Drag a state shape from the Workflow toolbox to the design surface near the Item Created shape. Change the caption to Analyze.
  6. Drag another state shape from the Workflow toolbox next to the first state, and change the caption to Design.
  7. Add two more states to the diagram, and name them Test and Release.
  8. Locate the Transition shape in the Workflow toolbox. Press CTRL, and click the Transition shape; this turns on sticky mode, so you do not have to go back to the toolbox after you add each transition.
  9. Click the Item Created shape, and drag to Analyze. Then, click Analyze, and drag to Design. Connect Design to Test, Test to Release, and Release to Item Deleted the same way. Click the Pointer shape in the Workflow toolbox to turn off Transition.
  10. Select the first transition. In the Properties window, change the Caption and the (Name) properties to New.
  11. Change the captions and names of the rest of the transitions, so they are identifiable. You might want to use a format such as OriginState_DestinationState, for example, Analyze_Design.
  12. Change the (Name) property of the states to match their captions, so when you add script to the workflow, the names of the states and transitions will be the same in the code as they are in the diagram.

You now have a basic functioning workflow process. When you save the project, all manipulations of tblProjects will be regulated by this workflow process.

Note   In the table hierarchy, if you expand the tblProjectTasks, you see an additional table has been added — tblProjectsStateLookup. In fact, if you switch back to Access and press F5 to refresh the database window, you see many objects have been added to your Access project. (In addition, you can view these objects in the SQL Server Enterprise Manager if you have it installed.)

You could finish the application now by skipping to Step 8 and creating the user interface, because once you have a functioning workflow process you only need to enter a record into the database for the workflow to take effect. However, to learn more about how the workflow process functions and to enhance your workflow, continue on and test what you have so far.

Step 4: Test Your Workflow

After adding a workflow process to a table, you can test the workflow directly in Access or the SQL Server Enterprise Manager. Even without a user interface or any script, the workflow states, transitions, and events are enforced when you enter data into your table.

To test a workflow process

  1. Find the modStateID for each state by opening the <*tablename>*StateLookup table in Access and noting the ID number beside each state name. You must know the modStateID to be able to move to a particular state in the workflow process when you are working directly in the tables.

  2. Open the workflow-enabled table in Access, and enter a new record.

    Change from one state to another by changing the ModStateID value. Try to change the status of your record from the first state to the last state. Since you have not added a transition event that makes this transition possible, you are notified that this is not a valid event. In addition, you are not able to delete the record while it is in a state that has no transition to the Item Deleted shape.

  3. If workflow is not performing as expected, return to your workflow diagram and make sure you have saved your workflow and refreshed the database. In addition, make sure you have added the required transitions to your diagram.

Step 5: Enhance the Workflow Process

When you add a transition, you create a permissible change from one state to another. In the current workflow design, for example, a record cannot go from Analyze to Closed; that transition would be rejected. In addition, when a record is open, it cannot be saved as Analyze again, because there is no Transition Within on the Analyze state; so the record must be saved as Design or closed without saving.

There are script blocks that are called when an item moves from one state to the next: OnExit_previous state, OnChange, and OnEnter_next state. Each script block has two script procedures — a validation script procedure and an event script procedure. The validation procedure must return True for the event script procedure to be triggered. By default, each validation procedure does return True.

In addition, other events can be scripted, such as OnCreate and OnDelete, that are added to a transition from the Item Created shape and to a transition to the Item Deleted shape respectively.

**Note   **You can modify the validation function to interact with your application. For example, perhaps this function only returns a True if a certain user is entering data or if it is a certain day. However, keep in mind that if this function does not return a True, the event script procedure will not fire, and the state transition will not be permitted.

Modifying a Workflow Process

If it turns out that the business process requires more flexibility, you can enhance your workflow process by adding more states and transitions. For this walkthrough, you are adding two states: Reanalyze and Redesign.

To modify a workflow process

  1. If required, open the ProjectTracker application in the Workflow Designer. Open the Workflow Processes folder, and double-click tblProjectsWorkflow. The tblProjects workflow diagram is displayed on the workflow design surface.
  2. Add a new state, and change the caption to Reanalyze. Put the new state beside the state called Design
  3. Repeat those steps, and add Redesign beside Test.
  4. Add a transition from Design to Reanalyze, and then, because transitions only permit movement in one direction, add another from Reanalyze back to Design.
  5. Add a transition from Test to Redesign, and another from Redesign back to Test.

Your workflow should look similar to the following illustration:

TblProjects Workflow Diagram

Aa166227.wfdiagram(en-us,office.10).gif

Next, you must set up user information, so you have an accessible list of database users for an employee lookup.

Step 6: Add Database Users and Create Roles

To assign users and groups to database roles, the users and groups must have valid Windows domain accounts and SQL Server logins. This walkthrough is simplified by having two users, user1 and user2, who have the required access. They can be test accounts that your system administrator creates for this walkthrough or any individuals with domain accounts.

Note   Generally, you have Windows group accounts for various categories of users, and you assign these groups to a SQL Server login. Then, this group is added as a database user and assigned to one or more roles. For example, to grant everyone access, you can use the Windows group account Domain Users.

Creating Database Roles

To manage the permissions in your workflow-enabled database, it is recommended you define a set of roles based on job functions and assign each role the permissions that apply to that job.

SQL Server roles exist within a database and cannot span more than one database. Because roles are unique to each database, you can reuse a role name, such as Reviewer, in each database you create. Use the SQL Server Enterprise Manager to create roles and add users to the roles.

To add database users using the Enterprise Manager

  1. From the Start menu, point to Programs, point to Microsoft SQL Server, and click Enterprise Manager.
  2. Expand a server group, and then expand a server.
  3. Expand the databases node, and then expand the ProjectTrackerSQL database.
  4. Right-click Users, and then click New Database User.
  5. Select Login Name user1 from the list. By default, the login is added to the Public database role.
  6. Click OK. User1 is listed under database users. Repeat the steps to add user2.

For this walkthrough, there are three database users — dbo, user1, and user2. Now, create two roles and assign these users to specific roles in this database. One is a Developer role, for individuals who review the information but cannot do certain workflow events or make certain transitions. The other is a Manager role, for individuals who create and delete projects and make certain state changes, such as setting a project to the Reanalyze or Redesign state.

To create SQL Server roles using the Enterprise Manager

  1. In the ProjectTrackerSQL database, right-click Roles, and click New Database Role.
  2. In the Name box, type Manager.
  3. Click the Add button to see a list of database users.
  4. Select user1, and click OK to assign it to the Manager role.
  5. Click OK.
  6. Click New Database Role again, and create a Developer role.
  7. Assign user2 to the Developer role.

Now, your application has three users — dbo, user1, and user2 — and two new roles that you defined, in addition to the default roles created automatically. You can grant and revoke workflow permissions to these roles.

To assign permissions to roles

Now, the roles are created, and you must specify the permissions for these roles. Role permissions are separate from the permissions that you designate for the workflow. Role permissions specify the privileges that members of these roles have on the server. For example, if a role does not have Select permissions, members of the role are not able to see any data, and if the role does not have Insert permissions, members cannot add records.

  1. With Enterprise Manager open, expand your server and database.
  2. Click Roles.
  3. In the Details pane, right-click the Public role, and click Properties.
  4. Click Permissions. If required, select List all objects. Objects are listed in rows, and permissions are listed in columns.
  5. You must grant Select permissions on four objects, tblProjects, tblProjectsView, tblProjectTaskAssignmentsView, and tblProjectTasks.
  6. Click OK.
  7. Edit Public permissions again, and add Insert, Update, and Delete permissions to three tables: tblProjects, tblProjectTasks, and tblProjectTaskAssignments.
  8. Click OK.

Notice that by granting the required permissions to the Public role, all database users inherit them. In the next step, you modify workflow permissions, so only certain roles can perform certain events. This is one way to manage permissions — being lenient on the role permissions and then tightening security using the workflow permissions.

Synchronizing the User Directory with Your Exchange Server

The workflow application User Directory lists all users of applications on a particular server. This directory information is displayed in a view (called modUserList) in each workflow-enabled database, which limits the list to role members for that particular application.

Before you are able to synchronize with Microsoft® Exchange 2000, an individual with administrative privileges on the server must setup the synchronization options. If you have Windows administrative privileges, then you can do this from your development machine. If you are not a Windows administrator, then the Windows administrator must install the Workflow Designer on the server and launch the Workflow Manager for SQL Server.

To set up workflow application User Directory synchronization

  1. From the Start button, point to Programs, point to Microsoft Office XP Developer, and click Workflow Manager for SQL Server.
  2. Select your SQL Server from the Server list, and click Refresh.
  3. On the User Information tab, click Synchronization.
  4. Select the Synchronize with Microsoft Exchange Server option.
  5. In the Exchange Server field, enter your Exchange Server name.
  6. Click OK.
  7. If a message appears informing you that data could be overwritten, click Yes.
  8. Click SynchronizeNow. Your user directory is populated based on information from Exchange. Minimally, you should see three users — you, user1, and user2.

If you want to use the user directory as a source for user information in your application, you can use the modUserList view in your application as a lookup.

Now there is user information available for the application. The next step is to assign permissions to the application roles and to enhance workflow functionality using script.

Step 7: Define Workflow Permissions and Add Workflow Script

Permissions added to workflow transitions make sure only members of designated roles can perform certain events. By default, when a new event is added, all users are permitted to perform the event. In the ProjectTracker, only the Manager role members should be permitted to create and delete projects and to set the states to Reanalyze and Redesign. Therefore, permissions must be revoked for the Public role (because all database users inherit Public permissions), and for the Developer role for these events.

To restrict user permissions for specific workflow events

  1. Open the ProjectTracker application in the Workflow Designer, and expand the Workflow Processes folder.
  2. Double-click tblProjectsWorkflow to open the workflow diagram.
  3. Select the first transition, named New, in the workflow diagram.
  4. Open the View menu, and click Property Pages.
  5. On the Permissions tab, clear the check boxes next to Developer and Public, and click OK. Now, only the Manager role will be able to add new records to the database.
  6. Repeat these steps for the transitions from Design to Reanalyze, from Test to Redesign, and from Release to Item Deleted.
  7. Select Save to update your application.

Adding Script to Workflow Transitions

In this walkthrough, you add a sub procedure called SetUser to the Code Editor. When called by an event, this procedure sets the ModifiedBy field in tblProjects to the current user.

To add script to workflow transitions

  1. In the Solution Explorer of the Workflow Designer, open the Workflow Processes folder, and double-click tblProjectsWorkflow, so the diagram appears on the design surface.

  2. Double-click the workflow diagram to open the Code Editor.

  3. The following procedure enters the current user's SAMAccountName in the ModifiedBy field. You can use this syntax when you want to insert the current user into a field, and you can change the target field by changing the Session.item(<Field Name>) to the name of the field you want to update with the current user's name. Type the following code into the Code Editor:

    Sub SetUser()
   'Session object represents the current recordset
   'this snippet sets the ModifiedBy field equal to the current user
   Session.item("ModifiedBy") = Session.User
   Session.item.updateBatch(3)
End Sub

  4. To set this up so the ModifiedBy field is updated when a new record is created, this procedure must be called from OnCreate for the transition called New, which goes from the Item Created shape to Analyze. Double-click the New transition to add the event script procedure, which looks like this:

    Sub New_OnCreate()
End Sub

    Between the Sub New_OnCreate() and the End Sub, type Call SetUser(). It looks like this:

    Sub New_OnCreate()
   Call SetUser()
End Sub

  5. Open the Tools menu, and click Verify Script.

  6. Click Save.

This completes the workflow for the ProjectTracker.

Step 8: Create a Web-Based User Interface Using Data Access Pages

You can use any form of user interface that has read/write capabilities on a SQL Server database. However, only data access pages support the full set of Workflow Designer features.

A data access page is a special type of Web page designed for viewing and working with data from the Internet — data that is stored in an Access database or SQL Server database.

Creating Application Data Access Pages

When you create data access pages for an application, you base the page on a view rather than a table, because row-level permissions are enforced through the views. In the following examples, you create pages using the tblProjectsView, the tblProjectTasksView, and the tblProjectTaskAssignmentsView.

To create a new data access page using a wizard in Access

  1. Open your data project in Access.

  2. In the Objects list, click Pages, and double-click the Create data access page by using wizard option.

  3. From the Tables/Queries list, select tblProjectsView.

  4. From the Available Fields list, add all the columns to the Selected Fields list, and click Next.

  5. Accept the default grouping levels by clicking Next.

  6. Accept the default sort order by clicking Next.

  7. Give your Web page the title Project Information. Leave the option selected to Modify the Page's Design. Click Finish.

  8. Right-click the design surface of the open page, and click Section Properties.

  9. Select the Data tab.

  10. In the UniqueTable property, select tblProjects. Close the property box.

    Note   This step makes sure that the base table can be updated through the data access pages.

To customize your page

  1. Click the data access page where you would like to insert the Microsoft Office Developer Workflow Toolbar control. The top portion of the page below the title often works well.

  2. From the Insert menu, select ActiveX Control, and double-click Microsoft Office Developer Workflow Toolbar.

  3. The toolbar is added to the page. This automatically binds it to the workflow rules by inspecting the UniqueTable property on the data source control. Stretch it out horizontally to display all the states and transitions.

  4. Click Save, and enter the full path to the Web site URL you created when you registered the application: http://<Server Name>/ProjectTrackerSQL.

    **Tip   **If you accidentally save a data access page to a site other than your Web site, you will not get the option to Save As and change the location. However, you can select the page in the database window and cut it. Then, paste it back into the same database window, and you will get the option to specify the save location.

  5. From the File menu, select Web Page Preview to see the toolbar.

Notice that transitions and events that are not permitted by the workflow process for the current record are not available. When you are in a blank record, the only permissible event is New. Furthermore, New is available only if you are a member of the Manager role.

To enter information in your data page

  1. On the toolbar, select New to start entering a new project. Notice the value of modStateID is set to that of the first state.
  2. Enter a ProjectName of Project 1, a ProjectDescription of First Project, and a beginning and ending date. Set Completed to False.
  3. Click Save Record.

When the record is saved, the ModifiedBy field is updated to the current user (courtesy of the SetUser() procedure placed in the workflow script) — in this case, your SAMAccountName.

Now, you can adjust the design of the data page. The design controls are very similar to those in the Access form and report design environments. However, the properties for these pages are quite different. In the Access Help, use the Answer Wizard, and search for "data access page design." You get many helpful topics about designing, creating, and using data access pages.

To create the Project Task Information data access pages

  1. In the Objects list, click Pages, and double-click the Create data access page by using wizard option.
  2. From the Tables/Queries list, select tblProjectTasksView.
  3. From the Available Fields list, add all the columns to the Selected Fields list, and click Next.
  4. Accept the default grouping levels by clicking Next.
  5. Accept the default sort order by clicking Next.
  6. Give your Web page the title Project Tasks. Leave the option selected to Modify the Page's Design. Click Finish.
  7. Right-click the design surface of the open page, and click Section Properties.
  8. Select the Data tab.
  9. In the UniqueTable property, select tblProjectTasks.

To customize your page

  1. Replace the ProjectID text box with a lookup by deleting the ProjectID field. In the toolbox, click the DropdownList control, and add it to the form.
  2. When the wizard opens, select the I want the combo box to look up the values in a table or query option, and click Next.
  3. Select the tblProjects table, and click Next.
  4. Add ProjectID and ProjectName to the Selected Fields list. Click Next.
  5. Make sure the Hide key column (recommended) box is selected, so the ProjectID column is hidden. Adjust the ProjectName column as required to display the expected values, and click Next.
  6. Click Finish.
  7. Right-click the DropdownList control, and click Element Properties.
  8. Click the Data tab, and set the ControlSource to ProjectID.
  9. Make sure ListBoundField is set to ProjectID.
  10. Make sure ListDisplayField is set to ProjectName.
  11. Close the Properties window, and click Save. Enter the full path to the Web site URL you created when you registered the application: http://<Server Name>/ProjectTrackerSQL.

To create the Project Task Assignments data access pages

  1. In the Objects list, click Pages, and double-click the Create data access page by using wizard option.
  2. From the Tables/Queries list, select tblProjectTaskAssignmentsView.
  3. From the Available Fields list, add all the columns to the Selected Fields list, and click Next.
  4. Accept the default grouping levels by clicking Next.
  5. Accept the default sort order by clicking Next.
  6. Give your Web page the title Project Task Assignments. Leave the option selected to Modify the Page's Design. Click Finish.
  7. Right-click the design surface of the open page, and click Section Properties.
  8. Select the Data tab.
  9. In the UniqueTable property, select tblProjectTaskAssignments

To customize your page

  1. Delete the ProjectTaskID text box, so it can be replaced with a lookup. From the toolbox, add the DropdownList control to the form.
  2. When the wizard opens, select the I want the combo box to look up the values in a table or query option. Click Next.
  3. Select tblProjectTasks table, and click Next.
  4. Add ProjectTaskID and TaskName to the Selected Fields list, and click Next.
  5. Make sure the Hide key column (recommended) box is selected, so the ProjectTaskID column is hidden. Adjust the TaskName column as required to display the estimated values. Click Next.
  6. Change the label to Task, and click Finish.
  7. Right-click the DropdownList control, and select Element Properties.
  8. Click the Data tab, and set the ControlSource to ProjectTaskID.
  9. Make sure ListBoundField is set to ProjectTaskID.
  10. Make sure ListDisplayField is set to TaskName.
  11. Close the Properties window, and click Save. Enter the full path to the Web site URL you created when you registered the application: http://<Server Name>/ProjectTrackerSQL.

The final step is to create a lookup to the Workflow Application User Directory information you synchronized earlier. To do this, you must create a drop-down list control that displays information from the modUserList view.

To create a lookup to the User Directory

  1. Delete the EmployeeID field from the ProjectTaskAssignments data page.
  2. From the toolbox, click the DropdownList control, and add it to the form.
  3. When the wizard opens, select the I want the combo box to look up the values in a table or query option, and click Next.
  4. Select the Queries option, and select modUserList view, and click Next.
  5. Add SAMAccountName and CN (full name) to the Selected Fields list. Click Next.
  6. Adjust the CN column as required to display the values, and click Next.
  7. Change the label to Employee, and click Finish.
  8. Right-click the DropdownList control, and select Element Properties.
  9. Click the Data tab, and set the ControlSource to EmployeeID.
  10. Make sure ListBoundField is set to SAMAccountName.
  11. Make sure ListDisplayField is set to CN.
  12. Close the Properties window, and click Save. Enter the full path to the Web site URL you created when you registered the application: http://<Server Name>/ProjectTrackerSQL.

Creating Navigation Controls

The final enhancement to the data access page in this walkthrough is the addition of navigation links on the ProjectsInformation page.

These will be hyperlinks added using the Script Editor.

To add navigational links the ProjectInformation page

  1. Open the ProjectInformation page in Design view.

  2. Right-click the page, and select Microsoft Script Editor.

  3. Scroll through the page until you see the tags </OBJECT></P>.

  4. Add the following HTML code after the </P> tag:

    <p><a href="Project Tasks.htm">Project Tasks</a>&nbsp;&nbsp; <a href="Project Task Assignments.htm">Project
Task Assignments</a></p>

    **Note   **If you paste the text from a Microsoft® Word document, use the Paste as HTML option from the Edit menu in the Script Editor.

    This code creates links to the Project Tasks and Project Task Assignments pages. Notice that, because all the application pages reside in the Web site, the <a href> tag does not include a server and path.

  5. Click Save, and close the script editor.

Step 9: Create a Template

A database template is a complete copy of an application. The template contains all of the information required to create an application, including the database schema, workflow, and data access pages.

Templates provide a quick way to create customized applications based on your original schema. You might want to create a template of the ProjectTracker and distribute it to various branch offices, which can then make any minor (or major) modifications required for each office.

To save an application as a template

  1. From the Start button, point to Programs, point to Microsoft Office XP Developer, and click Workflow Manager for SQL Server.

  2. On the Workflow Applications tab, click Create Template.

  3. Click Next, select Workflow Project, and specify the server that hosts the database for the application you want to include in the template.

  4. Click Connect and select the application you want to save as a template. Click Next.

  5. Enter ProjectTrackingExample as the name and Project Tracking Example Template as the title for the new template. Enter version information and a description if desired. After completing the wizard, you can view this information in the Workflow Manager on the Templates tab. Click Next.

  6. There are no additional files to include, so click Next.

  7. Add all the data to the template by accepting the defaults and clicking Next.

  8. Click Next to accept the default option that excludes current users of the application from the template.

  9. Select Save Template to File to create a .tpl file that can be distributed to other servers that have Workflow Services for SQL Server installed. Click Next.

  10. Click Finish. The wizard backs up the database, truncates all specified tables, removes database users, and stores the backed-up database information in the modTemplates table. It also enumerates all of the files in your Web site and stores them in the table.

    Note   This process might take a few minutes.

When this template is created, you can create new applications based on it quickly and easily. All of the work done in this example is rolled up in the template.

You should now have a good understanding of how to use the Workflow Designer to create a workflow process, how to set permissions on the workflow, how to create a basic user interface, and how to save your application as a template for distribution. You can build on these basic ideas as you create your own applications.

See Also

Walkthrough: Planning a Sales Management Workflow Application for SQL Server | Walkthrough: Creating a Workflow Application for SQL Server with a Visual Basic Interface | Developing Workflow Applications for SQL Server | Security Categories in Workflow Designer for SQL Server | Testing and Debugging in the Workflow Designer for SQL Server