The following sample formulas can be used as templates to help you create your own selection formulas using the Record Selection Formula Editor. These examples illustrate different selections that you could do, not necessarily the best selections.
Selecting Records using Character Strings
{file.FIELD} startswith "C"
Selects those records in which the value in the {file.FIELD} field begins with the character "C" (includes values like CyclePath, Corp. and Cyclist's Trail Co.; excludes values like Bob's Bikes Ltd. and Feel Great Bikes, Inc.).
not ({file.FIELD} startswith "C")
Selects those records in which the value in the {file.FIELD} field does not begin with the character "C" (includes values like Bob's Bikes Ltd. and Feel Great Bikes, Inc.; excludes values like CyclePath, Corp. and Cyclist's Trail Co.).
"999" in {file.FIELD}[3 to 5]
Selects those records in which the 3rd through 5th digits of the {file.FIELD} field is equal to "999" (includes values like 10999, 70999, and 00999; excludes values like 99901 and 19990).
"Cycle" in {file.FIELD}
Selects those records in which the value in the {file.FIELD} field contains the string "Cycle" (includes values such as CyclePath Corp. and CycleSporin, Inc.; excludes values like Cyclist's Trail Co. and Feel Great Bikes, Inc.).
Selecting Records Using Numbers
{file.FIELD} > 99999
Selects those records in which the value in the {file.FIELD} field is greater than 99999.
{file.FIELD} < 99999
Selects those records in which the value in the {file.FIELD} field is less than 99999.
{file.FIELD} > 11111 and {file.FIELD} < 99999
Selects those records in which the value in the {file.FIELD} field is greater than 11111 but less than 99999 (neither 11111 or 99999 is included in the range of values).
{file.FIELD} >= 11111 and {file.FIELD} <= 99999
Selects those records in which the value in the {file.FIELD} field is greater than 11111 but less than 99999 (both 11111 and 99999 are included in the range of values).
Selecting Records Using Dates
The Month, Day, and Year functions can all be used in examples like the following:
Year ({file.DATE}) < 1999
Selects those records in which the year found in the {file.DATE} field is earlier than 1999.
Year ({file.DATE}) > 1992 and Year ({file.DATE}) < 1996
Selects those records in which the year found in the {file.DATE} field falls between 1992 and 1996 (1992 and 1996 not included).
Year({file.DATE}) >= 1992 and Year({file.DATE}) <= 1996
Selects those records in which the year found in the {file.DATE} field falls between 1992 and 1996 (1992 and 1996 are included).
Month({file.DATE}) in 1 to 4
Selects those records in which the month found in the {file.DATE} field is one of the first four months of the year (includes January, February, March, and April).
Month({file.DATE}) in [1,4]
Selects those records in which the month found in the {file.DATE} field is the first or fourth month of the year (includes January and April, excludes February and March).
Selecting Records Using Preset Data Ranges
The preset date ranges can be used to create selection formulas similar to these:
{file.DATE} in LastFullMonth
Selects those records in which the date found in the {file.DATE} field falls within the last full month. (If the month is May, this selects all records with an April date.)
not({file.DATE} in LastFullMonth)
Selects all records except those in which the date found in the {file.DATE} field falls within the last full month (if the month is May, this selects all records except those with an April date).
{file.DATE} < CurrentDate
Selects all records in which the date found in the {file.DATE} field falls before today's date.
Selecting Records Using Date/Number/Character Combinations
"C" in {file.FIELD}[1] and Month({file.DATE}) in [1,4]
Selects those records in which the value in the {file.FIELD} field begins with "C" and the month is either January or April. For example, if this kind of formula was used with an order database, you could be asking for a report showing all customers whose names begin with "C" and who placed orders in January or in April.
"AOK" in {file.HISTORY}[3 to 5] and {file.OPENCRED} >= 5000
Selects those records in which the {file.HISTORY} field shows the characters "AOK" as the 3, 4, and 5 characters and the {file.OPENCRED} field (the amount of available credit) is at least 5000.
These templates can be used as they are (with your own data), or they can be combined to create complex formulas.
Troubleshooting Record Selection Formulas | Pushing Down Record Selection to the Database Server