Scripting the Experience with Microsoft Office Developer
Andrew Clinick
Microsoft Corporation
March 13, 2001
Download Marchmod.exe.
This month marks the release of the next generation of Microsoft® Office, OfficeXP, so I thought I'd take a look at how script is utilized in Office, and in particular Microsoft Office XP Developer (MOD). Office XP provides a number of exciting new features that are easily scriptable, particularly on the server. I'll cover the workflow designer for Microsoft SQL Server™ and the development environment that comes with Microsoft Office XP Developer.
What Is Microsoft Office XP Developer?
Many of you might not be aware of what Microsoft Office XP Developer (MOD) actually is, so here's a brief explanation from the MOD documentation:
"MOD is designed for professional developers building and deploying applications and business solutions targeting Microsoft Office. It provides a complete set of developer tools, documentation, and sample code for quickly creating custom applications with Office."
Here's my translation of what that means: MOD provides a set of tools that make it really simple to customize Office applications and, perhaps more importantly, build applications based on Office and other components. In particular, the use of the Visual Studio® development environment with all its great development features and great integration with Microsoft Exchange Server and SQL Server make it a great addition to your scripting toolkit.
Scripting a Workflow
Much has been made about workflow and how it was (or still is) going to revolutionize how we develop applications. I remember having to give a presentation about "how workflow was going to influence application development" during my interview loop to join Microsoft seven years ago this week. I concluded back then that pretty soon workflow would become an integral part of how people program, and that workflow applications would become less important as a result. It appears that I was a little over optimistic about the uptake of workflow, but luckily they gave me the job anyway!
Today there are still definite workflow applications and it's not that simple to build workflow capabilities into your applications, but all this is about to change with the release of MOD since it ships a workflow designer and runtime for you to use in your applications based on Microsoft Exchange or SQL Server.
The obvious question is which one to choose, Exchange or SQL? I think that depends on your application, how data centric it is, and what type of data you're storing. If you're writing a document management system, then Exchange would be a good fit since it has a rich data store that can store all sorts of content from documents to spreadsheets. If your application is more of a traditional database application, then SQL, with all its querying and relation characteristics, makes a better bet. The key here is that the MOD developers wanted to provide a set of tools that met your requirements rather than having to impose a particular back-end data store on your application.
Adding Workflow to a Company
Let's say your company has been growing fast and as a result, managing internal processes has become somewhat of a pain. In particular, the ability to manage expenses has been very difficult. To address this issue, your developers might develop a simple SQL Server database that allows employees to enter expense reports through a Web page. Eventually you'll be able to do it in an Excel spreadsheet, and a VBA macro in the spreadsheet will submit the report through a Web service, but Rome wasn't built in a day. The database structure is pretty simple, allowing you to have many expense reports per employee, and each expense report has many expense details.
Figure 1. Expense report database structure
The key to getting the expense reporting system to work properly is ensuring that the report goes through the correct approval process. Luckily, MOD provides a great way to do this with the workflow designer for SQL Server. This is a special project type that allows you to visually build a workflow for a particular table in your SQL database and assign certain states and actions to build a workflow.
For the Fabrikam expense reporting system, I've built a workflow based on the expenses table. (Fabrikam is our fictional company; no association with any real company, organization, product, domain name, e-mail address, logo, person, places, or events is intended or should be inferred—my lawyer makes me say that...) When an expense report is created its state is set to unapproved, at which point the user can freely edit the report and change details. Once the report is finished, it is submitted to the correct approver (in this case the user's manager) and the state is set to submitted. When the report is submitted, the user's manager can approve, ask for more details, or reject the report. Assuming that all is well and the report is approved, then the reports state is set to approved and it can be paid. There's no way a report can be paid without being approved.
Defining the Workflow
To build this workflow using MOD requires that you create a new SQL Server workflow project. A workflow project is associated with a particular database on a designated SQL Server. Associating the database with a particular database adds a number of new tables and stored procedures that are used to implement the workflow tracking system. The project is now created, ready for workflows to be defined. The SQL workflow system works around being able to define a workflow for a particular table. In the example here, the workflow is for the expenses table. To create a workflow, all you need to do is select the add new workflow option on the context menu of the workflow folder in the workflow project, setting the table that the workflow is to be for, and drawing out the workflow.
.gif)
Figure 2. Workflow for the expenses table (Click thumbnail for lager image.)
Here is the graphical representation of the fabrikam.com expense system:
.gif)
Figure 3. Graphical representation of the fabrikam.com expense system (Click thumbnail for lager image.)
The graphical representation does more than just draw the workflow, it actually sets up the rules required to run the workflow, and so once the diagram is saved your database will have a rudimentary workflow in place. The workflow is actually implemented by a number of stored procedures that are added to the database when the workflow project is created. If the modStateID is changed from 1 (Unapproved) to 4 (Paid) in any application, SQL Server will return an error message informing the user that this change breaks the workflow rules and the transaction will be rolled back. For example, when updating the expenses table directly from Microsoft Access, the following dialog box shows the error.
Figure 4. Example of a SQL Server error message generated when a change breaks the workflow rules
Scripting the Workflow
Now that a simple workflow has been established, all that remains is to extend the workflow to meet the requirements of the fabrikam.com users. Luckily, the SQL Server workflow system allows you to run VBScript throughout the workflow. To make it simpler to script a workflow, the service that runs the workflow fires a number of events though the lifetime of the workflow. For example, an OnCreate event is fired when a record in the workflow table is created, and each state in the workflow has a number of events fired when the state is entered, changed, and left. To illustrate how you might use script to customize a workflow, I've added some simple script behind the enter events on the submitted, approved, rejected, paid, and overdue. The Expenses table has fields to store the date when the expense report progresses through the workflow. To update these fields, the system uses some script behind the OnEnter event of each state in the workflow.
The MOD IDE provides a full-fledged, script-editing environment that can be accessed by double clicking on any element on the workflow design surface. When you double click on a state, the editor automatically selects the relevant state object in the script editor and you choose which event you want to script. The first state that needs to update the database, Created, needs to change the dateCreated field value to the date and time that the state was entered. The workflow runtime provides an object model that makes accessing the current record and its fields very simple by providing a single row ADO recordset containing the values of the current record. The script in the OnEnter event takes advantage of this object and sets the value of dateSubmitted to now, and then calls the UpdateBatch method, which commits the change to the database.
Sub NewReport_OnCreate
' Put in the Created Date Session.Item.Fields("DateCreated").value = cdate(now)
Session.Item.UpdateBatch
End Sub
The fabrikam.com expense reporting system also allows for a timeout on a report so if a report that has been submitted isn't approved or rejected within 14 days, the report state will change to overdue. This is achieved by using the expiration transition type and is triggered by a custom action being run by the SQL Server Agent service that runs every night at midnight. When a reports state is set to be changed to overdue, an event stateOverdue_OnEnterValidate is raised, so this is the obvious place to put some script to check if more than 14 days have passed since the report was submitted. If so, the workflow will send some mail to the user telling them to hassle their manager about the report. The script doesn't send e-mail to the manager because if they've ignored the report for 14 days it's unlikely they will look at a nagging e-mail.
Sub NewReport_OnCreate
' Put in the Created Date
Session.Item.Fields("DateCreated").value = cdate(now)
Session.Item.UpdateBatch
End Sub
And
Function stateOverdue_OnEnterValidate
' Send an email to the user
' reminding them that this report has been around
' for more than 14 days
dim userEmail
dim trackingEmail
dim strMessage
dim strSubject
trackingEmail = "tracking@fabrikam.com"
strMessage = "Your manager is slacking and has not approved your
expense report number " & session.Item("ExpenseID")
strMessage = strMessage + "Give him/her a call and sort them out"
strSubject = "Expense report " & session.Item("ExpenseID") & "overdue"
' Check to see if now - the datesubmitted is greater than 14 days
' If it is send the mail
If now - session.item("DateSubmitted") >= 14 Then
stateOverdue_OnEnterValidate = true
' Work out the users email address
userEmail = mid(session.user,InStrRev(session.User,"\")+1) & "fabrikam.com"
' Send the mail
modSendMail(userEmail,trackingEmail,strSubject,strMessage)
End If
End Function
A Great Development Environment for Script
So far I've dealt with the solutions that you can build with MOD and there are some exciting applications that are easy to build, but what about the environment that you write the scripts in? MOD ships an IDE that is based on the same technology that is currently being built for Visual Studio .NET so there are considerable improvements in the HTML and script editing support. I think the HTML editing package gets the award for most improved product this time round. The most impressive feature that I use is the ability to specify a level of HTML support and the editor adjusts what elements are valid and what objects can be used from script. You can pick HTML 3.2 and only get the relevant elements for HTML 3.2, and so on. This saves so much time you wonder how you ever worked without it. Another feature that falls into the "how did I ever cope" bucket is the addition of IntelliSense® to the editor. This feature provides you with all the hints and drop downs that you're familiar with in script authoring, but for the HTML elements. I won't miss having to remember exactly what the syntax is for iframes or style sheets. Since the HTML editing component is shared, you also get the new HTML authoring features in the Microsoft Script Editor that ships with every version of Office XP. It's cunningly hidden under the tools/macros menu, but provides the same HTML and script authoring experience as MOD. However, it's only for single HTML pages, whereas the MOD IDE allows you to open groups of pages in a solution.
Summary
MOD provides a great way for you to capitalize on your script skills when building applications using SQL Server and Exchange, as well as Office development as a whole. I haven't had time to cover all the new features, but I recommend that you take a look at MOD if you're doing any development either in Office or as general Script development. The SQL workflow engine makes it worthwhile in itself. You can get more information about MOD at Office XP for Developers.