Office Business Application for Sales Proposal Document Management

Article
07/09/2014

Summary: Learn how to use the Microsoft Office platform to create a custom solution that allows multiple users to collaborate on the creation of a sales proposal response document. (22 printed pages)

Joel Krist, Akona Systems

February 2008

Applies to: 2007 Microsoft Office System, Microsoft Office SharePoint Server 2007, and Microsoft Visual Studio 2008.

Download sample files: 2007 Office System Business Application: Sales Proposal Document Management.

Contents

Overview
Using the RFP Document Management Add-in
Using the RFP Web Service
Using Custom Workflows in Office SharePoint Server 2007
Design Decisions
Enabling Extensibility
Conclusion
Additional Resources

Overview

Many organizations that offer a commodity or service accept Request for Proposal (RFP) documents from their customers. An organization that receives an RFP then creates an RFP response document for the customer that provides the answers to the questions asked in the RFP.

An RFP can include requests for information on topics that span a wide range of areas. Some examples are requests for corporate information, pricing quotes, product specifications, and technical details. The odds are slim that a single individual will have the knowledge and expertise to answer all of the questions posed in a complex RFP, requiring the assignment of the different areas of the RFP to different people.

A collaborative document editing process that allows multiple people to work simultaneously on their assigned areas is more efficient than a serial editing process and decreases the amount of time required to create an RFP response document, giving an organization a competitive advantage during the bidding process.

This article presents an example of an Office Business Application built on the Office platform that supports collaborative creation of an RFP response document. The solution allows a team lead to assemble a virtual team and then assign different regions of an RFP to team members. After making the necessary assignments, the team lead dynamically creates a document workspace from within Microsoft Office Word 2007 that is based on the RFP document. The document workspace makes use of custom workflows in Microsoft Office SharePoint Server 2007 to manage the collaborative editing, review, and reassembly of the final response document.

Office Business Application makes use of the following key Microsoft products and technologies:

Microsoft Office Outlook 2007
Microsoft Office SharePoint Server 2007
Microsoft Office Word 2007
Microsoft SDK for Open XML Formats
Windows SharePoint Services 3.0
Microsoft Visual Studio 2008
Microsoft Visual Studio Tools for the Microsoft Office system (3.0)

Scenario Overview

The scenario begins when a sales team lead receives an RFP as a Word document from a customer. The team lead installed a Word 2007 application-level add-in that allows him to manage the collaborative creation of a response to the RFP. He opens the RFP document in Word and selects the custom Microsoft Office Fluent Ribbon, RFP Document Management that is created by the add-in.

Figure 1. RFP document management Word add-in

RFP Word add-in

Because the document is not yet a managed RFP response document, the add-in displays the Team Lead RFP Activities group on the custom Ribbon. The buttons in the group allow the team lead to assemble a virtual team, assign sections of the document, display a custom task pane, and start the collaborative editing process.

After reading over the RFP, the team lead can identify the people on his sales team he would like to assign document sections. The team lead clicks Create RFP V-Team ribbon button, which displays a form.

Figure 2. Create RFP V-Team form

RFP V-Team form

The RFP Document Management add-in includes a configuration file that contains a setting for the top-level SharePoint site that the RFP document workspace sites are created under. The Create RFP V-Team form uses this information to get the list of SharePoint groups defined on the site. In this example, the team lead's sales team members are in the group named Sales Team 1. The team lead selects the group in the Available Groups tree, which causes the Group Members list to display the groups members. Double-clicking a group member adds them to the virtual team members list. Double-clicking a member in the virtual team members list removes them from the virtual team. After the team lead adds the desired members to the virtual team, they click OK to close the form.

After the team lead assembles his virtual team, next he assigns sections of the RFP document. To manage the current assignments, he clicks RFP Task Pane to display the Team Lead RFP Task Pane.

Figure 3. Team Lead RFP task pane

Team Lead task pane

The team lead then selects a region in the document to assign, clicks Assign Document Section, and then picks the virtual team member to assign the section.

Figure 4. Assigning document sections

Assigning document sections

As the team lead makes assignments, the members are displayed on the Assign tab in the Current Assignments tree of the task pane. Clicking an assignment in the tree highlights the corresponding assigned section in the document. On the Assign tab, click Delete Assignment to delete the currently selected assignment.

Figure 5. Displaying current assignments

Displaying current assignments

After the team lead assigns document sections, next he clicks Create RFP Workspace. On the Create RFP Workspace form, he can specify the name of the document workspace. The team lead enters a name and clicks OK to create the workspace.

Figure 6. Create RFP workspace form

Create RFP Workspace

When the team lead clicks OK, the add-in calls a custom Web service to dynamically create a SharePoint document workspace site based on a custom template. It creates subdocuments for each team member who is assigned document sections. It locks the entire document and inserts a rich text content control below a team member's assigned section to allow the team member to focus on just their assignments. When ready, the subdocument is uploaded to the document workspace. Then, the add-in then adds content controls to the original RFP document. The content controls are part of the document review process. The add-in saves the RFP document to a document library on the document workspace.

Table 1. Lists in the RFP document workspace

List	Description
Response Document	A document library that contains the final version of the RFP response document.
Master Document	A document library that contains the master RFP document with markup added to track assignments and support the editing review process. A custom state machine workflow in Office SharePoint Server 2007, named RFPMasterDocumentWorkflow, is associated with items in this library and manages the overall response document review and editing process.
Assigned Documents	A document library that contains documents that are assigned to team members for editing and to the team lead for reviewing. A custom sequential workflow in Office SharePoint Server 2007, named RFPSubDocumentWorkflow, is associated with items in this library and manages the editing of a single assigned document.
Tasks	A task list that contains the tasks associated with editing and reviewing the documents in the Assigned Documents library.

The following figure shows the RFP document workspace in its initial state.

Figure 7. RFP document workspace initial state

RFP document workspace initial state

When the add-in uploads the RFP master document and subdocuments to the site, the associated workflows start automatically on item creation. The subdocument workflow creates the task associated with an assigned document. Then, it sends an e-mail to the task owner, and notifies the master document workflow when the task completes. The master document workflow generates a proposed response document from the current versions of the subdocuments. It sends it for review to the team lead. It also generates the final response document when the proposed response document is finally approved.

The master document workflow is a state machine workflow. It supports the ability of the team lead to reject a proposed response document. When a document is rejected, the editing process restarts with new subdocuments created for the rejected sections. The master document workflow manipulates Word documents on the server using objects based on the Open XML formats. For more information, see Open XML Format SDK 1.0.

When the team lead assigns a team member to a related task, the team member receives an e-mail alert containing a link to her assigned document.

Figure 8. Assigned RFP document in Outlook

Assigned RFP document in Outlook

In the e-mail alert, the team member clicks the link to open the assigned Word document. A copy of the original document displays, showing the context of her assigned sections. However, she can only edit her assigned regions.

Figure 9. Word document with assigned sections

Word document with assigned sections

The team member decides to do some research. She installs the same Word 2007 application-level add-in as the team lead and then clicks the custom RFP Document Management Ribbon. The add-in determines that the current document is a managed RFP subdocument and displays buttons specific to that document type. In this case. there is a single button that shows or hides the RFP task pane.

Figure 10. Team Member RFP task pane

Team Member RFP task pane

Using the custom task panes, team members can perform keyword searches across the Internet and enterprise data. The internet searching functionality uses the Live Search Web Service API. The enterprise data search uses the Windows SharePoint Services Query Web Service Service to perform scoped searches across a custom SharePoint list that contains archived questions and answers from previous RFPs. The list is located on the parent site of the RFP document workspace site.

The team member enters keywords, selects the domains to search across, and clicks Search. A substring of each search result is displayed in the Results tree. Clicking a result displays the full result in the Full Answer text box. The full question is displayed in the Full Question text box if the result came from the list on the parent site that contains archived RFP questions and answers.

The Live Search Web Service returns a limited description of a result. The team member can press CONTROL and then click on a search result node to navigate to the original source of the result. If the user sees a search result that she wants to use, she can select the result and then click Insert Answer on the task pane to insert the full answer text into the document at the current insertion point.

Figure 11. Search results

Search results

The team member edits her assigned section, saves the document and then clicks Edit this task…. The task opens in the SharePoint list where she can mark it as completed.

Figure 12. Marking a task as completed

Marking a task as completed

The RFP subdocument workflow instance that is associated with the subdocument refreshes when a user sets a task to complete. When this occurs, the workflow notifies the RFP master document workflow. If there are still incomplete subdocuments, the RFP master document continues to wait.

After all subdocument tasks are set to complete, the RFP master workflow assembles a proposed RFP response document from the subdocuments. It copies the proposed document to the Assigned Documents document library causing an instance of the RFP subdocument workflow to start for the item. The subdocument workflow creates a new task for the team lead and sends an e-mail alert containing a link to the proposed document.

Figure 13. Proposed document alert in Outlook

Proposed document alert in Outlook

The team lead clicks the link to open the assigned document. The proposed response document is displayed showing the content of the original RFP document with the merged content. The content from each team member is displayed in a rich text content control and a combo box content control is displayed for each region, allowing the team lead to add comments and approve or reject the response document on a section by section basis.

Figure 14. Reviewing proposed response document

Final RFP document in Outlook

The team lead reviews the response document, adding comments and approving or rejecting sections. He saves the document and then click Edit this task…. The associated task item in the SharePoint list opens and he can update the status as appropriate.

Setting the task item to complete indicates to the subdocument workflow the new status. The subdocument workflow notifies the RFP master document workflow. The RFP master document workflow processes the proposed document to determine the approval status. If any of the assigned regions were rejected, the master document workflow assembles subdocuments for the rejected sections, saves the subdocuments to the Assigned Documents document library, sends new e-mail alerts to the team members, and waits for all subdocument tasks to complete.

The entire subdocument workflow repeats until the team lead approves all assigned regions in the proposed document. After reaching approval, the master document workflow generates the final document from the proposed response document. It adds an archive copy of each question and answer pair to the custom SharePoint archive list. It also saves the final document to the Response Document library, and sends an e-mail to the team lead.

The team lead clicks the link to the document to view the RFP response document. The document shows the final approved content as provided by each team member. The team lead clicks the custom RFP Document Management Ribbon. The RFP document management add-in determines that this is an approved response document and so displays the Team Lead RFP Activities group with the RFP management startup related buttons disabled.

The team lead selects Finalize Response Document and reviews the document. At this time, he can remove any remaining internal markup. The team lead can then send the document to the customer.

Figure 15. Finalizing the RFP response document

Finallizing the RFP response document

Using the RFP Document Management Add-in

The RFP Document Management add-in is a Word 2007 application-level add-in that provides a custom Office Fluent Ribbon and custom task panes for use during the collaborative creation of an RFP response document.

Implementing the RFP Document Management Add-In

The RFP Document Management add-in was created using Microsoft Visual Studio Tools for the Microsoft Office system (3.0). While the add-in is an application-level add-in, it maintains state on a per document basis, allowing it to track and display the section assignments for the associated document. The add-in also performs document type and state checking. This enables it to display the correct Ribbon group and button states depending on the active document.

The add-in makes use of the RFP Web service to create a document workspace from a custom site template. For more information, see Using the RFP Web Service.

Deploying the RFP Document Management Add-In

Microsoft Visual Studio 2008 was used to generate the files needed to support ClickOnce deployment of the add-in. For more information about support for Visual Studio ClickOnce deployment, see What's New in Visual Studio 2008.

Key Sample Files

The following table lists key add-in files.

Table 2. Key RFP Document Management add-in files

File	Description
ThisAddIn.cs	Provides the custom task pane and document type and state management for the add-in.
TeamLeadCTP.cs, TeamMemberCTP.cs	The code files for the add-ins custom task panes.
RFPAssignmentManager.cs	The code file for the class used by the add-in to manage document section assignments.
ctlSearch.cs	The code file for the user control used by the add-in to support searching from the custom task panes.

Using the RFP Web Service

By default, Windows SharePoint Services 3.0 provides numerous Web services, such as the Document Workspace Web Service. The Document Workspace Web service supports dynamic creation of sites but is limited to using the standard document workspace site template.

The Sales Proposal Document Management solution requires additional document libraries and associated workflows that are not part of the standard document workspace site template. The custom RFP Web service allows the RFP Document Management add-in to create sites dynamically based on a custom site template.

Implementing the RFP Web Service

Web Service Methods

The RFP Web service exposes a single method:

Table 3. RFP Web Service Method

Method	Description
CreateDocumentWorkspaceFromTemplate	Creates a site based on site template.

Deploying the RFP Web Service

The RFP Web service uses the Microsoft.SharePoint.SPContext class to determine the correct site collection to create an RFP document workspace site under the top-level SharePoint site. The Web service must be running in the context of a Microsoft SharePoint Products and Technologies deployment to do this.

Deploying a Web service to run in context of Office SharePoint Server 2007 requires creating and deploying static discovery and WSDL files. For more information about creating and deploying a custom Web service to Office SharePoint Server 2007, see Walkthrough: Creating a Custom Web Service.

Key Sample Files

Table 4. Key RFP Web service files

File	Description
Service.cs	Provides the implementation of the WebService class and the CreateDocumentWorkspaceFromTemplate method.
RFPServicedisco.aspx, RFPServicewsdl.aspx	Provides discovery and description for the Web service.

Service.cs

Provides the implementation of the WebService class and the CreateDocumentWorkspaceFromTemplate method.

RFPServicedisco.aspx,

RFPServicewsdl.aspx

Provides discovery and description for the Web service.

Using Custom Workflows in Office SharePoint Server 2007

The Sales Proposal Document Management solution uses two custom workflows in Office SharePoint Server 2007, named RFPMasterDocumentWorkflow and RFPSubDocumentWorkflow. The workflows handle the assignment of tasks and document manipulation required to generate an RFP response document collaboratively.

The workflows communicate by using custom document library metadata. The Master Document library defines a custom DateTime column named RFP Sub-Document Edit Completed that the subdocument workflow updates when a subdocument editing task is completed. The update of the metadata notifies the master document workflow that all subdocument tasks are complete and that the team lead finished reviewing the proposed response document. The Assigned Documents library defines a custom Boolean column named Submitted for Review. The master document workflow uses this metadata to determine if there are any incomplete subdocuments. The subdocument workflow uses this metadata to determine if it should execute for a given document. The subdocument workflow sets the value of the Submitted for Review column when the user sets the subdocument editing task to complete.

Implementing Custom Workflows in Office SharePoint Server 2007

RFP Master Document Workflow

The RFP master document workflow is a state machine workflow in Office SharePoint Server 2007 that manages the overall RFP response document creation process. It is associated with items in the Master Document library. An instance of the workflow is created when the add-in uploads the RFP document to the library. The following figure shows the states, activities, and transitions of the workflow.

Figure 16. RFP Master Document Workflow States

workflow states

RFP Sub-Document Workflow

The RFP subdocument workflow is a sequential workflow in Office SharePoint Server 2007 that manages the editing of documents in the Assigned Documents library. On activation, the workflow verifies that the associated document is submitted for review by checking the value of the custom Submitted for Review library column. If the document is not submitted for review, the workflow creates a task for the document owner and then waits for the task to be set to complete.

Upon a change to the metadata, the workflow verifies the value is set to complete. If it was not, it continues to wait. If the value is set to complete, the workflow updates the Submitted for Review value and then notifies the RFP master document workflow by updating the value for RFP Sub-Document Edit Complete.

Figure 17. RFP Subdocument workflow

sub-document workflow

Key Sample Files

Table 5. Key Workflow Files

File	Description
RFPMasterDocWorkflow.cs	Contains the implementation of the RFP master document workflow.
RFPDocumentManager.cs	Contains the implementation of the RFPDocumentManager class used by the RFP master document workflow to manipulate Word documents using OpenXML.
RFPSubDocWorkflow.cs	Contains the implementation of the RFP subdocument workflow.

Design Decisions

The following section provides the rationale behind some of the design decisions and choices made during the implementation of the Sales Proposal Document Management Office Business Application.

Workflow Design Requirements

The decision to use two separate workflows rather than a single workflow is based on two requirements:

The desire to keep things simple for purposes of illustration. While you can implement the solution as a single complex workflow, doing so makes the final solution more difficult to understand. There are also issues related to the difficulty of creating multiple tasks for multiple users that disappear when you use a single sequential workflow instance for each subdocument.
The subdocument editing process fit the sequential workflow model while the response document creation and review process managed by the master document workflow more closely fit the state machine model. It is true that the current implementation of the master document workflow only has one state change that goes from a later state to an earlier state and that you could implement it as a sequential workflow. However, for extensibility, we felt like a state machine model would more appropriately support additional states such as a more complex review process.

Enabling Extensibility

The Sales Proposal Document Management Office Business Application presented illustrates using the Microsoft Office system to implement a collaborative document editing system. While complex, the provided solution is intended to be an example of what is possible with the Microsoft Office system rather than an example of a full-fledged collaborative document editing solution. The following sections provide some ideas of how you can extend the current implementation to provide a more complete solution.

Extending the Workflow Design

The master document workflow currently supports a basic review process by a single individual, the team lead. You can extend it to support review by multiple individuals, including extending into legal departments that have their own workflow process.

You can implement additional states to support digital signature gathering as part of the review process.

You can augment the OpenXML document manipulation performed by the master document workflow to support complete scrubbing and finalization of the RFP response document on the server.

The archiving of previous RFP questions and answers currently stores only the question and answer text from the RFP document. There is no preserving of any formatting information. You can extend the solution to support archiving of the formatted content as well.

Extending the RFP Document Management Add-In

You can add risk analysis functionality to the beginning of the RFP response process, allowing the individual receiving the RFP to use an RFP risk assessment tool to determine whether the RFP is worth pursuing. The risk assessment tool makes use of an Excel worksheet that is published to Excel Services to perform the needed calculations on the server. Only if the risk versus opportunity is deemed acceptable will the RFP response process continue with the assignment to the Business Team Lead.

Office Outlook can synchronize items in a SharePoint task list to an Outlook inbox. The Sales Proposal Document Management solution does not currently take advantage of that feature but you can extend it to do so.

You can add additional buttons to the custom Office Fluent Ribbon to allow the team lead and team members to submit their assigned document to the correct document library and automatically mark the associated task as completed, removing the requirement that the user go to the SharePoint site.

You can extend the enterprise searching functionality to support returning and inserting more than just text.

Conclusion

This article provides an overview of a solution that uses the Microsoft Office system to use existing and familiar software assets and enterprise services to support collaborative document editing. Technologies and products used included: