Limiting What Records Appear

To restrict users to a subset of available data on a temporary basis, applications can use ranges and filters. When you apply a range or a filter, the client dataset does not display all the data in its in-memory cache. Instead, it only displays the data that meets the range or filter conditions. For more information about using filters, see Displaying and editing a subset of data using filters. For more information about ranges, see Limiting records with ranges.

With most datasets, filter strings are parsed into SQL commands that are then implemented on the database server. Because of this, the SQL dialect of the server limits what operations are used in filter strings. Client datasets implement their own filter support, which includes more operations than that of other datasets. For example, when using a client dataset, filter expressions can include string operators that return substrings, operators that parse date/time values, and much more. Client datasets also allow filters on BLOB fields or complex field types such as ADT fields and array fields.

The various operators and functions that client datasets can use in filters, along with a comparison to other datasets that support filters, is given below:

Filter support in client datasets

Operator or function	Example	Supported by other datasets	Comment
Comparisons
=	State = 'CA'	Yes
<>	State <> 'CA'	Yes
>=	DateEntered >= '1/1/1998'	Yes
<=	Total <= 100,000	Yes
>	Percentile > 50	Yes
<	Field1 < Field2	Yes
BLANK	State <> 'CA' or State = BLANK	Yes	Blank records do not appear unless explicitly included in the filter.
IS NULL	Field1 IS NULL	No
IS NOT NULL	Field1 IS NOT NULL	No
Logical operators
and	State = 'CA' and Country = 'US'	Yes
or	State = 'CA' or State = 'MA'	Yes
not	not (State = 'CA')	Yes
Arithmetic operators
+	Total + 5 > 100	Depends on driver	Applies to numbers, strings, or date (time) + number.
-	Field1 - 7 <> 10	Depends on driver	Applies to numbers, dates, or date (time) - number.
*	Discount * 100 > 20	Depends on driver	Applies to numbers only.
/	Discount > Total / 5	Depends on driver	Applies to numbers only.
String functions
Upper	Upper(Field1) = 'ALWAYS'	No
Lower	Lower(Field1 + Field2) = 'josp'	No
Substring	Substring(DateFld,8) = '1998' Substring(DateFld,1,3) = 'JAN'	No	Value goes from position of second argument to end or number of chars in third argument. First char has position 1.
Trim	Trim(Field1 + Field2) Trim(Field1, '-')	No	Removes third argument from front and back. If no third argument, trims spaces.
TrimLeft	TrimLeft(StringField) TrimLeft(Field1, '$') <> ''	No	See Trim.
TrimRight	TrimRight(StringField) TrimRight(Field1, '.') <> ''	No	See Trim.
DateTime functions
Year	Year(DateField) = 2000	No
Month	Month(DateField) <> 12	No
Day	Day(DateField) = 1	No
Hour	Hour(DateField) < 16	No
Minute	Minute(DateField) = 0	No
Second	Second(DateField) = 30	No
GetDate	GetDate - DateField > 7	No	Represents current date and time.
Date	DateField = Date(GetDate)	No	Returns the date portion of a datetime value.
Time	TimeField > Time(GetDate)	No	Returns the time portion of a datetime value.
Miscellaneous
Like	Memo LIKE '%filters%'	No	Works like SQL-92 without the ESC clause. When applied to BLOB fields, FilterOptions determines whether case is considered.
In	Day(DateField) in (1,7)	No	Works like SQL-92. Second argument is a list of values all with the same type.
*	State = 'M*'	Yes	Wildcard for partial comparisons.

When applying ranges or filters, the client dataset still stores all of its records in memory. The range or filter merely determines which records are available to controls that navigate or display data from the client dataset.

Note: When fetching data from a provider, you can also limit the data that the client dataset stores by supplying parameters to the provider. For details, see Limiting Records with Parameters.