4.3.2 Automation

Automation

The Automation category features a number of classes that make it easy to deal with server applications such as Word, Excel or Graph. The problems resolved by these classes are among the most frequently encountered, at least as far as I can tell by the questions I receive. My personal favorite is the Mail Merge class (discussed later).

 

Cross Tab

Class	_xtab
Base class	Custom
Class library	_utility.vcx
Parent class	_custom
Sample	...\Samples\Vfp98\Solution\Ffc\automate.scx
Dependencies	_base.vcx, vfpxtab.fxp (_GENXTAB)

The Cross Tab object creates a cross-tab query and places the data in a cursor. A cross-tab query is a set of records in spreadsheet format, such as sales by country and month, where the months are placed on the horizontal axis (columns) and countries are displayed vertically (rows).

The Cross Tab foundation class uses the Cross Tab Wizard engine to create a cross-tab query. The Cross Tab is based on the currently selected data source. The data source must have at least three fields, one for each axis and a third for the actual content. Using the Cross Tab object is simple and straightforward:

oCrossTab = NewObejct("_xtab","_utility.vcx")
SELECT country, month, maxordamt FROM Sales INTO CURSOR Temp
oCrossTab.RunXTab()

By default, Field 1 is placed on the vertical axis (rows), Field 2 is used for the columns, and the order amount (Field 3) is used as the actual field content. You can influence this through various properties of the Cross Tab object. Here is another example:

oCrossTab = NewObejct("_xtab","_utility.vcx")
SELECT country, month, maxordamt FROM Sales INTO CURSOR Temp
oCrossTab.nRowField = 2
oCrossTab.nColField = 1
oCrossTab.RunXTab()

In this example I switched the position of the rows and columns. You could also use a different field for the data by setting the oDataField property. Specifying different field numbers is also important when using a data source with more than three fields.

Table 1 shows an example of the retrieved result.

Table 1. The first few rows and columns of a cross-tab query.

	January	February	March	April
Canada	15,000	13,000	16,000	15,000
Europe	124,000	109,000	134,000	128,000
Mexico	.NULL.	.NULL.	6,000	11,000
United States	210,000	198,000	245,000	229,000

Additional properties allow you to specify details such as whether a thermometer should be displayed during creation of the result cursor (lShowThem), whether the name of the result file can be specified (cOutfile), whether one can specify that totals should be calculated (lTotalRows), and whether null values should be displayed as 0 or .NULL. (lDisplayNulls).

Subclassing this class doesn't make a lot of sense, because it just calls the Cross Tab Wizard behavior, which is defined in a program you can specify using the _GENXTAB system variable. The class itself is merely a simple wrapper that has only a couple of lines of code. If you wanted to change the underlying behavior, you have to change the program referenced by _GENXTAB.

Graph by Record

Class	_graphbyrec
Base class	Container
Class library	Utility.vcx
Parent class	_container
Sample	...\Samples\Vfp98\Solution\Ffc\graphrec.scx
Dependencies	_base.vcx, _utility.h, MS Graph

The Graph by Record object renders a graph (on a row-by-row basis) using Microsoft Graph. This foundation class has a relatively rich interface that allows the user to navigate through records, choose the kind of diagram and set some options, such as whether they want to see a legend or whether they want to plot by row. Figure 1 shows a simple use of the Graph by Record foundation class. I simply dropped the class on a form, added a table with four numeric fields to that form, and ran it.

Figure 1. The Graph by Record object rendering a 3-D pie chart using default settings.

The rendered graph is a little rough. The caption (Record: 1) is not informative, and if the user decides to show the legend, the information isn't very useful. Luckily it's easy to define how you'd like your graph to look. The sample that ships with Visual FoxPro demonstrates this well. Figure 2 shows the form Graphrec.scx, which is located in the FFC subdirectory of the Visual FoxPro Solutions sample. Note that this is one of those places where the Foundation Class documentation is not only incomplete, but also wrong. The Automate.scx form does not have a Graph by Record sample.

Figure 2. The more sophisticated Graph by Record Solutions sample shows more useful information.

Figure 2 shows a more informative caption than in Figure 1. This caption actually comes from the underlying data source. We simply need to specify the field name that contains this information. This can be done in the property cGraphField. Not only is the Figure 2 caption more informative, but the legend also contains useful information. This can be specified in the aDataFields array. Because array values cannot be configured through the property sheet, this has to be done in source code like so:

DIMENSION THIS.aDataFields[2]

THIS.aDataFields[1] = "unit_price"

THIS.aDataFields[2] = "unit_cost"

The aDataFields array allows you to define what fields should be used in the graph and in what order. This is extremely important, because in complex scenarios you don't want to display every single numeric field in the graph.

There is one less obvious thing I want to mention about Figure 2: The graph type of this diagram is initially set to "3D Column," which is a non-default setting. It can be defined through the nChartType property. Table 2 shows all valid values. Please note that the values described in the VFP documentation are wrong!

 

Table 2. Valid diagram types.

Value	Diagram Type	Defined in (_utility.h)
76	Area	I_AREA_GRAPH
78	3D Area	I_AREA3D_GRAPH
57	Bar	I_BAR_GRAPH
60	3D Bar	I_BAR3D_GRAPH
51	Column	I_COLUMN_GRAPH
54	3D Column	I_COLUMN3D_GRAPH
5	Pie	I_PIE_GRAPH
-4102	3D Pie	I_PIE3D_GRAPH
4	Line	I_LINE_GRAPH
-4101	3D Line	I_LINE3D_GRAPH

A couple of additional properties allow you to influence the appearance of the rendered diagram. You can set these properties through the provided user interface or predefine them directly through the property sheet. Among them are lAddLegend and lSeriesByRow. You can set those properties programmatically during runtime and the graph will be refreshed immediately. This is accomplished by access and assign methods that trigger a RefreshGraph() when one of the properties changes. You can call this method at any time to refresh the contents of the graph.

So, what if the interface doesn't match the rest of your application? This is a common concern, but it's no problem at all. Figure 3 shows essentially the same form as Figure 2 (as far as the underlying objects go), but I changed the interface somewhat. I used an ActiveX control to create a fancy toolbar (and set many of the original controls to invisible) and changed the size of the diagram to make it easier to read.

Figure 3. The Graph by Record object using a different (custom) interface.

 

As I mentioned above, the Graph by Record object uses Microsoft Graph to render the diagram. This is done using Automation and inplace activation. (Inplace activation is one of the oldest parts of COM/OLE. Using inplace activation, another application such as Graph is embedded and activated in another document, such as a Visual FoxPro form.) As with all objects of that nature, the user can double-click on the object to activate it and utilize its entire functionality. For the Microsoft Graph object, this means that the user is able to modify the underlying data as well as a number of display options, such as 3-D angle, color, and the like. Figure 4 shows some of the possibilities.

Figure 4. The activated MS Graph object can be customized substantially.

Graph Object

Class	Autograph
Base class	Custom
Class library	Autograph.vcx
Parent class	Automation
Sample	...\Samples\Vfp98\Solution\Ffc\automate.scx
Dependencies	Automate.vcx, Automate.h, Autograph.h, MS Graph

The Graph object provides the base functionality for communication with Microsoft Graph. This object is used by the more abstract graph objects in the Fox Foundation Classes.

 

Mail Merge Object

Class	Mailmerge
Base class	Custom
Class library	Mailmrge.vcx
Parent class	Automation
Sample	...\Samples\Vfp98\Solution\Ffc\automate.scx
Dependencies	Automate.vcx, mailmrge.h

Mail merge is one of the features that every programmer has to implement at one point in his career. Typically data is supposed to be sent to Microsoft Word. This is not a trivial task, and most programmers have a hard time with it, especially because Word has changed over time, and some users still have older versions that must be supported as well, and so forth.

The Mail Merge foundation class is of great help when it comes to implementing this functionality. It's easy to use, and it represents the same mechanism used by the Visual FoxPro Mail Merge Wizard. In the following example I open the Customer table, make sure the database is set properly (this is a common cause of failure for the Mail Merge object), set a couple of properties to make sure the object knows about the data I want to export, and finally I retrieve the names of all the fields and put them in the aAutoFields array. Now I'm ready to rock 'n' roll, which I do by calling the MakeOutput() method:

LOCAL loMailMerge, lnCounter

loMailMerge = NewObject("mailmerge","mailmrge.vcx")

SELECT 0

USE Customer

SET DATABASE TO TestData

* We decorate the mail merge object

loMailMerge.cAlias = Alias()

loMailMerge.cDBCTable = DBF()

loMailMerge.cDBCName = DBC()

* We retrieve information about the fields in the current table...

DIME loMailMerge.aAutoFields(FCount(),1)

FOR lnCounter = 1 TO FCount()

loMailMerge.aAutoFields(lnCounter,1) = Field(lnCounter)

ENDFOR

* We do the merge

loMailMerge.MakeOutput()

USE

In this example I use the Customer table, but you could substitute any table and the code would be generic enough to handle it. When you do that, make sure the correct database is specified in the cDBCName property. When testing the code above, I first forgot to set the database. For this reason, the DBC() function returned a wrong database name, and the whole scenario failed, showing a very uninformative error message.

The Mail Merge class uses the specified data source by telling Word to open the specified data source using ODBC (or other mechanisms if ODBC isn't available). Based on this data source, Word creates a new mail-merge document. This document has a Mail Merge toolbar (see Figure 5) that allows the user to select fields and place them in a document. However, this might not be the desired result.

Figure 5. The Mail Merge toolbar shows all the fields from a FoxPro data source.

In many scenarios, users want to reprint mailings that they've already done, or at least use some templates. You can allow this by configuring the Mail Merge object to use an existing document and by specifying a document name like so:

loMailMerge.nNewDoc = 2
loMailMerge.cDocName = "C:\My Documents\MyMergeDoc.doc"

In this example I set the nNewDoc property to 2, which specifies that I want to use an existing document (setting it to 1 would specify that I wanted to create a new document). If you prefer using words instead of numbers to define whether a new or existing document should be used, you can use predefined constants defined in mailmrge.h, like so:

#INCLUDE mailmrge.h
loMailMerge.nNewDoc = N_EXISTING_DOC

In fact, this is the better way to do it if the values change in future versions. The available constants are N_EXISTING_DOC and N_NEW_DOC.

The first time the user wants to do a mail merge, he won't have an existing document. In this case, you should let him choose what kind of document he wants to create. Available are Letter, Label, Envelope and Catalog. You can specify these types through the nTemplate property:

#INCLUDE mailmrge.h
loMailMerge.nTemplate = N_FORMLETTER
loMailMerge.nTemplate = N_LABEL
loMailMerge.nTemplate = N_ENVELOPE
loMailMerge.nTemplate = N_CATALOG

The differences between these document types are significant. The letter creates a document for each record, while the catalog puts many records on one page. Labels and envelopes are self-explanatory.

When communicating with applications such as Word through automation, the server application might display some messages. In this case you want the dialog to look like it is one of yours. You can do so by setting the window title of all dialogs through the cAppTitle property.

Pivot Table

Class	Pivottable
Base class	Custom
Class library	Pivtable.vcx
Parent class	Automation
Sample	...\Samples\Vfp98\Solution\Ffc\automate.scx
Dependencies	Automate.vcx, pivtable.h

Pivot tables are great tools. They are interactive tables that summarize large amounts of data in a cross-tab format. Unfortunately, they are hard to create. The Pivot Table foundation class carries the burden of most of this complexity. You can rotate the table's rows and columns to see different summaries. You can also apply filters to see different pages of data, and even drill down to see the details that resulted in the displayed summary. Figure 6 shows a simple example. Note that the fields that look like buttons can be dragged around on the document to summarize the data differently. Also, the combobox in the first row can be used to filter data.

Figure 6. A simple pivot table.

Creating the pivot table in Figure 6 with the help of the Pivot Table object is relatively easy. First you need to open a data source. The name of the table, the database and the fields you want to use must be specified through the appropriate properties of the Pivot Table object. Also, you have to specify which fields to use in the initial table and where to place them. All of this can be done like so:

LOCAL loPivotTable

loPivotTable = NewObject("pivottable","pivtable.vcx")

SELECT 0

USE Customer

SET DATABASE TO TestData

* Now we decorate the pivot table object...

loPivotTable.cAlias = Alias()

loPivotTable.cDBCTable = DBF()

loPivotTable.cDBCName = DBC()

DIME loPivotTable.aAutoFields(4,1)

loPivotTable.aAutoFields(1,1) = "city"

loPivotTable.aAutoFields(2,1) = "maxordamt"

loPivotTable.aAutoFields(3,1) = "country"

loPivotTable.aAutoFields(4,1) = "cust_id"

loPivotTable.cPivFldRow = "city"

loPivotTable.cPivFldData = "maxordamt"

loPivotTable.cPivFldPage = "country"

* We create the table...

loPivotTable.MakeOutput()

* We close the data

USE

The first couple of lines are easy to understand. I create the Pivot Table object and open the data I want to use. Then I specify the data in the object by setting the cAlias, cDBCTable and cDBCName properties. I'm using the DBC() function to retrieve the name of the database. If you do that, make sure the correct database is set; otherwise you will see a generic error message that doesn't really point to the problem. Next, I specify the fields I need in my table. This can be done in the aAutoFields array. I also specify where the fields should be positioned. CPivFldRow specifies the field by which the rows will be subtotaled. CPivFldCol would do the same thing for the columns, but I'm not using it in the example above. The cPivFldData property specifies the field that holds the data to be displayed. Finally, the cPivFldPage property is used to define a field that can be used for filtering. In pivot tables, these are usually referred to as "pages". Note that not all fields of the aAutoFields array show up in the pivot table initially. Nevertheless, it's very important to have them, because as I mentioned above, a pivot table is meant to be an interactive tool where the user can drag fields around and drill down to see more details.

Optionally, pivot tables can use row and column totals, activated by the properties lHasColumnTotals and lHasRowTotals. I didn't use totals in the example above. Neither did I set the application title, which is important if a message box or dialog has to be displayed. This can be specified in the cAppTitle property as in most of the other Automation objects.