how to add a key using a foxpro index file ( cdx)

Question

Is it possible to add a key to index file cdx programmatically using a function like addkey()? I want to use Ms FoxPro.

An Integrated functions to manipulate a foxPro index File . I used that but with clipper 5.2e Is there an equivalent in FoxPro

Nb : I use sx_KeyAdd ( SIX RDD Library for clipper).

Answer 1

In FoxPro the database engine isn't a library like it is in Clipper. Therefore there are commands instead of functions to manipulate index files. To add a key to a CDX file you must first open the table exclusively:

USE path-and-filename EXCLUSIVE

Then you can use the INDEX command to add a key. For instance, if you have a column named fullname and you want to create a key named tagName, you would use the following index command:

INDEX ON fullname TAG tagName

If you have two columns named firstname and lastname and you need an index on combined value of both columns, you would use the following index command.

INDEX ON lastname+firstname TAG tagName

This code assumes you want to sort by last name followed by first name. To manipulate a CDX file from a different language than FoxPro you need a library that is specifically aware of FoxPro index files. While FoxPro's index files are similar to those of dBase and Clipper, they aren't identical.

Index files are automatically maintained by FoxPro when you change an indexed column, that is, the name of the column is listed in the index expression. This happens for all open index files. Index files that are not open are not updated and cannot updated individually. The only way to bring a closed index file up-to-date is to open it and use the REINDEXcommand.

For this reason best practice in FoxPro is to use one CDX file with all index tags per table. While FoxPro supports multiple CDX files and even combinations with IDX files, these are very difficult to work with. The primary CDX file is automatically opened by FoxPro whenever you open the table.

The only option to selectively add records to an index is with the FOR clause. You can specify one expression that is evaluated for a record when it's added and when it's updated. These rules should only base on the current record not on external data such as the user that is logged on into the application. When VFP reindexes the table, this expression is evaluated for every record outside of the scope that the record was added in initially.

The FOR clause is part of the INDEX command. For instance, to create an index on the column INVOICENO for all records where the PAID_ON column is empty, ie. all unpaid invoices, you would create an index like this:

INDEX ON InvoiceNo TAG InvoiceNo FOR NOT EMPTY(Paid_On)