Adding a Custom Query

You can create a custom, or “named” query that can be reused by multiple pages.  This saves you time since you don’t have to recreate the same query for each separate use.  The concept of a custom query is very similar to the concept of a “database view”, except that the custom query is defined in Iron Speed Designer and not in the underlying database to which you may not have access.

Custom queries are available for use throughout Iron Speed Designer, including:

• The Application Wizard for creating Add Record, Edit Record, Show Record and Show Table pages based on the custom query.

• As data sources for fields, records and tables.

The custom queries are defined using a dialog similar to the Add WHERE Clause dialog, accessed from the Query Wizard.  The queries are modified in one central place – the Database folder in Iron Speed Designer – allowing you to centrally manage and update them.

Each custom query generates an appropriate set of code classes for the query.  These classes will be derived from the VirtualRecord and VirtualTable classes.  The benefit of using queries is that there will be fewer generated classes related to the virtual tables, and more importantly, their names correspond to the name you provide, making them more understandable.

When a query is used as a data source, editing the data is not permitted unless you add a Virtual Primary Key for the query.  No editing is allowed regardless of the number of levels below a query without the Virtual Primary Key.  For example, if a query was used for a Table, and a record is embedded within another record in the table row, no editing is allowed for either of the records because they were derived from a query that contains no Virtual Primary Key.

Query Name Step

Query names may contain alphanumeric characters, spaces, and underscores. 

Query names and alias names have these additional restrictions:

• They may not include asterisks, question marks, or dashes.

• They may not start with a digit.

• They may not end with a period (no trailing period).

• Query names may not be a reserved word, including any of the following:

AUX

CLOCK$

COM1

COM2

COM3

COM4

CON

LPT1

LPT2

LPT3

NUL

PRN

TABLE

FROM step

Table Name Aliases

When creating queries it is sometimes necessary to join a table with itself.  For example, the Northwind.Employees table has a ReportsTo field that is a foreign key to the Employees table (i.e. it references another Employee).  Suppose you wanted to create a query that shows an employee's first and last name as well as the first and last name of the person they report to.  In SQL your query would look something like the following:

SELECT Employees.FirstName, Employees.LastName, EmployeesReportsTo.FirstName, EmployeesReportsTo.LastName

FROM Employees, Employees EmployeesReportsTo

WHERE Employees.ReportsTo = EmployeesReportsTo.EmployeeID

"EmployeesReportsTo" is an alias for the Employees table and is used to avoid ambiguity.  When creating queries using Iron Speed Designer, you can create aliases such as EmployeeReportsTo using the "New Alias" button.  You can then select this alias in the same way you select a table when specifying join criteria.

See Also

Using Tables and Views in WHERE Clauses

Using Constant Values in WHERE Clauses

Using URL Parameters in WHERE Clauses

Using the Logged-In User ID in WHERE Clauses

Calling Custom Code Functions in WHERE Clauses

Adding a Custom Query