We experienced a problem with a structural index (CDX file) which we can not explain and the fix appears to be be magic.

The problem:

When updating a table in a batch we compare the new data to data in the table. If the record is found, it is updated with the new data, if it is not found we insert (INSERT INTO) the new data in the table.

After this the new data does not appear in a grid used to display the table's data (after the update routine runs).

Quitting the app and viewing the table in the development mode. We still do not see the new data. We see updated data (data which was changed) but not data records which were inserted. After issuing REINDEX the inserted data majically appeared. We came to the conclusion that the indexes were not being rebuilt.

The table was buffered (5) and the TABLEUPDATE was successful.

The Fix:

The CDX contained an index based on the expression :

STR(rc_ch_key) + STR(rc_c_pre) + STR(rc_c_year)

Where : rc_ch_key is an integer (foriegn key)
rc_c_pre - number field
rc_c_year - character field.

This index was used to identify the records (there is no primary key).

We removed all indexes (DELETE TAG ALL) and used INDEX ON to rebuild the indexes. We did not rebuild the above index and create indivudal tags for rc_ch_key,rc_c_pre,rc_c_year. We replaced the SEEK in the code with LOCATE. The routine works fine now.

Our problem now is this same scenario is being used throught this app and we are fearful that we will get bit again.

What could of happened?