Customizing Database Queries with the Query Wizard

Go to:

Page Layout Spreadsheet, Data Sources tab, Edit…

The SQL query determines which records are selected from the database for display or editing.  Queries provide a built-in filtering mechanism so you can control how much data is displayed in a Table control.  In more detailed terms, the query is the basis of the underlying SQL query’s “WHERE” clause, and for experienced programmers, this is perhaps the best way to think about queries.  Depending on the query you specify, the result set can be as simple as single record from a single database table or as complex as multiple rows from a multi-table join.

One or more records can be selected as a result of the specified query.  In the case of a Table control, all records within the selected tables are available for display in the Table control.  In the case of a Record control, only the first record returned is actually displayed, since the associated controls are capable of displaying only one field at a time.

Creating multi-table joins with the Query Wizard

One of the more powerful features in Iron Speed Designer is the ability to create multi-table joins.  A multi-table join is the result of a query that joins together two or more database tables using a set of joining conditions.  This is useful when you need to display data fields from several related tables.

Additional filtering criteria may be applied to the multi-table join.  For example, you may display a table containing information from the Orders and Customers table, showing only orders greater than $1,000.  Or, you might display information only pertaining to the currently signed in user, allowing them to see only their own information.

Iron Speed Designer automatically creates the SQL query required to join your selected tables together.  Any number of tables may be included in the join and any number of joining conditions may be applied to filter the result set.  Iron Speed Designer provides a wide variety of ways to create and customize SQL queries.

Use the Add WHERE Clause dialog to add additional filter criteria.

The following example shows two selection criteria that join three tables: Order, OrderDetails, and Customer.  The resulting data set will include rows matching only these selection criteria and will include fields from all three tables.

Order.OrderID is equal to OrderDetails.OrderID AND

Order.CustomerID is equal to Customer.CustomerID

Note:  You can select a table that not been previously connected through another WHERE clause and then add another clause connecting this table later.  As such, you can specify an invalid selection criterion or one that returns no records.

Step 1:  In Design Mode, select the table or record panel you wish to configure and open the Data Sources tab.

Step 2:  In the Data Sources tab, select the appropriate query and open the Query Wizard (Edit...).

Step 3:  In the FROM step of the Query Wizard, select the tables you wish to join.

Step 4:  In the WHERE step, click “Add WHERE Clause…” and enter the additional clause(s) as necessary.

Step 5:  In the ORDER BY step, select the default sort order for the initial table data display.

Step 6:  Click ‘Finish’ to close the Query Wizard and update the query.

Creating multi-table joins with database views

An alternative to the Query Wizard is creating a database view.  Creating a database view within your database is the best way to proceed if you have a truly custom query, such as queries containing:

Database views are easy to use and make use of your databases underlying power and ability while letting Iron Speed Designer focus on application generation.

Step 1:  In your database, create a database view containing whatever SQL you wish.  This can be a simple or complex query – anything you want within the realm of the SQL language.

Step 2: Use the Application Wizard in Iron Speed Designer to create a set of pages for that database view.

Creating custom queries in Iron Speed Designer

Custom queries are a good alternative if, for some reason, you can’t create a database view in your application and the other mechanisms outlined here won’t work for you.  Custom queries are useful if you plan to use the same query on multiple pages and want to define it just once.  See Adding a Custom Query for details.

See Also

The Query Wizard JOIN Step

The Query Wizard SELECT Step

The Query Wizard TOP Step

The Query Wizard WHERE Step

The Query Wizard ORDER BY Step

The Query Wizard FINISH Step

Adding a Custom Query