Designing and Building Tracking Solutions

Customizing the Outlook Contact Form

Now that you have created the folder that will contain the client contacts for ImagiNET, I will focus on the Contact form customization that must be performed to capture and present the appropriate contact information in the way that best suits the sales team. Let me quickly remind you that the new Client Contact form is based on the default Contact form that is provided with Outlook. Remember that this is one of the built-in Outlook forms that is completely customizable, meaning that you can customize the built-in fields and controls that are displayed on the General tab of the Contact form. This makes your job even simpler.

Reviewing the Client Contact Form

Before I describe how to create it, let's see what the new Client Contact form will look like. Figure 13.3 displays the new Client Contact form.

Figure 13.3
The Client Contact form.

Generally, here are the steps taken to build this form:

1. Put a default Contact form in design mode.

2. Remove the controls and pages that are not needed on the form. Modify existing control properties, such as the controls caption, to meet design specifications.

3. Create new fields that will be used to capture new information on the form. Add these fields, assigned to the appropriate form control, to the form. Arrange all of the controls on the form to the design specifications.

4. Disable all of the existing actions and create a new action that allows a user to take an order from a customer. Order information is stored in a SQL Server.

5. Place VBScript behind the form that displays a list of orders taken by the client directly in the form, in the Orders tab.

6. Set the remaining properties of the form.

7. Name and publish the new form to the Client Contacts public folder.

Creating a New Contact

Let's begin by creating a new contact in the new Client Contacts public folder. I will use this as the template for the new Client Contact form. Next, place the form in design mode by selecting Tools, Forms, Design This Form from the menu bar.

The next step is to create new fields that will capture some ImagiNET specific information types. Create the new fields according to the specifications in Table 13.2.

Table 13.2  New Fields Required by the Client Contacts Form

Field Name	Field Type
ContactID	Text
Region	Text
Type of Company	Text

---

Reusing Built-In Fields

ImagiNET's sales people also want the ability to assign clients to a particular sales person. Instead of creating a new field to capture this relationship, I have decided to reuse the Contacts field for this purpose. This provides a great deal more flexibility in the long run, because selecting a contact for a form brings up the Select Contacts dialog box, allowing the user to select just about any contact in any Contact folder he has access to. Unfortunately, this does not allow the user to assign people from the Global Address Book.

---

Adding New Controls

The next task is to create a couple of new controls on the form for the fields you just created. For the ContactID field, simply use your mouse to drag the field from the Field Chooser dialog box. For convenience, I will assign the Region and Type of Company fields to combo boxes so that users can choose from possible valid values when entering data. This, unfortunately, is a two-step process. For each field, create a new combo box on the form. You must then assign each combo box to the appropriate field by going into the properties of the combo box and selecting the appropriate field in the Value tab of the properties window.

After you have assigned the fields to the new combo boxes, make the form pretty by rearranging and removing unnecessary controls. At this point, you can do just about anything you want to make it look and feel better, such as add graphics and rename controls. Notice that in Figure 13.3, I renamed the Contacts button Assigned to Sales Person, removed a number of unnecessary controls, such as the Address drop- down box, and drastically rearranged the controls on the form to make better sense for the business needs.

---

Tab Order Maintenance

Just remember that when you add or rearrange controls on the form, you should always modify their tab order on the form as well. You can do this by selecting Tab Order from the Layout menu.

---

Always make sure that you publish or save your form frequently. There is no Auto Save feature when developing Outlook forms, and the longer you go without saving or publishing your form, the greater amount of lost work you will have if something goes wrong. You can publish the Client Contacts form by selecting Publish Form from the Tools, Form menu. Outlook confirms the location to which you want to publish the form. In this case, choose the Client Contacts folder by clicking the Browse button. You must also provide a Display Name and the Form name. Type Client Contact in each field.

---

The Client Contact Message Class

Notice the message class that appears at the bottom of the Publish Form dialog box. It should look something like IPM.Contact.Client Contact. This forms message class is comprised of the name of the form that was just provided and the base form that the Client Contacts form was constructed from, Contact.

---

The previous set of steps was pretty darn easy. Let's leave the General tab of the Client Contacts form and move on to some of the other tabs that need modification.

Adding a Tab

As I mentioned earlier, I am going to add a tab to the form that allows sales people to view all of the orders placed for the selected customer. Figure 13.4 should give you a pretty good picture of what I mean.

Figure 13.4
The Orders tab of the Client Contact form.

All you need for this is two controls: a button and a grid of your choice. In this case, I chose to use the Microsoft's DataGrid for no other reason than the fact that you can assign it a recordset and it handles the rest (and the fact that I'm quite lazy).

Let me take a moment to tell you why I chose to put a Refresh Orders Detail button on the form instead of having the DataGrid populated on form open. First, this form will be accessed when not connected to the SQL Server, so any attempt to automatically retrieve data would fail at that time. Second, if all the sales person needs is the contact's phone number, he would probably get annoyed waiting the extra second it takes to fill the DataGrid control.

The first step is to enable one of the unused tabs that appears in design mode. Select the (P.2) tab, enable it, and rename it to Orders using the appropriate options in the Form menu of the Forms Design window. Because I decided to use the Microsoft DataGrid control, the control reference must be added to the control toolbox by displaying the control toolbox and selecting Custom Control after right-clicking some free space in the control toolbox window. This displays the Additional Controls window where you can select the check box beside the Microsoft DataGrid control to add it to the control toolbox.

---

Using Custom Controls on Forms

Remember that for the Client Contact form to work properly, the controls you use on your forms must be present on every computer that launches the form prior to users using the form.

---

The controls that appear on this page of the form are not bound to any data field, which makes your next step pretty straightforward. Simply draw the button and DataGrid controls on the screen. In the properties dialog box of the DataGrid, type dgOrders in the Name field and Orders for this Customer in the Caption field. Similarly, give the button the name cmdRefreshOrders and Refresh Orders Detail as a caption. Additionally, I use script to ensure the position of the DataGrid control on the screen, which executes when the form opens. Example 13.1 illustrates the code that accomplishes this.

Example 13.1  Setting up the form.

Function Item_Open()

' Set the size of the DBGrid control
  Dim dgOrders

  Set dgOrders = Item.GetInspector.ModifiedFormPages("Orders").Controls("dgOrders")

  dgOrders.Top = 12
  dgOrders.Width = 312
  dgOrders.Left = 6
  dgOrders.Height = 192

End Function

The next step is to place additional VBScript code in the click event of the cmdRefreshOrders button. When the user clicks this button, the VBScript should attach to SQL Server using ActiveX Data Object (ADO) and retrieve the orders placed by the currently viewed client contact. Take a look at the VBScript code in Example 13.2 that refreshes order information within the form.

Example 13.2  Refreshing order data.

Function cmdRefreshOrders_Click()

' Connect to SQL Server and get all of the orders from this client
  
  dim adoConnection
  dim sCommand
  dim dgOrders
  dim sCustomerID
  
  On Error Resume Next

  if not (Item.UserProperties.Find("ContactID").Value = "") then
    sCustomerID = chr(39)& Item.UserProperties.Find("ContactID").Value & chr(39)
    sCommand = "Select OrderDate, ProductName,Amount from CustomerOrders where CustomerID = " & sCustomerID & " order by OrderDate"
  
    Set adoConnection = CreateObject("ADODB.Connection")
       Set adoRecordset = CreateObject("ADODB.Recordset")

      adoConnection.ConnectionString = "driver={SQL Server};server=Joelhome;uid=sa;pwd=;database=collabmtp"
    adoConnection.Open

      adoRecordSet.Open sCommand, adoConnection, 1, 3, 1

    if adoRecordSet.RecordCount > 0 then
      set dgOrders = Item.GetInspector.ModifiedFormPages("Orders").Controls("dgOrders")
      set dgOrders.Datasource = adoRecordSet
    else  
      msgbox "There are no orders for this customer"
    end if
  else
    msgbox "The ContactID field is blank. No orders returned."
  end if

End Function

As you can see from the preceding code, this is fairly simple. I wanted to demonstrate two things in this example. First, you have the ability to instantiate and use any COM object registered on your computer, including objects that run in a middle tier solution, such as Microsoft Transaction Server. Second, you can integrate with other services, such as Microsoft SQL Server, to give your forms quite a bit of added functionality. Of course, generating really funky code isn't a lot of fun to do in the Outlook Forms Script Editor due to its Notepad-like qualities, but it gets the job done in the long run. I'm hoping that future releases of Outlook provide something better. Sometimes I feel like I'm programming with VI all over again.

Let's take a quick look at this code again. First, take a look at the name of the function:

Function cmdRefreshOrders_Click()

This is the Click event handler for the cmdRefreshOrders button.

---

Button Event

Remember that a button in an Outlook form has only one event—the Click event.

---

---

Good Coding Practices

One of the things I like to do is make VBScript code as readable as possible, because more than likely you or someone else will have to revisit the code to debug, extend, or modify it. So be nice to others and make sure that your code is properly formatted and commented.

---

Because orders that are stored in the SQL Server database are tied to a client identifier, the code checks to ensure that this contact has this field set. If it does, the code goes on to dynamically construct a SQL query that will be used to obtain the orders from SQL Server. The resulting SQL query should look something like this:

Select OrderDate, ProductName,Amount from CustomerOrders where CustomerID = "ALFKI" order by OrderDate

After the SQL Query has been constructed, the following code connects to the SQL Server and executes the SQL Query to fill a recordset:

Set adoConnection = CreateObject("ADODB.Connection")
Set adoRecordset = CreateObject("ADODB.Recordset")

adoConnection.ConnectionString = "driver={SQL Server};server=Joelhome;uid=sa;
pwd=;database=collabmtp"
adoConnection.Open
adoRecordSet.Open sCommand, adoConnection, 1, 3, 1

---

Late Binding COM Objects

Late binding is used to make reference to COM objects.

---

After the recordset is populated, the DataGrid's DataSource property can be set using the following lines of code:

set dgOrders = Item.GetInspector.ModifiedFormPages("Orders").Controls("dgOrders")
set dgOrders.Datasource = adoRecordSet

Notice how you had to use the implicit Item object to get a reference to the control on the form.

---

Using Literals

When writing in VBScript, there is a tendency to use a lot of literals throughout your code. In practice, you should try to minimize this or at least format your code so that these literals stand out and easy to change.

---

All you need now is some cleanup work and the form should be complete. Let's start by configuring the built-in actions on this form. When the form is in design mode, select the Actions tab and configure the listed actions according to Table 13.3.

Table 13.3  The Form's Action Properties

Action	Property
Reply	Disable
Reply to All	Disable
Forward	Disable
Reply to Folder	Disable

You might have noticed that the form depicted in Figures 13.3 and 13.4 has an extra action called Take Order. This new action simply launches another Outlook form that is responsible for gathering and submitting order information. The Take Order form does not create any new Outlook items; it simply gathers order information and stores it directly into the SQL Server database that maintains order and product information. I will cover the creation of the Take Order form in Chapter 14, "Designing and Building Workflow Solutions," because a bit of workflow will be added to this portion of the solution.

The final task that must be completed is to set the forms properties using the Properties tab found on the form at design time, according to Table 13.4.

Table 13.4  Properties of the Client Contact Form

Property	Value
Category	Sales and Marketing.
Sub-Category ManagementContact	Client Contact Provide a valid contact name.
Version	1.0.
Form Number	Provide a form number that helps you locate or identify this form.
Password	Enter a password for this form to prevent users in a production environment from placing the form in design mode.

Finally, publish the Client Contact form in the Client Contacts public folder, if you haven't already done this, and you are done.