Posted on by Anouar

Using the Filter in Delphi datasets

One of the best implemented features in Delphi datasets is the filter (records filtering). This built-in tool work by combining SQL filtering commands and string options like case sensitivity.

In this quick tutorial, will show you how to use the filter effectively.

To write a filtering confition, you have to set an SQL command using fields names and string patterns, associated with operators. Like in these cases :

Field_name Operator 'Pattern';

Field_name1 Operator1 'Pattern1' Association_Operator Field_name2 Operator2 'Pattern2';

In the above code, the first command is used only for one condition, while the other is used for two and more conditions.

The pattern must be string values or control’s property.

SQL operators

Filters can compare field values to literals and to constants using specific comparison and logical operators. These operators are keywords and symbols in the SQL language, dedicated for writing filtering commands.

N.B : These characters are not compatible with MS Access SQL dialect.

OperatorMeaning
<Less than
>Greater than
>=Greater than or equal to
<=Less than or equal to
=Equal to (find the exact match of)
<>Not equal to
LIKELike or similar to (use partial comparing to find the closest match of)
IS NULLHas a null value
ANDTests two statements are both True
NOTTests that the following statement is not True
ORTests that at least one of two statements is True
+Adds numbers, concatenates strings, adds numbers to date/time values (only available for some drivers)
–Subtracts numbers, subtracts dates, or subtracts a number from a date (only available for some drivers)
*Multiplies two numbers (only available for some drivers)
/Divides two numbers (only available for some drivers)
%The percent : Wildcard symbol for partial comparisons, represents zero, one, or multiple characters
(“An%” can return : An, And, Anaconda, Answer, Angel, Anthony, …).
_The underscore : Wildcard symbol for partial comparisons, represents only one single character
(“Hi_” can return : Him, His, Hit, …)
Comparison and logical operators used in a filter

Filter examples

To get all the customers named “Michel” and living in “California” (“CA”), you will write this command :

CustomerName = 'Michel' AND Residency = 'CA';

You can combine as many conditions as you want to form a complex filter, here is a long command to get all users who, for example, travelled to Spain and never travelled to France and has no children and travel only on First class :

Destination = 'Spain' AND NOT Destination = 'France' AND Children IS NULL AND Class = 'First class';

Delphi’s filter options

Delphi provide two filter options for customizing :

  • Case sensitivity : it is suitable for some situations of partial comparing using wildcard symbols, for example, “%Maria%” can return (Maria, Mariah, Mariana, Anna-Maria) but will not return (Annmaria, Amariah).
  • Partial compare : to perfom partial comparison with patterns containing wildcard symbols. It must be enabled (foNoPartialCompare set to “False”).

Apply the filter on design-time

Now as you can imagine and write the necessary filter. Let’s see how to apply it.

On design-time, you can easily apply the filter in the dataset properties from the object inspector :

  • Write the filter command as string in the Filter property.
  • Then, you enable the required filter options (Case insensitive, Partial compare)
  • Now, you enable and apply the filter by switching the Filtered property to “True”.

Apply the filter on run-time (by codes)

It is possible to apply a filter and change it as much as you want on run-time. This situation is the most used in records management softwares and applications (in retail stores, medical centers, human resources departments, …).

In this case, you have to write a routine (to be called by others actions or events) that will apply and update the filter. And consider the following notes :

  • When the dataset is already filtered, you should disable the filter before updating it.
  • If your filtering patterns are coming from string variables or Edit control’s text property (if used as search boxes in your app), you have to convert it using the “QuotedStr()” function.
  • Write your filtering command like usual Delphi strings.
  • Set the filtering options using : [foCaseInsensitive, foNoPartialCompare]

So, here is an example of the perfect code snippet for this job :

Dataset1.Filtered := False;
Dataset1.Filter := 'Name LIKE ' + QuotedStr('%' + SearchBox.Text + '%');
Dataset1.FilterOptions := [foCaseInsensitive];
Dataset1.Filtered := True;

Or, you can use the (with … do) convenience :

with DataSet1 do
begin
Filtered := False;
Filter := 'Country = ' + QuotedStr('France') + ' OR ' +
'Country = ' + QuotedStr('Germany');
FilterOptions := [foCaseInsensitive,foNoPartialCompare];
Filtered := True;
end;

And here, an example combining the filter with regular Object Pascal conditions :

with DataSet1 do
begin
Filtered := False;
if Filter = ''
   then Filter := 'Name = ' + QuotedStr(AName)
   else Filter := Filter + ' AND ' + 'Country = ' + QuotedStr(ACountry);
FilterOptions := [foCaseInsensitive];
Filtered := True;
end;

See also

Leave a Reply