Tip: Indexing & Searching With dBASE Logical Fields 
Author Message
 Tip: Indexing & Searching With dBASE Logical Fields

With dBASE tables, indexes cannot be constructed based on a Boolean field
or such that the index expression includes a Boolean field. This precludes
index searches against Boolean fields as well as filtering via ranges.
However, it is possible to perform these operations if the index expr-
ession only indirectly references the Boolean field, as through a dBASE
expression using the dBASE IIF() function. (While the IIF() function is
part of the dBASE programming language and not that for Delphi, it is
included in the dBASE BDE DLL, and so can be used in dBASE indexes -- and
only dBASE indexes -- by any BDE-using application capable of creating
dBASE tables and indexes.)

The IIF() function is fundamentally an if..else construct, in a single
function. The IIF() function takes three areguments. The first is an expr-
ession that will evaluate to a Boolean value. This can literally be the
values True or False, or it can be a logical comparison that would either
be true or false. The second parameter is of type String, and is the value
the function will return if the logical expression evaluates to True. The
third, the value returned if the logical expression is False.

This dBASE function can then be used in an index expression to provide the
index with a String type value, one value if the field contains a True
and another for a False in the field. So if the index if to order the
records with True values first, followed by those with False values, the
True and False returns values for the IIF() function could be "A" and "Z",
respectively. The index expression would then be:

  IIF(LogicalFld, "A", "Z")

When simply displaying data, this would cause all of the records with
True values in the field LogicalFld to appear first. All of the records
with False values would follow.

While the index would be based on being one of two values ("A" or "Z"),
filtering could be accomplished by using the AsString property of the
field to convert the Boolean value to String (for purposes of this
filtering operation only), and a range could be set using the String
values "True" or "False" for the respective Boolean value equivalents.
For example, to display only those records where a Boolean type field
called LogiField contains True values:

  procedure TForm1.Button1Click(Sender: TObject);
  begin
    with Table1 do begin
      SetRangeStart;
      FieldByName('LogiField').AsString := 'True';
      SetRangeEnd;
      FieldByName('LogiField').AsString := 'True';
      ApplyRange;
    end;
  end;

--
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
_/ Steve Koterski               _/   The opinions expressed here are    _/

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/



Tue, 06 Jan 1998 03:00:00 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. Tip: dBASE Expression Indexes: A Primer

2. BDE, dBASE, SQL, and Logical fields (boolean)

3. Dbase Expression Index DTOS Search

4. Problem with search in dbase index files

5. Pos Type string search for dBase field

6. dbase III+ & index manipulation (Newbie question)

7. Index creation errors for DBase & Paradox

8. Dbase Table Level Changed when adding single field index

9. Index over 4 fields with DBASE

10. Field index out of range on dBase file

11. Problems with autoincremental field & Dbase

12. Index Logical?

 

 
Powered by phpBB® Forum Software