| Tutorials > Tutorial 4 - C1Query Schema Features |
In the previous tutorials, you did not use most of the features of C1Query schema, you only imported a schema from database and used it as is.
In this tutorial you will learn how to use C1Query Schema Designer to customize user queries. You will create a custom folder structure to hide the complexities of database structure from the users. You will also explore some more advanced capabilities of C1Query schema views, such as multi-table views and calculated fields. Complete the following steps:
Repeat steps 1–6 of Tutorial 3 - Query Result Fields in C1QueryFrame Control.
Press the right mouse button over the C1Query1 control and select Schema Designer from the context menu. ComponentOne Query Schema Designer will open. In Schema Designer, connect to the database, pressing the Connect to database button on the Tables palette, or selecting Connect to database from the Schema menu. When you press OK in the Connect to database dialog box, the Tables palette is populated with the list of all database tables and their fields.
Remove all folders from the Folders palette except three: Customers, Order Details and Orders. To remove a folder, select it and choose Remove from the context menu or press the DELETE key.
Rename the Order Details folder to Order Items. To rename an object, select it and choose Rename from the context menu or click on it twice with an interval.
Expand the Order Items folder and change the order of fields in it using drag and drop (no specific order is required).
Add the Products table to the Order Details view, making it a multi-table view consisting of two tables: Order Details and Products. To add a table to a view, drag the table from the Tables palette and drop it on the view in the Views palette.
Open the View Designer window by double-clicking the Order Details view in the Views palette. In the View Designer, select the Products table (added to the view on step 6). Here you must join the Products table to the Order Details table (main table). Press the New Condition button that will open a New join condition dialog box. Select OrderDetails.ProductID in the left combo box and Products.ProductID in the right combo box. Press OK. The join condition will be added to the list box:
OrderDetails.ProductID = Products.ProductID
Add a calculated field ExtendedPrice to the Order Details view. Use the Add button on the top of the View Designer. For the newly added calculated field, specify Name: ExtendedPrice, Type: Currency, Expression:
[Order Details].[UnitPrice] * [Order Details].[Quantity] * (1 - [Order Details].[Discount])
Add two folder fields to the Order Items folder: ProductName and ExtendedPrice. To add the fields, open the folder for editing by double-clicking the folder in the Folders palette, and add the fields using the Add button on the top of the Folder Designer. Select the fields from the menu that opens when you press the Add button. The menu shows all view fields in submenus organized by view name. The ProductName field is in the Products submenu, the ExtendedPrice field is in the Order Details submenu. Selecting a view field adds a new folder field to the folder. The new folder field represents the selected view field.
Field menu presented to the end user show the folders and fields as they were defined in the schema, not database tables and fields. This allows you to isolate your users from database structure, to present them with choices consistent with their business view of the data.
You can query ProductName and ExtendedPrice fields from Order Items folder, although they don’t belong to the Order Details table. Specify conditions for these two fields in the Conditions tab and press the Show SQL button to see the SQL statement generated by C1Query. It contains the calculated field expression and the join between Products and Order Details that you specified in the Order Details view.