This tutorial leads you through the process of creating a version of Stonefield Query for the Testdata database that comes with Visual FoxPro.
- Start the Stonefield Query Configuration Utility and create a new project by clicking the New Stonefield Query Project button (
). - Add the Testdata database that comes with Visual FoxPro to the data dictionary. Start by selecting the "Database" node in the TreeView and clicking the Create button (
). Choose Visual FoxPro, ensure Database container is selected, and click the button beside the Database Location textbox and select Testdata.dbc from the Samples\Data subdirectory of the Visual FoxPro home directory. Click the OK button to begin the discovery process.
- If the discovery process was successful, you'll see the Testdata database under the "Databases" node in the TreeView. Click the + in front of the Testdata and Tables nodes to see the tables in this database.
- Click the Orditems table and change its Caption to "Order Items."
- Because the extended price for an order item is a derived value (unit price multiplied by quantity), it isn't stored in the database. However, the user may want to query on that, so let's create a calculated field for it. Expand the Orditems table, expand the Fields node, and then click the Create button to create a new field. Enter "TOTAL_PRICE" for the name, set the data type to Currency, enter "Total Price" for Caption, set Format to "$," and enter "999,999.99" for Picture.
Select the Calc page and enter the following for Output Expression:
Unit_Price * Quantity
Turn off the Stonefield Query Expression option, since this expression can be evaluated by Visual FoxPro.
Click "newfield" in the TreeView to save this change. You'll see the name in the TreeView change to "TOTAL_PRICE."
- Click the + in front of the Orders table, then click the + in front of the Fields node, and click the Ship_Via field. This integer field is really an enumerated field; let's assume that 1 means Fedex, 2 means UPS, and 3 means Mail. To define ShipVia as an enumerated field to Stonefield Query, select the Special page in the right side of the Configuration Utility and check the Enumerated Values option. Then, click the Add button to add a new value to the list, and in the grid, enter "1" in the Value column and "Fedex" in the Description column. Do the same for 2 (UPS) and 3 (Mail).
- Expand the Configuration node and click Application. Select the Application Name property and enter "Stonefield Query for Testdata" as the value.
- Click the Launch Stonefield Query button (
). - Since this is the first time Stonefield Query for Testdata is run, the Setup dialog appears. Leave the Location of Stonefield Query files option at the default (the same directory where you created the Stonefield Query project) and click the Finish button.
- Create a new report with the following fields: Company from Customer; Order ID, Order Date, and Ship Via from Orders; Quantity, Unit Price, and Total Price from Order Items; and Product Name from Products. When you preview this report, you'll see that ShipVia is displayed as Fedex, UPS, or Mail rather than 1, 2, or 3 and that the Total Price for each item is shown.