Designing and Building Tracking Solutions
The following scenario outlines what you will build in this chapter. ImagiNET Resources Corp. employs a number of sales people responsible for the acquisition of new business and the maintenance of existing clients. ImagiNET has grown considerably over the last three years and is now in dire need of a new Sales Contact Management and Order Entry System. ImagiNET Resources has a number of extremely talented developers on staff who are currently between projects, so ImagiNET has decided to build its own solution to best match its current needs. By developing an in-house solution, ImagiNET can utilize staged releases, adding new functionality at each release while maximizing the usage of each component as it is released.
The first release of the Sales Contact Management and Order Entry System (SCOES for short) will focus primarily on a contact management solution tailored to the ImagiNET's sales staff. Additionally, the order entry component's design is well underway and will be built upon a SQL Server 7.0 database.
To determine the needs of the resulting contact management solution, the developers staged a number of design meetings with ImagiNET's sales team. The developers quickly arrived at the following design goals:
The sales staff wants a way to specify which sales person is assigned to which client.
The sales staff wants to use Microsoft Outlook and Microsoft Word exclusively to manage and send correspondence to their many assigned clients. The sales staff wants to use Outlook for the following reasons:
All members of the sales staff are proficient users of Outlook and do not want to relearn how to use another contact management application.
The sales staff appreciates the tight integration that Outlook has with the rest of the Microsoft Office suite of products. The sales staff finds it extremely easy to right-click a contact from a Contacts folder to create a message, task assignment, appointment, or journal item associated with that contact.
Everyone in Sales uses folder views to organize, sort, and filter contact information, and they do not want to switch to other methods.
All of the sales people have mobile computers and must have the ability to work with their client information offline. They want to have the ability to add, delete, and modify client information offline and, when online, have their changes automatically propagated to everyone else in the organization.
These goals weren't quite what the developers were expecting because the order entry component had already been designed around a SQL Server database for efficiency and performance reasons. To accommodate both ImagiNET's sales force's requirements and the order entry design, the developers decided to take the following steps to arrive at a solution for Release 1 of SCOES:
Create a Client Contacts public folder to house the client contact information required by ImagiNET's sales force. Additionally, a Client Activity Journal public folder will be created to maintain client journal entries.
Customize the built-in Contacts form to capture and display client information in a format that would best suit the sales staff. This new form, called Client Contact, would then be the default post form for the new Client Contacts public folder.
Create an event scripting agent to handle one-way synchronization of contact information from the Client Contacts public folder to a SQL Server database.
Finalize the Microsoft Outlook client components, such as creating the appropriate views and configuring the new folders for offline use.
So get comfortable, sit back, put up your feet, and let me explain how to build SCOES.
Review the Design
Let me take a few minutes to formalize the design to make sure that we are all on the same playground. The entire premise of this solution revolves around the reuse and customization of the built-in Outlook Contacts form to meet the needs of ImagiNET's sales staff. The Outlook Contacts form will be modified to remove the fields that are not needed while adding fields and controls to capture additional information required by the sales staff.
Just to make things more interesting, the new Client Contact folder will have an additional tab that allows users to view all of the orders for that client. This information will come directly from a SQL Server. Of course, this functionality only works if the sales person is connected to the same network as the SQL Server, either directly or through a VPN connection. In addition to this new public folder, a corresponding folder that holds all of the contact activity for the new Contacts public folder will also be created.
If ImagiNET wants to store order and product information in a SQL Server, client information must also be accessible from the SQL Server to ensure that the appropriate relationships and references are established. Because Exchange 5.5 does not support OLEDB, you must replicate the information that resides in the new Contacts public folder to the SQL Server using an event scripting agent. Every time a new contact is added to the associated public folder, an Add event fires. Code is written behind this event to add a new entry to a Clients table in a SQL Server database. The same is true when Contact items are modified or deleted. Their counterpart records in SQL Server are correspondingly modified or deleted. To make life a little simpler, replication only happens in one direction, from the Exchange public folder to the SQL Server.
Pictures make more sense than words, so take a look at Figure 13.1 for an overview of this solution.
){kind=link}
Would You Do the Same Thing with Exchange 2000?
If you are using Exchange 2000, is it necessary to create a synchronization routine between an Exchange public folder and a SQL Server database? Probably not. Remember that Exchange 2000 now supports OLE DB, even though this is server side only. Smart developers can use this feature to create a linked server in SQL Server (assuming that the SQL Server resides on the same server as the Exchange Server) allowing the use of distributed queries. This allows you to store all contacts, for example, in Exchange exclusively, which is much nicer than solutions that require synchronization.
Figure 13.1
The SCOES solution architecture.