Developing an End-to-End Solution with the 2007 Microsoft Office Release

Overview

Introduction

With previous versions of Microsoft Office, building and configuring end-to-end solutions required many hours of development time and many lines of custom code. The Microsoft FabriKam 3.1 Solutions Learning Platform provides a good illustration of this effort. Released in April 2005, the FabriKam platform offers six solutions and four reusable components utilizing Office 2003 with Microsoft Windows SharePoint Services, Microsoft BizTalk Server 2004, Microsoft SQL Server 2000, and Microsoft Visual Studio .NET 2003. Overall, it took approximately three man-years of development time and more than 20,000 lines of managed code to create the FabriKam platform.

By contrast, end-to-end solutions utilizing the 2007 Microsoft Office release can be created in a significantly reduced amount of time and with much less customization. This article offers an example of how the 2007 release can reduce the effort required to create a workflow solution, and you will see how a sample building permit application process is developed using Microsoft Office InfoPath 2007, Microsoft Office SharePoint Designer 2007, and Microsoft Office SharePoint Server 2007. You will become familiar with some of the design steps for creating the solution in Office InfoPath 2007 and Office SharePoint Designer 2007. In addition, you will learn how to create key performance indicators (KPIs) against data in a site collection. Overall, the most important takeaway from this article is an understanding of how the 2007 release simplifies the development process for these types of solutions.

Technologies Highlighted

Office InfoPath 2007
Office SharePoint Designer 2007
Office SharePoint Server 2007

Audience

IT professionals

Scenario

The city of Anytown, MI, wants to automate the process of applying for a building permit and put it online. This will make it easier for residents and business owners in Anytown to complete the necessary paperwork for renovations and other building projects.

The application process begins when a user accesses a browser-based InfoPath form. This form captures specific data about the building project. Once all data for the building project has been captured, the user submits the data to a SharePoint document library hosted by the city. A workflow solution, designed using Office SharePoint Designer 2007 and associated with that library, is triggered when a new form is added. The workflow evaluates data in the form and sends an e-mail message to an Anytown compliance reviewer, who must determine if the project meets certain requirements, such as fire safety regulations. The compliance reviewer accesses the form data with either a Web browser or the InfoPath client to determine if these requirements have been met and then submits the data back to the same SharePoint document library. If all requirements in the application have been met, an e-mail message is sent to the applicant indicating that the application has been approved and that a specific permit amount is due. If the application is not approved, a different e-mail message is sent to the applicant with a hyperlink to the form. The applicant must then make changes and re-submit the data to start the review process again.

The city also needs the ability to run reports based on submitted applications. For this functionality, they will use Report Center, a new feature in Office SharePoint Server 2007 that includes a number of business intelligence capabilities, including KPIs.

During the exercises you will use Office InfoPath 2007, Office SharePoint Designer 2007, and Office SharePoint Server 2007 to learn how to build an end-to-end solution like Anytown’s building permit application process. The exercises are as follows:

Designing the InfoPath Form
Creating the Workflow Logic
Creating a KPI

Setup Instructions

First, copy the BuildingPermitApplication.zip archive to your root c:\ drive and extract all files. This action will result in the creation of c:\Demo and all necessary files and subfolders. In order to complete the exercises in this article, you should perform the following additional setup steps:

Create a Permits site and set user permissions accordingly:
1. From your SharePoint default home page, select Site Actions | Create Site.
2. Type Permits in the Title text box.
3. In the Web Site Address section, type permits in the URL name text box.
4. Click Create.
5. In the Permits site, select Site Actions | Site Settings.
6. In the Users and Permissions section, click People and groups and add your building permit application solution user accounts to the "Owners" group. Make sure all exercise steps are done using one of the accounts in the “Owners” group.
Add the Anytown database:
1. Start SQL Server 2005 Management Studio and connect to your server.
2. Right-click Databases and select Attach.
3. Click Add, navigate to c:\Demo\database, double-click the file Anytown.mdf, and click OK.
4. In the Anytown database, modify the Property table column values so that they use actual user names and e-mail addresses.
5. Close the SQL Server 2005 Management Studio.
Modify the form template’s data connection file:
1. From Windows Explorer, navigate to c:\Demo\dataconnectionfiles.
2. Open the file Property.udcx in a text editor.
3. Within the udc:ConnectionString element, change the Data Source property value from FABRIKAM01 to the name of your SharePoint server.
4. Within the udc:ConnectionString element, change the Workstation ID property value from FABRIKAM01 to the name of your SQL Server 2005 server name.
5. Change the udc:Name and udc:Description element values accordingly.
6. Save and close the file.
Create a data connection library and upload the data connection file:
1. From the Permits site, select Site Actions | Create.
2. In the Libraries section, click Data Connection Library.
3. Type permitconnections in the Name text box and click Create.
4. In the new data connection library, select Upload | Upload Document.
5. Click Browse, navigate to c:\Demo\dataconnectionfiles, double-click the file Property.udcx, and click OK.
6. Click OK.
Create a KPI list:
1. Navigate to the Reports site.
2. In the left navigation bar, click View All Site Content.
3. Click Create.
4. In the Custom Lists section, click KPI List.
5. Type Permit KPIs in the Name text box and click Create.
Modify the form template locally:
1. From Windows Explorer, navigate to c:\Demo.
2. Right-click BuildingPermitApplication.xsn and click Design.
3. Select Tools | Data Connections.
4. Select Properties, click Remove, and then click Yes to confirm the removal of the Properties connection.
5. Click Add.
6. Click Next (search for connections).
7. Click Manage Sites.
8. Click Add.
9. Type the URL for the Permits site (http://<your server name>/permits) in the URL text box.
10. Type Permits in the Display name text box and click OK.
11. Click Close.
12. Select Permits from the Site list.
13. Expand permitconnections, click Property.udcx, and click Next.
14. Click Next (do not store a copy of the data).
15. Change the name of the connection to Properties and click Finish.
16. Double-click Main submit.
17. In the Document library text box, change fabrikam01 to the name of your SharePoint server and click Next.
18. Click Finish.
19. Click Close.
20. Select Tools | Submit Options.
21. Click Rules.
22. Modify the reviewer rules (for example, SetTracyAsReviewer) so that they use actual user names and e-mail addresses.
23. Click OK until all dialog boxes have been closed.
24. Save and close the form template.

Assumption

The exercises in this article were tested using the Beta 2 build (12.0.4017.1006) of the 2007 Microsoft Office release. There is an assumption that some or all of the exercise steps will not work with a different build.

Purpose

The objective of this article is to learn how a sample end-to-end solution can be built with the 2007 Microsoft Office release without extensive custom code.

Disclaimer

The exercises in this article are an illustration of a sample end-to-end solution, not a production-level system.

Exercises

Exercise 1 – Designing the InfoPath Form

Office SharePoint Server 2007 offers developers the ability to create InfoPath forms that can run as both rich client and browser applications. This capability allows forms to be designed once and then published directly to a SharePoint site, where data can be accessed either from the InfoPath client or browser. In this exercise, you will examine a local copy of the building permit application form template and review the logic that allows it to be used in a workflow setting. Then, you will use the InfoPath Publishing Wizard to set certain promoted fields to be editable and publish the form template to the Permits site.

Discussion Points	Actions taken
The building permit application form template is meant for two types of users: (1) applicants and (2) compliance reviewers. First, the applicants—residents and business owners in Anytown—provide building project information. After the applicant submits this data to a SharePoint document library, a compliance reviewer is automatically assigned to ensure the project meets certain requirements. The assigned reviewer then completes the compliance section in the form, which will determine if the permit is approved or rejected. Within this template, there is a form view for each type of user. The Lot Info (default) view is displayed when the applicant first fills out the form. Using button controls in that view, the applicant can also access the Owner Info and Project Info views to provide building owner and project information, respectively. The compliance reviewer uses the Reviewer view to approve or reject the application based on requirements. In the case of a rejection, this view is also used to offer specific feedback for the applicant. The Initiator Rejected view is displayed for the applicant if any of the compliance requirements are not met, allowing that user to see comments from the reviewer and edit project information as needed.	Log on to your machine as a user that is in the "Owners" group for the Permits site. From Windows Explorer, navigate to c:\Demo. Right-click BuildingPermitApplication.xsn and click Design. In the Design Tasks task pane, click Views to see the different form views, shown in Figure 1. Figure 1. Views task pane In the Views task pane, click each form view.
In order to understand both the form logic and workflow logic, it is helpful to become familiar with the form’s data source. Some of the top-level nodes that are worth noting are RoleID, PermitStatus, and Processing. The InfoPath user role feature is not supported in browser forms, so a RoleID node was added to the data source to identify the role of the current user. The possible values for this node are 1 (applicant, the default value), 2 (compliance reviewer), and 3 (indicating that the process is complete and the form is approved). The RoleID node value is updated by the Office SharePoint Designer 2007 workflow each time the form file is submitted to the document library. The PermitStatus node identifies the status of the form. The possible values for this node are OPEN (the default value), REJECTED, and APPROVED. The PermitStatus node value is updated by rules that exist in the form template. The Processing node is a Boolean that identifies if the form file is ready for workflow processing. A value of true (or Yes) indicates that the corresponding workflow should evaluate data in the form file and perform actions accordingly. A value of false (or No) indicates that the workflow should not evaluate form data. The reason for this Boolean value will become clearer in the next exercise when you examine the workflow logic. For now, just be aware that this node value is set to true by a rule each time a user submits the form file to the document library.	Select View \| Data Source to access the form’s data source, shown in Figure 2. Figure 2. Form data source In the Data Source task pane, hover over some of the top-level nodes to view the corresponding data type and default value. Note: The RoleID default value is 1 and the PermitStatus default value is OPEN.
The RoleID node drives much of the form logic, including which view to display to the user when the form file is opened. Using the Open and Save tab of the Form Options dialog box, rules have been created that switch the view depending on the RoleID value (and perhaps the PermitStatus value). If the RoleID value is 1 and the PermitStatus value is REJECTED, then a rule will fire that switches to the Initiator Rejected view. If the RoleID value is 2, then a rule will fire that switches to the Reviewer view, regardless of the PermitStatus value. If neither condition is met, then the default view (Lot Info) is displayed to the user.	Select Tools \| Form Options. Click the Open and Save category. Click Rules. Double-click the InitiatorRejectedView rule, shown in Figure 3. Click Cancel until all dialog boxes have been closed.
Figure 3. InitiatorRejectedView rule
The form uses data connections to retrieve and submit data. Lot information is retrieved using a database connection that points to the Property table in the Anytown SQL Server 2005 database. This connection, named Properties, uses a data connection file that is stored in a new library type known as a data connection library. Connection files stored in this library can be used across form templates. This capability provides developers the luxury of being able to make modifications to an external data source without having to update and re-deploy those form templates. The form also has an e-mail submit data connection, named Email Submit, which the solution uses when the applicant submits the form file. This connection is set up to send a message to the individual identified as the applicant and references a permit ID in the subject line. When using e-mail connections, you can choose to send form data as an attachment and even attach the form template to ensure that users can open the form. This template is designed so that only the active view of the form is sent to the applicant. Meanwhile, the main submit data connection, named Main submit, submits the form file to the SharePoint document library.	Select Tools \| Data Connections. Note the details for the Properties connection. Double-click the Email Submit connection. Note the form field values used in the To and Subject lines. Click Next. Note the options for sending attachments, as shown in Figure 4. Click Cancel. Click the Main submit connection and note the details. Click Close.
Figure 4. E-mail attachment options
Every time a user submits the form file to the SharePoint document library, rules are executed that modify certain node values and activate the submit data connections described in the previous set of steps. For example, looking at the SetProcessingFlag rule, the Processing node value is always set to true when the form file is submitted. Again, the reason this Boolean node value is set will become clearer in the next exercise. You can also see that the Email Submit data connection is used in the EmailSubmit rule. In this case, the rule executes only when the RoleID value is equal to 1, sending a snapshot of the most recent form view to the applicant.	Select Tools \| Submit Options. Click Rules. Double-click the SetProcessingFlag rule. Click Cancel. Double-click the EmailSubmit rule, shown in Figure 5. Click Cancel until all dialog boxes have been closed.
Figure 5. EmailSubmit rule
Now that you are familiar with the form views, data source, and logic, you are ready to publish the form template to the Permits site. Because there is no managed code in the form, you can use the InfoPath Publishing Wizard to publish the template directly to a document library. Note also that there are a number of fields that have been promoted as columns in SharePoint. Property promotion allows users to review certain values in forms in the SharePoint document library without having to open those forms in InfoPath. It also enables workflow logic to be written against a form’s data source. Moreover, promoted fields can be made editable, which will allow the workflow to modify the RoleID and Processing nodes as part of its actions. Note: It is a good practice to use the InfoPath Design Checker for server forms before using the Publishing Wizard. The Design Checker will identify compatibility issues (for example, dialog box actions within rules will not show in server forms).	In the Data Source task pane, click Design Tasks. In the Design Tasks task pane, click Design Checker. Note: There are no compatibility errors or warnings for the form template. Select File \| Publish. Click Next (to a SharePoint server). In the location text box, change fabrikam01 to the name of your SharePoint server and click Next (http://<your server name>/permits/buildingpermitapplication). Click Next (document library). Click Next (create a new document library). Type buildingpermitapplication in the Name text box and click Next. In the promoted column page, double-click Role ID. Ensure the option to Allow users to edit data in this field by using a datasheet or properties page is checked, as shown in Figure 6. Click OK. In the Microsoft Office InfoPath dialog box, click OK. Double-click Processing and then repeat the last three steps to ensure that field is editable. Click Next. Click Publish. Click Close. Close Office InfoPath 2007.
Figure 6. Editable promoted field

Exercise 2 – Creating the Workflow Logic

Office SharePoint Designer 2007 enables site administrators to write workflow logic in conjunction with any SharePoint list. Within the Workflow Designer, users can specify action sets—which contain conditions and actions—without having to write any custom code. These action sets work with the fields that are promoted as SharePoint columns. In this exercise, you will learn how to write an action set for the Building Permit Application document library.

Discussion Points	Actions taken
The Workflow Designer of Office SharePoint Designer 2007 has a relatively simple UI that allows users to create workflow logic for any SharePoint list. Action sets can be added for a workflow. This is similar to how rules are added in the InfoPath designer. You can have actions performed such as sending an e-mail message or setting a field value. These actions are executed based on the values of certain promoted fields in the form. You can even dynamically add the values of promoted form fields to the bodies of e-mail messages. In the previous exercise, we mentioned how the significance of the Processing node would be clearer once you started designing the workflow. Note that workflow start options can be set on the first page of the Workflow Designer. Because the workflow will be started whenever an item is created or changed, the workflow will start even after a RoleID value is updated as the result of an executed action set. So, in order to prevent the workflow from firing repeatedly after a form file is updated, the Processing node should be set to false (or No) within each action set.	Start Office SharePoint Designer 2007. Select File \| Open Site and open the Permits site (http://<your server name>/permits). Select File \| New \| Page. Click the SharePoint Content tab. Click Workflow and click OK. Select buildingpermitapplication from the list of SharePoint lists. Click Automatically start this workflow when a new item is created. Click Automatically start this workflow whenever an item is changed. The first page of the Workflow Designer should appear as shown in Figure 7. Click Next.
Figure 7. First page of the Workflow Designer
Workflows are composed of steps, which are composed of action sets. Each workflow step is processed in the order listed in the Workflow Steps task pane. Action sets are also processed in the order listed within each step. Processing within a step stops when one of its action sets evaluates to true. For this exercise, you will start by creating a single action set for when the form is approved by the compliance reviewer. Again, it is important to remember that your options are set so that the workflow will start whenever an item is changed. As a result, you will need to include an evaluation of the Processing node in your conditions and then set that node value to false (or No) in the corresponding actions. You can perform a workflow lookup and set the To field in the e-mail message to a property’s value. In this case, the promoted property is Submitter Email. You can use promoted property values in the CC, Subject, and message body fields as well.	Click the Conditions list and select Custom Condition (buildingpermitapplication). Click field and select Processing. Click value and select Yes. Click the Conditions list again and select Custom Condition (buildingpermitapplication). Click field and select Role ID. Click value, type 2 and press ENTER. Click the Conditions list again and select Custom Condition (buildingpermitapplication). Click field and select Permit Status. Click value, type APPROVED and press ENTER. Click the Actions list and select Set Field in Current Item. Note: If an action does not appear in this list, select More Actions to find it. Click field and select Processing. Click value and select No. Click the Actions list again and select Set Field in Current Item. Click field and select Role ID. Click value, type 3 and press ENTER. Click the Actions list again and select Send an Email. Click this message. In the Define Email Message dialog box, click the lookup button located to the right of the To text box. Double-click Workflow Lookup. Select Submitter Email from the Field list and click OK. Click OK. Type Approved in the Subject text box and click OK. The action set should appear as shown in Figure 8.
Figure 8. Approval action set
The complete workflow for the end-to-end solution has a single step with three action sets. You have already created the action set for when the compliance reviewer approves the permit application. The second action set handles applications that are rejected by the compliance reviewer, while the third action set addresses forms submitted by the applicant. In this set of steps, you will create the other two action sets to complete your workflow. For all action sets, remember to include a condition where Processing equals Yes and then a corresponding action that sets Processing to No.	Click Add ‘Else If’ Conditional Branch to add conditions and actions for when the application is rejected by the compliance reviewer. After adding the logic for the second action set, click Add ‘Else If’ Conditional Branch again to add conditions and actions for when the applicant submits the form. The complete workflow should appear as shown in Figure 9. Optional: Click any of the e-mail links to add a promoted property value to the message body. Click Finish. Close Office SharePoint Designer 2007.
Figure 9. Complete workflow

Exercise 3 – Creating a KPI

Office SharePoint Server 2007 includes the ability to report against site collection data. In this exercise, you will learn how easy it is to create a KPI for data in the Building Permit Application document library.

Discussion Points

Actions taken

KPIs allow users to report against different types of data in a site collection. You can create a KPI against data in a SharePoint list or Excel workbook, from SQL Server 2005 Analysis Services, or with manually entered information. In this exercise, you will create a KPI for one of your compliance reviewers.

When creating the KPI, you must first copy the URL for the Building Permit Application document library. On the KPI definition page, you type a name and then paste the document library URL in the List URL field. After you tab out of the List URL field, you can create simple logic in the Value Calculation subsection using promoted SharePoint columns. For this KPI, you are calculating the percentage of list items where the Permit Status column value is equal to OPEN and the Reviewer column value is equal to a specific reviewer. For illustrative purposes, Luis Bonifaz will be the reviewer in this exercise. In the Status Icon section, you set goal and warning values to display a status icon for the KPI. After you click OK, the new KPI for open applications assigned to Luis will be rendered. If you go ahead and start some sample building permit applications for different users and then return to this KPI list, you will see how the values for the KPI change.

Note: The browser-based version of the form template can be accessed from http://<your server name>/_layouts/formserver.aspx?xsnlocation=http://<your server name>/permits/buildingpermitapplication/
forms/template.xsn&openin=browser. The browser-based version of an existing form file can be accessed from http://<your server name>/_layouts/formserver.aspx?xmllocation=http://<your server name>/permits/buildingpermitapplication/<file name>.xml&openin=browser. When filling out the form as an applicant, you will need to use lot numbers that are listed in the Property table of the Anytown database.

Navigate to the Building Permit Application document library (http://<your server name>/permits/buildingpermitapplication).
Copy the URL in the Address field.
Navigate to the Reports site.
In the left navigation bar, click Permit KPIs.
In the Permit KPIs list, select New | Indicator using data in SharePoint list.
Type Open Applications Assigned to Luis Bonifaz (or the name of an actual compliance reviewer) in the Name text box.
In the List URL field, paste the URL for the Building Permit Application document library and press TAB.
In the Value Calculation subsection, click Percentage of list items in the view where.
Select Permit Status from the first column list and, in the first value text box, type OPEN.
Select the And radio button.
Select Reviewer from the second column list and, in the second value text box, type Luis Bonifaz (or the name of an actual compliance reviewer). Your Value Calculation subsection should appear similar to what is shown in Figure 10.
In the Status Icon section, type values in the goal and warning text boxes.
Click OK.
Note: Start some sample building permit applications as different users to see how this KPI changes.

Figure 10. Value Calculation subsection

Conclusion

Building and configuring end-to-end solutions with previous versions of Microsoft Office required many hours of development time and many lines of custom code. The Microsoft FabriKam 3.1 Solutions Learning Platform provides a good illustration of this effort. Overall, it took approximately three man-years of development time and more than 20,000 lines of managed code to create the entire platform.

By contrast, end-to-end solutions can be created with the 2007 Microsoft Office release with a significantly reduced amount of time and customization. In this lab, you used Office InfoPath 2007, Office SharePoint Designer 2007, and Office SharePoint Server 2007 to learn how you can build an end-to-end solution rapidly.

Manage Your Profile