The Samples\Sample Project 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 Stonefield Query Studio, click the Open button, and select the Sample Project directory. Alternatively, you can click the Open Sample Project link in the Getting Started section of the Start Page.

This project uses a Microsoft Access database called Northwind.MDB, which is a modified version of the NWind.MDB that comes with Microsoft Access. 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, Inventory Control, and Order Entry) and reduces the number of tables the user sees at any one time.

  • Calculated fields: The Employees table has a FullNameLF field, which displays the complete employee name. This field, which doesn't actually exist in the database, combines the Last Name and First Name fields using a formula. The Order Details table has a calculated field named Total_Price that 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. Managers has a subtable filter so only those records with the Is Manager field set to True (actually, equals 1) 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 plugins: 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 Expression property of the field to calculate the value, the Expression calls the GetCSZ plugin function to perform a more complex calculation (the format of the address depends on the country the customer is in). Order Details has a second calculated field, named Tax, that uses the GetTax plugin function to calculate the tax for the line item. A function can have as complex code as you need.

  • 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 function from the 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 Stonefield Query Studio 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 Stonefield Query Studio. 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.