The Query Wizard WHERE Step

You can join to other database tables and views and constrain the displayed data based on values in those tables.

The Query Wizard’s WHERE step.

Joining two tables via the Add WHERE Clause dialog.

You can also “hop” from one level (table) to the next record connected by the original record. For example, if you have an Order record containing a Customer ID, you can display the Customer’s Last Name by selecting the Foreign Key relationship of “Orders to Customers”. This is only applicable from a parent to a child, not from a child to a parent.

You can display data from fields in several tables that are joined via their foreign key relationships.

In this example, the page displays fields from both the Orders and Customers tables by using the foreign key relationship between Orders.Customer and the Customers table.

Foreign key relationships can be:

Explicit. Explicit foreign key relationships are defined in the database itself. Iron Speed Designer automatically detects these foreign key relationships and uses them in your application. Developers or DBAs typically enforce foreign key constraints within the database.
Implicit (virtual). Implicit foreign key relationships are not defined in the database. Instead, you identify these implicit foreign key relationships to Iron Speed Designer via the Virtual Foreign Key facility (Databases, New Virtual Foreign Key…). For more details on adding virtual foreign key relationships to your application, see Adding Virtual Foreign Key Relationships.

Using Formulas in WHERE Clauses

In many cases, it’s useful to constrain the result set to records with a particular ID value, constant name, or other value. You can even constrain the selection criteria using data passed into the page from the page’s URL, a common way of requesting individual records to be displayed. You can do all this using formulas.

For example, to create a table showing only the Western Region’s orders, you may create selection criteria where one constraint is that the sales territory is “Western Region”, e.g.:

Order.SalesTerritory is equal to Western Region

Or, you might constrain the data to orders larger than $50,000, e.g.:

Order.TotalAmount is greater than 50000

The Constant selection criteria is most frequently used with numerical and string values, though dates and other field types can be used as well. This value is specified in string format and converted to the data type of the field selected on the left hand side.

You can combine Constant criteria with other selection criteria to create more refined data sets, e.g.:

Order.SalesTerritory is equal to Western Region AND

Order.TotalAmount is greater than 50000 AND

Order.SalesPersonID is equal to LoggedInUserID()

This example selects order data from the Western Region over $50,000 for the currently logged in sales person, giving the sales person a custom report showing only his or her large orders.

Using Geo Proximity in WHERE Clauses

A Geo-Proximity where clause can be added only if the underlying table has columns with Validation Type (in Databases) as Latitude and Longitude.

In order to add a Geo-Proximity where clause, click on the “Add Geo-Proximity Clause…” button. It opens the “Add Geographic Proximity Clause” dialog.

In this dialog the Origin can be specified (e.g. GetBrowserLocation()) as well as the Radius. Applying the Geo-Proximity where clause produces query results which satisfy the query.

Using ‘Display Foreign Key As’ values as constants

You can enter foreign key values as constants in addition to numeric values. However, certain caveats apply:

If the field specified in the left-hand side of the query clause is a foreign key field with a “Display Foreign Key As” enabled, enter the text version of the constant. For example, enter “Western Region” instead of “25”.
If the field specified in the left-hand side of the query clause is a foreign key field without “Display Foreign Key As” enabled, enter the key value itself. For example, enter “25” instead of “Western Region”.

See Setting Database Field Properties for details.

Using URL Parameters in WHERE Clauses

You can constrain the selection criteria using data passed into the page from the page’s URL. This is a very common way of requesting individual records to be displayed.

The URL contains the primary key value of a record in the database. Your application parses the URL parameter, extracts the primary key value, and performs the record lookup in the database.

In this example, a query string called “ID” is used in the URL to convey a primary key value of “Bob Pinkston”. Bob Pinkston’s record is retrieved from the Customers table and displayed by a series of ASP.NET controls in the web page.

Use the Add WHERE Clause dialog to select URL parameters to constrain the selection criteria by a parameter passed into the control from the page’s URL. This is a very common way of requesting individual records to be displayed.

At application run-time, if a URL parameter is specified then the data is filtered accordingly. If no URL parameter is specified, then all data is shown.

Using the Logged-In User ID in WHERE Clauses

In many cases, it’s useful to constrain the result set to records pertaining to the currently logged in user. For example, in a customer portal application, you may want to display the customer’s recent order history. Obviously you should display only orders relevant to a particular customer; it would be inappropriate to show every customer’s orders to everyone. Similarly, you may wish to constrain a sales person’s view to only orders from their accounts.

Constrain the selection criteria to retrieve data for the currently logged in user.

The best way to create such selection criteria is by using the built-in Logged-in User ID function. To create a table showing only one customer’s orders, you may create selection criteria where one constraint is that the CustomerID must match the Logged-in User ID e.g.:

Order.CustomerID is equal to USERID()

Or, you might constrain the data to orders for each sales person, e.g.:

Order.SalesRepID is equal to USERID()

The currently logged in user ID value provided comes from the application security code created by Iron Speed Designer. This code maintains the user ID and other state information throughout your application. More information on the application security mechanism created by Iron Speed Designer is in “Securing Your Web Pages with Role-Based Security”.

Using LIKE and NOT LIKE clauses as operator

Starting with version 11.0 of Iron Speed Designer LIKE and NOT LIKE operators are treated differently than Contains. Before they we actually converted to Contains and Not Contains. Now their content is copied unchanged to the final where clause with exception of being surrounded with single quotes and certain encodings. You can specify regular expressions for SQL server database. For example you can specify
“%ea%” for the city name and it will result in Where clause of LIKE ‘%ea%’.
For Sql Server you can specify for example “[^0-9][^0-9][^0-9]” for Country name and get all three letter countries such as USA, OAE, etc.

Using custom functions in formulas

In addition to the standard (built-in) functions, you can define and call custom functions in formulasFor web applications, the file is located at:

\Data Access Layer\Shared\BaseFormulaUtils.cs (.vb)

For web sites, the file is located at:

\App_Code\Shared\BaseFormulaUtils.cs (.vb)

Or both types can use FormulaUtils which can be found at

\Shared\FormulaUtils.cs (.vb)

Once you specify your custom function in FormulaUtils or BaseFormulaUtils, you can invoke the function by using it in the Formula tab or Add WHERE Clause dialog, e.g.:

= YourFunction()

where “YourFunction” is the name of your function.