Limiting What Records Appear
Go Up to Working with Data Using a Client Dataset
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 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.