Limiting What Records Appear

From RAD Studio
Jump to: navigation, search

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.

See Also