The indexing is not working correctly in my FoxPro database

[foxprohater]

Data has not been showing up in a couple some dbf files. I copied one dbf with the correct data over the the dbf file with the incorrect data. The problem now is when anybody does a search the name comes up incorrectly in FoxPro 2.6. If someones types in Adams, FoxPro returns Arrington. I did not copy the cdx file with the dbf file.I did copy the cdx file over afterwards, but it still was working incorrectly. Is there anyway to reindex the dbf and cdx file so the searching comes back correctly? Thank You

 

[Stella740pl]

.cdx file IS the index file to the .dbf file. If you did what you did, copied just .dbf file, did not reindex it, worked with it for some time, then copied over an older, not up to date, index file, you effectively corrupted the index.

It is easy to fix, though.

In FoxPro, when your table in question is selected, you can try issuing REINDEX command from the command window. It might fix the problem, but usually is more radical way is recommended.

First, make sure you saved all the index tags - names, expressions, ascending/descending order, etc. - in a file. Even a simple text file will do.

Then, delete all the tags by issuing DELETE TAG ALL when your table is selected.

After that, recreate them by issuing commands
INDEX ON Expression TAG TagName ASCENDING/DESCENDING

(For more options of the INDEX command, see Help file).

 

[foxprohater]

The clients use an .mpr file to view each table when the foxpro.exe is launched. When I use the REINDEX command, I have to point to the directory of the dbf file in question. Also I would have to launch FoxPro without it pointing to the .mpr file. When your talking about tags meaning saving all of the indexing files as a word documents. The database is using access codes as its primary key to link all of the tables. Thanks for help. I appreciate it.

 

[Stella740pl]

When your talking about tags meaning saving all of the indexing files as a word documents.

No, no, that's not even close to what I meant.
NOT saving index files as word documents.

I agree with Mike, " index files must be maintained / created by FoxPro or a FoxPro app. This indexing is usually part of a professionally built FoxPro app."

But in the case that you need to restore an index file - if your application doesn't have an option to deal with it - you need to be sure to have a record of all the tags, expressions and names, to be recreated before deleting them, even if you have to jot them down on a piece of paper.

But I think, you might need help of the programmers (who built the app or who knows FoxPro) to help you rebuild and maintain the index files - and to assist you in installing the application in the future, with all the appropriate files and without corruption.

 

[foxprohater]

What are the exact commands to use in the command box. Should I reindex the cdx file and dbf file or just the dbf file? Thank You

 

[Stella740pl]

How to explain...

The commands I already gave you in my first post - may be not 100% exact because I don't know the details.

You open the .dbf file through FoxPro, you select it, take the details of the existing tags, you delete the tags, you create them again using those details, and what you get is new .cdx file. Because .cdx file is the file that stores the index for the .dbf file.

It's a simple task for a FoxPro person, and I hope this helps, but from your last few questions I got a feel that you would need much more help and hand-holding than that - sorry.

 

[cricket]

I'll bet you have an ALLTRIM() or LTRIM() or RTRIM() in your index expression.

This index will have only the same depth as the first record

If the first record has 1 character, the 'ADAMS' and 'ARRINGTON' will be indexed only on the 'A' and the order will be table natural order.

 

[dbMark]

I agree with cricket, TRIM(), RTRIM() and ALLTRIM() should never be used in indexing. Notice how trimming in an index expression can drastically affect the effective order.

Order without trimming:
A          WANG
ADAMS      JAMES
ANDERS     WILLIAM
ANDERSON   ALBERT
ARRIN      WALTER
ARRINGTON  MIKE 

Order with trimming:
ADAMS      JAMES
ANDERSON   ALBERT
ANDERS     WILLIAM
ARRINGTON  MIKE 
ARRIN      WALTER
A          WANG

Another function to avoid in an index expression is RECNO() because a table could be sorted or packed and results could also be unexpected.

 

[dbMark]

The above example is using a TRIM function followed by the first name field. I admit that if your TRIM function is at the end of the index expression, then it may very well work as expected. Even so, it is generally good practice to avoid trimming in indexes so subtle errors are not introduced as changes are made later on.

As for the truncating of trimmed fields, I seem to recall that it could happen when using the SELECT command, as in...
[

SELECT ALLTRIM(LastName), ALLTRIM(FirstName) FROM mySourceTable INTO CURSOR myCursor

 

[Olaf Doschke]

foxprohater,

you seem not like to read anything. Stella already told you cdx files ARE dbfs indexes. So why do you later ask if you should rendex the cdx???

You really should get into some reading of the foxpro documentation. You can't be helped if you don't even know the basics.

dbf is the table data of 'normal' fields. fpt is a file containing memo fields, general fields, blob fields in newer VFP versions (8,9). cdx files are index files. idx are othe index files. All together make up one foxpro table.
if you USE sometable.dbf, dbf an cdx open. REINDEX then reindexes that table. You normally don' need to care in how many files foxpro does divide one table, from the language it mostly is handled as one thing/file.

There are some commands eg for indexing, that have options to specify some idx or cdx file, but if you use the cdx belonging to the dbf you will need nothing else, as the one cdx can hold many indexes.

Mpr files you mentioned somewhere don't work on tables, they are menu code files and starting them mainly only creates a menu, they also contain the code executed, when a menu item is choosen. But then it's commands or procedures and most commonly screens, that work on the tables.

You are having a very weird and wrong knowledge of foxpro, no wonder that you hate it. Take your time to learn it or leave it to someone else. You're simply feeding your hate if you try to do something based on try on error and only do damage to your data.

Bye, Olaf.