Strategies for writing efficient record selection formulas

Consider the following points when creating a record selection formula:

Consideration #1

Any record selection formula that you can generate completely with the Select Expert, without writing pieces of the formula yourself, can be pushed down.

This case actually follows from the points below. However, you can write more types of record selection formulas using the tips below than is possible with the Select Expert. To do this, you need to edit the record selection formula directly with the Formula Editor or from within the large text window in the Select Expert.

Consideration #2

Any selection formula that is of the form: DataBaseField SupportedOperator ConstantOrParameterExpression can be pushed down.

Of course DataBaseField is just a database field. SupportedOperator is any of =, <>, <, <=, >, >=, StartsWith, Like or In.

ConstantOrParameterExpression is any expression that involves constant values, operators, functions and parameter fields. It cannot involve variables, control structures and fields other than parameter fields. By their very definition, constant and parameter expressions can be evaluated without accessing the database.

Note:    A constant or parameter expression can evaluate to a simple value, range value, array value or an array of range values. Here are some examples of such expressions:

{?number parameter} - 3
Year ({?run date})
CurrentDate + 5
DateDiff ("q", CurrentDate, CDate("Jan 1, 1996"))
Month (Maximum ({?date range parameter}) + 15)
["Canada", "Mexico", "USA", {?enter a country}]
1000 To 5000
[5000 To 10000, 20000 To 30000, 50000 To 60000]

A complete example:

{Orders.Order Date} >= CurrentDate - 3

The program can also push down an expression that just contains a Boolean field (without the operator and constant parts).

{Orders.Shipped}
Not {Orders.Shipped}

Consideration #3

IsNull (DataBaseField) can be pushed down.

Consideration #4

SqlExpression SupportedOperator ConstantOrParameterExpression can be
pushed down.

For example, the selection formula {@ExtendedPrice} > 1000 cannot be pushed down if {@ExtendedPrice} is Quantity * Price. However, if the formula @ExtendedPrice is replaced with the equivalent SQL Expression, then this will be pushed down.

Consideration #5

Expressions following the above considerations, separated by "And"s and "Or"s. You can have several of each, and use parentheses to give priority. You can also use "Not". For example:

{Orders.Order ID} < Minimum({?number range}) Or
{Orders.Order Amount} >= 1000

(IsNull({Customer.Region}) Or 
 {Customer.Region} = "BC") And
{Customer.Last Year's Sales} > 2000