Board index » delphi » Findkey on Multiple field index

Findkey on Multiple field index

Carlton Comp. High School (carl...@crocus.sasknet.sk.ca) wrote:
: I have a dBASE database with the following (partial structure)
:       ACCT    Character field
:       SUB     Character field
:       ...

: It has an MDX index tag ACCT_NO which is the expression ACCT+SUB (which
: is as far as I know a valid dBASE index).  When I try to do a findkey
: using the following syntax

:       Acct_nr := '123456';
:                   {main sub}
:       if Table1.Findkey([Acct_nr]) then
:           {do something}

: it generates a "Field index out of range" exception.

: How do I do this?

Here is a technical article I wrote on this subject, available as TI2838
(a TI being a Technical Information sheet), "dBASE Expression Indexes: A
Primer." Among other topics related to using dBASE expression indexes, it
discusses this aspect in particular. Technical article follows...

Indexes for dBASE tables may be based on a the values from a single field,
unmodified, or on an expression. Index expressions, unique to dBASE
indexes, may be composed of multiple fields, modifications of field
values, or combinations of these. The expression for a dBASE expression
index is created by using dBASE functions and syntax to concatenate multi-
ple fields or to perform the modifications of field values for fields
included in the index expressions.

Two sections are included at the end of this technical article which desc-
ribe the mechanics of creating dBASE expression indexes, one applicable
to doing this in the Database Desktop utility and the other for including
this capability in Delphi applications.

Expression Indexes Based On Multiple Fields
===========================================

dBASE functions are available for use in Delphi or the Database Desktop
for the express use in index expressions, and then only in conjunction
with dBASE indexes. That is, you cannot use dBASE functions or syntax
to build an index expression for a Paradox or Local InterBase Server
(LIBS) table. Nor can dBASE functions be used in Delphi programming. They
are only available for dBASE expression indexes. The dBASE functions and
syntax that can be used for expression indexes are provided by the Borland
Database Engine (BDE) Dynamic Linked Library (DLL) file IDDBAS01.DLL.

When creating a dBASE index that is to be based on the values from two or
more fields in the table for which the index is being created, the two or
more fields are concatenated (connected together) in a manner similar to
how String type values are concatenated in Delphi syntax: the "+" oper-
ator. For example, the expression needed to create an index that orders
first on a LastName field and then on a FirstName field would be:

  LastName + FirstName

Unlike in dBASE itself, such indexes that are based on multiple fields are
limited to using just those fields in the one table. dBASE allows the
creation of indexes based on multiple fields contained in different
tables. This is possible only by having the other table open at the time
the index is created or when the table containing the index is used.

With multi-field indexes for other table types (e.g., Paradox and Inter-
Base), the multiple fields are delimited by the semi-colon (;), as in:

  LastName;FirstName

In dBASE expression indexes that concatenate multiple fields, an actual
expression is used:

  LastName + FirstName

When creating index expressions that concatenate two or more fields, all
of the fields included in the index expression must be of the same data
type. Additionally, if they are to be concatenated instead of added
together, the fields must all be of String type. For example, given two
Integer type fields, Value1 and Value2, the index expression...

  Value1 + Value2

...would not cause an error. But then, neither would it concatenate the
two field values; it would add them together. Thus, if Value1 for a given
record contained 4 and Value2 5, the resulting index node would be an
Integer value of 9, not a String concatenation "45".

If fields to be included in an expression index are not of String type,
they must be converted. Here are some dBASE functions to convert various
data types to String for purposes of creating index expressions:

  STR(<numeric value> [, <width> [, <decimal places>]])
  Converts from either Float or Numeric dBASE types to Character (String)

  DTOS(<date value>)
  Converts Date value to Character, format YYYYMMDD

  MLINE(<memo field>, <line number>)
  Extracts a single line from a memo field as a Character value

Another consideration in creating indexes based on the concatenation of
multiple field is the maximum allowable length of the index value. The
value returned by an index expression may not exceed 100 characters. This
is a limit on the length of the value returned by the expression, not on
the length of the expression itself. For example, you cannot index on the
concatenation of two fields that both have a length of 255 characters.

Expression Indexes Based On Modifications Of Field Values
=========================================================

In addition to creating indexes based on the concatenation of two or more
field values, it is also possible to construct an index that is based on
a modification of a field value. Examples of this include indexing on just
the first three characters of a String type field, on just the year and
month from a Date field, indexing on a contantenation of a String and Date
field such that the ordering of the String field is ascending but the Date
descending, and even indexing on Boolean field values.

Creating indexes that are based on modifications of field values requires
at least a working knowledge of dBASE functions and syntax -- because the
process uses dBASE, and not Delphi, functions and syntax. The dBASE func-
tion SUBSTR() extracts a substring of a String value. The Delphi equiv-
alent for this dBASE function is Copy. But, of these two functions that
serve the same purpose, only SUBSTR() may be used in dBASE index express-
ions.

To use dBASE functions in dBASE index expressions, simply include the
function wherever an index expression is called for, using the approp-
riate dBASE syntax for the function, along with a reference to the
name(s) of the field(s) used in the function. For example, an index expr-
ession based on only the last three characters of a String type field
called Code, that is 20 characters long, would be:

  RIGHT(Code, 3)

Caution should be used in constructing dBASE index expressions that modify
field values to ensure that the resulting expression would return a value
of a consistent length for every record in the table. For instance, the
dBASE TRIM() function removes the trailing blanks (ASCII decimal 32) from
a String type value. If this were used in conjunction with concatenating
two String fields where the field does not contain values of the same
length for all records, the value resulting from the expression will not
be the same for all records. Case in point, an index expression based on
the concatenation of a LastName and a FirstName field, where the TRIM()
function is applied to the LastName field:

  TRIM(LastName) + FirstName

This expression would not return values of a consistent length for all
records. If the LastName and FirstName fields contained the values...

  LastName FirstName
  -------- ---------
  Smith    Jonas
  Wesson   Nancy

...the result of applying the index expression above would be:

  SmithJonas
  WessonNancy

As can be seen, the length of the value for the first record would be 10
characters, while that for the second 11 characters. The index nodes
created for this index expression would be based on the field values for
the first record encountered. This would result in an index node 10 char-
acters long being applied to the field values for all record. In this
example, that would result in the truncation of the expression value for
the second record to "WessonNanc". This would subsequently cause searches
based on the full field value to fail.

The solution to this dilemma would be to not use the TRIM() function so
that the full length of the LastName field, including padding from the
trailing spaces, is used. In indexes that use the IIF() function to order
by one field or another, based on the evaluation of a logical expression
in the IIF(), if the two fields are of different lengths, the shorter
field would need to be padded with spaces to make it the same length as
the longer field. For example, assuming an index that uses the IIF() func-
tion to index either on a Company or a Name field, based on the contents
of Category field, and where the Company field is 40 characters long but
the Name field is 25 characters long, the Name field would need to be
padded with 15 spaces; accomplished with the dBASE function SPACE(). That
index expression would then be:

  IIF(Category = "B", Company, Name + SPACE(15))

Searches And dBASE Expression Indexes
=====================================

dBASE expression indexes are exceptions to the norm in how they are
handled by Delphi and the BDE as opposed to how multiple field indexes for
other table types are handled.

This puts such dBASE indexes into a separate class. Handling of such
indexes by Delphi and the BDE is different than those for other table
types. One of these differences is that not all index-based searching
using Delphi syntax can be used with these dBASE expression indexes. The
FindKey, FindNearest, and GotoKey methods of the TTable component cannot
be used with expression indexes. If an attempt to use FindKey is made,
this will result in the error message: "Field index out of range." If the
GotoKey method is tried, this error message may occur or the record
pointer may just not move (indicating the search value was not found).
Only the GotoNearest method may be used with expression indexes. Even
then, the GotoNearest method may not work with some index expressions.
Experimentation will be needed to see whether the GotoNearest ...

read more »

 

Re:Findkey on Multiple field index


I have a dBASE database with the following (partial structure)
        ACCT    Character field
        SUB     Character field
        ...

It has an MDX index tag ACCT_NO which is the expression ACCT+SUB (which
is as far as I know a valid dBASE index).  When I try to do a findkey
using the following syntax

        Acct_nr := '123456';
                  {main sub}
        if Table1.Findkey([Acct_nr]) then
          {do something}

it generates a "Field index out of range" exception.

How do I do this?

David Dice              carl...@crocus.sasknet.sk.ca

Re:Findkey on Multiple field index


Carlton Comp. High School (carl...@crocus.sasknet.sk.ca) wrote:
: I have a dBASE database with the following (partial structure)
:       ACCT    Character field
:       SUB     Character field
:       ...

: It has an MDX index tag ACCT_NO which is the expression ACCT+SUB (which
: is as far as I know a valid dBASE index).  When I try to do a findkey
: using the following syntax

:       Acct_nr := '123456';
:                   {main sub}
:       if Table1.Findkey([Acct_nr]) then
:           {do something}

: it generates a "Field index out of range" exception.

: How do I do this?

Hi,

I wrote a descendant of tTable that will allow you to search using your
dbase expression index.

You could do Table1.FindKeyDBF(ACCT+SUB)

There is also support for FindNearest, and ApplyRange.

The file is called MKTBL100.ZIP and is available on my Web page
http://www.tuns.ca/~klaamasm/delphi.html.

Cheers

--
===============================================
= Mark Klaamas:  klaam...@newton.ccs.tuns.ca  =
= Home page:     http://www.tuns.ca/~klaamasm =
= Electrical Engineering, TUNS, Canada        =

Other Threads