The Samples subdirectory of the Stonefield Query program directory includes a sample project you can use to test how Stonefield Query works as an end-user ad-hoc report writer without having to create your own project first. To open this project, start the Stonefield Query Configuration Utility, click the Open button, and select the Samples directory.

This project uses a Microsoft Access database called Northwind.MDB, which is a modified version of the NWind.MDB that comes with Access and Visual Basic. Some of the features of this project are:

• Data groups: Each table is assigned to one or more data groups. Using data groups provides a logical grouping to your tables (in the sample, there are Human Resources, Order Entry, and Inventory Control groups) and reduces the number of tables the user sees at any one time.

• Calculated fields: The Employees table has Full Name (First Last) and Full Name (Last, First) fields, which display the complete employee name. These fields, which don't actually exist in the database, combine the Last Name and First Name fields using formulas. The Order Details table also has a calculated field: Total Price. This field displays the product of the Quantity and Unit Price fields. These calculated fields can be reported, filtered, or sorted just like any other field.

• Self-joined table: The Employees table has a Reports To field that contains the ID value for each employee's manager. Since the manager also has a record in the Employee table, this table can be said to be self-joined because each employee record points to another record in the same table. Self-joins are resolved using a feature of Stonefield Query called "subtables." A subtable is a copy of the original table that exists only in the data dictionary. In this case, the Managers table is a subtable of Employees. Not all fields are marked as "reportable," so only a few fields from this "table" are visible in Stonefield Query. Also, Managers has a subtable filter so only those records with the Is Manager field set to True are visible. To see an example of how this works, create a report showing some fields from the Employees table and the Full Name field from the Managers table. Behind the scenes, Stonefield Query performs a self-join on the Employees table so it retrieves the proper values from the correct records.

• Multiple relations between tables: There are two fields in the Orders table that contain the ID of a record in the Employees table: EmployeeID, which contains the ID of the employee who made the sale, and ShipEmployee, the employee who shipped the order. Thus, there are two relationships between Orders and Employees. As with self-joins, multiple relationships are handled using subtables. The ShippingEmployees table is a subtable of Employees used for employees who ship orders. So, you can create a report showing fields from Orders, Employees, and ShippingEmployees to show information about an order and the employees who sold and shipped it.

• Fields calling scripts: The CSZ field in the Customers table displays the city, state, and postal code fields combined into one string (CSZ stands for "City/State/ZIP"). Like Total Price in Order Details, CSZ is a calculated field. However, rather than having an expression in the Output Expression property of the field to calculate the value, the Output Expression calls the GetCSZ script to perform a more complex calculation (the format of the address depends on the country the customer is in). A script can have as complex code as you need, even calling methods of COM objects or Web services if necessary.

• Enumerated fields: The ShipVia field in the Orders table is an integer field. However, it doesn't contain the ID value for a record in another table. Rather, it contains a hard-coded range of values: 1 means Fedex, 2 means UPS, and 3 means Mail. Since the user likely sees a drop-down list in the data entry application showing the choices of Fedex, UPS, and Mail, they expect to see those choices in a report rather than 1, 2, or 3. Although you could call a script from the Output Expression for the ShipVia field that returns the proper value, Stonefield Query supports this type of field another way: you can define an enumerated list of values. The Special page of the Properties pane in the Configuration Utility provides a list of values and their descriptions. In the case of the ShipVia field, the values 1, 2, and 3 are defined to match the descriptions Fedex, UPS, and Mail. So, when the user selects the ShipVia field for a report, they see Fedex, UPS, and Mail rather than 1, 2, or 3, and they can also use these descriptions for filter conditions as well (for example, filtering on ShipVia equals Fedex).

• Displaying fields from related tables: The CategoryID field in the Products table contains the ID value for a record in the Categories table representing the category of a product. Rather than requiring the user to select fields from Products and the CategoryName field from Categories to show the category for each product, the CategoryID field is set up to display CategoryName from Categories. This is done in the Special page of the Configuration Utility. That way, the user simply selects the CategoryID field (which has a caption of "Category") from the Products table to display the category name for the product. Also, it's faster to directly access a field this way than asking the database engine to perform a two-table join.