Query Wizard

Go to:

Layout Editor, Property Sheet, Queries section, 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.

All data sources can be modified using the Query Wizard by editing the Query in the Property Sheet.

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 database views

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, edit, or compare data fields from several related tables. Creating a database view within your database is one way of creating a multi-table join. It 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 multi-table joins with Iron Speed Designer

One of the more powerful features in Iron Speed Designer is the ability to create multi-table joins. There are several ways to create joins using Iron Speed Designer:

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.

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 the Layout Editor, select the query in the Property panel you wish to configure.

Step 2:  In the Property Sheet, select the appropriate query and open the Query Wizard (Edit...).

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

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

 

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

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.