Filter Table Control Contents

A table control can be filtered to display only a subset of values by adding a WHERE clause in the Query Wizard.

Simple WHERE clause example

Step 1:  In the Page Layout Spreadsheet, select a table control.

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

Step 3:  In the Query Wizard, add a new WHERE clause containing a formula that restricts the contents.  For example, you can show only orders with a UnitPrice greater than $20 using the “is greater than” operator coupled with this formula:

 =20

The resulting WHERE clause is:

Order Details.UnitPrice is greater than $20

Step 4:  Build and run your application.

Using calculation functions in WHERE clauses

Table control functions can be very useful in formulas. For example, you can display only records which have a UnitPrice lesser than average of all the UnitPrice values with this WHERE clause coupled with the “is less than” operator.

= ProductsTableControl.Average("UnitPrice")

See Also

Common Formula Examples