We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now


dBASE subset filter expression indexes,creating

Medium Priority
Last Modified: 2013-11-24
I need to be able to create dBASE IV "subset filter" expression indexes on-the-fly in my Delphi 1 app.  Currently, I *DO* know how to create a simple expression index:
Table1.addindex('INDEXNAME', 'FIELD1 + FIELD2', [ixexpression]);

In DataBase Desktop, it is possible to add a boolean subset/filter expression, such as FIELD3="A VALUE".
How do I create such an index programmatically.  I have been told that a direct call to the BDE is required.

Thank you very much.

David M. Goncalves
Watch Question

Could you give me an example of what exactly do you want to get?
I guess it has nothing to do with table filtering, hasn't it?


In response to javiertb's request...  Thank you for your interest.  Yes it has *something* to do with table filtering, but I'm not sure what that means to you.  

I believe I need to use the BDE function dbiAddIndex and specify the correct IDXDesc structure using bSubset=True and set the szKeyCond, but I am so rushed doing other things I don't have time to figure it out.  Ideally you would be able to show me how to use a similar function DbiGetIndexDesc to steal the structure from an existing table.

My application serves to store drug testing records in dBASE IV tables.  There are several different indexes for browsing the data.  Each index is a compound index using several fields: PART_ID, SERIAL, and DRUGORALC.  PART_ID is a unique participant id.  SERIAL is the serial number of the test, and DRUGORALC is always "D" for drugs or "A" for Alcohol.

PART_ID + SERIAL + DRUGORALC is always a unique key. In several indexes, DRUGORALC is evaluated in a subset condition (filter) index to require either DRUGORALC="D" or DRUGORALC="A".

I realize that there are other ways to perform this filtering, but such changes would be far-reaching in my established code.  I need to be able to create these indexes, and others like them, programmatically.

Following is an excerpt from the Database Desktop help file, describing subset condition (filter) indexes:

"A subset condition expression (also called a filter) is an expression that evaluates to true or false. Database Desktop creates for a dBASE table an index that points only to values that meet the filter's requirements. For example, if you create the subset condition expression State=CA, you tell Database Desktop to create an index on those values in the State field that match the value CA.
You create a subset condition expression on a dBASE table from the Create Table dialog box or the Restructure Table dialog box. Choose Define to display the dBASE Define Index dialog box.

Enter a subset condition expression in the Subset Condition (filter) Expression edit box.
To create a subset condition expression,

1.      Choose Subset Condition (filter) Expression. The button name changes to Index Field and the insertion point moves to the Subset Condition (filter) Expression edit box.
2.      Enter the expression you want.
3.      Choose Index Field to return to the field list.

Copyright 1993-1995 Borland International."
If you want to add a subset/filter programatically, just set Table1.Filter:='FIELD3 = <a value>'
i.e. if value is a number Filter:='FIELD3='+ inttostr(value);
     if value is string Filter:='FIELD3='+#39+value+#39;

Anyway, here you have an example of using DBiAddIndex and dbiGetIndexDesc

*******  dbiAddIndex ***************

procedure TForm1.Button1Click(Sender: TObject);
 var hCursor:hdbiCur;
     Baza: Pchar;
{this is building index descriptor}
 Witn Desc do
  {this is indexfile name: Full path and extention neccessary}
  iIndexId:=0;{ allways 0 }
  szTagName:='NALDOH';{ This is indexname }
  szFormat:='BTREE';{ I set always 'BTREE' and this work }
  bPrimary:=false;{ always false }
  bUnique:=false or true;{ Depend on nessesery  }
     {If DBaseTable has a Unique index, after deleting
     Record, impossible insert record into Dataset with same Key      value, because this record marked in file as deleted. To         resolv this problem I called in method AfterDelete function      DBIPackTable, It Packed corresponding file. It`s seems to me      that`s wrong- Borland IDAPI driver work in this case no          valid Therefore I never set unique index }
  bDescending:=false;{ True, for descending index }
  bMaintained:=true;{ True,  }
  bSubset:=false;{ I set false }
  bExpIdx:=true;{ True, if expression index, for multiple                   index-always true}
  iCost:=0;{ always 0 }
  iFldsInKey:=0;{0-for expression index 1-for not expression  }
  iKeyLen:=14;{ Length of index expression (without 0 byte see                 szkeyexp)}
              {for not expression index always = 12 }
  bOutofDate:=false;{  i set always false }
  iKeyExpType:=513;{ 513 for Expression index; 529 for not                      expression }
  aiKeyFld:=[0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0];{for expression              index always [0,0,0..0]}
            {for not expression [N,0,0,0..0], where N- number of              index field}
  szKeyExp:='str(nal,2)+str(doh,12)';{ Key expression }
  szKeyCond:='';{ Subset condition . I set always =''; }
  bCaseInsensitive:=false; { True, if case insensitive index. I                              set always false}
  iBlockSize:=4096;{ Block size in bytes.}
 {This value defined expirience for example to use function   dbiGetindexdesc}
  iRestrNum:=1;{ Restructure number: 1,2,3... }
{this is getting Database handle}
DBase:=Session.FindDatabase('zarplata');{other way possible}
{this is receiving table cursor}
DBREZ:=DbiGetCursorForTable(DBase.Handle,Baza,'STANDARD',hCursor);{other way possible}
If DbREz<>0 then
Application.Messagebox('Getting cursor error','Error',mb_OK);
{this is creating new index}
If DbREz<>0 then
Application.Messagebox('Invalid index descriptor','Error',mb_OK);

Note: Corresponding Object TTable must be exclusivly open.

******* dbiGetIndexDesc

function GetIndexDesc(T: TTable; IndexName: String): IDXDesc;
  hNewCur: hDbiCur;
  iIndexId: LongInt;
  InfoStr: string;
  pInfoStr: array[0..100] of char;
  Check(DbiCloneCursor(T.Handle, False, False, hNewCur));
    iIndexId:= 1;
    Check(DbiSwitchToIndex(hNewCur, PChar(IndexName), Nil, iIndexId, FALSE));
    Check(DbiGetIndexDesc(hNewCur, 0, Result)); //'0' specifies the active index


Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts


So sorry, but I have encountered several problems attempting to implement your solution:

1) I receive an (Error 44 : field identifier expected) when trying to set Table1.filtered:=true or Table1.filter:='DRUGORALC="A"'.  I suspect that the Filtered and Filter properties of the TTable component you suggested do not apply to my Delphi 1 VCL, since these properties are not in my help file.  That's ok, and I'm really more interested in my original question:

2) In an attempt to replicate your usage of the BDE engine calls, I have created a new project, added DBIProcs and DBITypes to my Uses clause, pasted in the sample you provided, and substituted information about my table.
When trying to set
or szFormat:='BTREE';
or aiKeyFld:=[0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0];
szKeyExp:='SERIAL + PART_ID';{ Key expression }
szKeyCond:='DRUGORALC="C"'; {subset filter expression},
I receive (Error 26 : Type mismatch).  Each time I received the error, I would remark out the line, until I received another compiler error.  

I believe that you have used this function successfully, but for some reason, I cannot use it as-is. Perhaps there is an environmental parameter that I need to change...?  I have searched out the buffer typedefs, DBITBLNAME, DBINAME, and DBIKEY, but the brief descriptions are not too meaninful to me at this point.

Thank you for your time; I'm sure we can work this out shortly...

I'll try to see what is the problem you have due to.

You have to include BDE and DB units in your uses clause.
First of all I'd recommend you to take a look at BDE help file where you'll find some delphi examples on using DbiAddIndex. You were right about Filter property, it's a delphi 2 feature (sorry).
Anyway, here you have specific help on those errors you got:


The following table describes how to name Paradox indexes:

Index ID Param      Non-composite index      Composite index

szName               Same as field name      Can be any legal name not                                         used as a field name;                                         must be unique
iIndexID      Same as field number    Valid ID (256 to 511)                   (1 to 255)              output only;not specified                                         while index  is created
DBINAME      Specifies the index tag name. Supported for dBASE only.

DBINAME      Currently, for information only. Describes the physical index format type (for example, BTREE or HASH).

DBIKEY      Specifies an array of field numbers in the key.

DBIKEYEXP Specifies the key expression for an expression index (dBASE only). This field is used only if bExpIdx = TRUE. The expression is stated as a dBASE expression.

DBIKEYEXP Specifies the expression that defines the subset condition (dBASE only). This field is used only if bSubset = TRUE. The expression is stated as a dBASE expression.

Those examples I told you on BDE help file are more clear and simple than this one, so you'd better start from this point.




Please note I have increased the points on this question, as a token of my appreciation for all your additional comments.  I am very pleased with the advice you have given thus far, but...

Regarding you last comment:
I do not appear to have BDE.DCU anywhere on my system, or on my Delphi disk.  Could these BDE functions only be available to later versions of Delphi (remember, I use Delphi 1.0).


David M. Goncalves


An additional response came in from a newsgroup query I had posted on comp.lang.pascal.delphi.databases.:

On Fri, 23 May 1997 08:55:05 -0400, "David M. Goncalves"
<dmgoncal@vicon.net> wrote:

>I am trying to use DBiAddIndex and dbiGetIndexDesc in my Delphi
>1.0 app. I am told I need BDE.DCU in my uses clause, but I do
>not have this on my system.  Is this file for Delphi 2+ only?
>Is it possible to use these very important functions in Delphi

BDE.DCU is for D2
for D1, use all of these : DbiProcs,DbiTypes, DbiErrs

Chuck Gadd
Director of Software Development, Cyber FX Communications.

Sorry about this mistake, I should have asked you befor what Delphi version you had. I already knew that BDE is only for Delphi 2 and that you had to include the other dbi* units for Delphi 1. I'm sorry about that. Anyway, I hope you have succesfully make it work.

Regards, Javi



Thank you for your continued assistance.  Unfortunately, my latest problem persists even with the following units in my uses clause: DbiErrs, DbiProcs, DbiTypes (they were always there).

When trying to set SZNAME, aiKeyFld,szKeyExp,szKeyCond  I receive Error 26 : Type mismatch.

If you refer to my original question, you will see that I ask:
"I need to be able to create dBASE IV "subset filter" expression indexes on-the-fly in my DELPHI 1 app."  All the rest has been commentary.

I will continue to post my "point allowance" on this question until I have a conclusive answer to my first question.  That is,
IF IT IS POSSIBLE, HOW CAN I DO IT?  Obviously, we're just a fractional step away.


Still haven't gotten it to work, but I'll post a fresh question to narrow-down the problem.  Thanks for all your help; we have made significant progress.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.