Replace doesn't work

Ok , what I'm trying to do is to generate a new ID every time I append a new record, this is the code I use

I find the maximum value and store it to a variable

CALCULATE MAX(invoice.id) ALL TO var_id

Increment the variable

var_id=var_id+1

I replace the "id" field with the variable, but it doesn't work

REPLACE invoice.id WITH var_id

What could be wrong the field's value doesn't get replaced with the variable's value

Visual FoxPro2

When you use command: CALCULATE MAX(invoice.id) ALL TO var_id -> cursor go to EOF() -> the field's value doesn't get replaced with the variable's value

Before use command REPLACE you find record # for invoice.id is max, after you use REPLACE invoice.id WITH var_id.

The CALCULATE command moves the record pointer to EOF() (it has to, to be sure it has checked all records)

REPLACE has a default scope of NEXT 1 - i.e. it only replaces data in 1 record.

If you are at EOF() there is NO record, so nothing to replace - but this is not an error - it just means that the replace has 'finished'.

The solution is to save the record pointer BEFORE you do the Calculate and restore it afterwards:

lnRec = RECNO()
CALCULATE >>>>>>
GO TO (lnRec)

However, this is NOT a good way to generate IDs. The best way is to maintain a separate table which contains, for each table that requires an ID, the table name and the last value. Then write a little function that will locate the correct record, LOCK it, get the next value and then update the table and unlock.

This way you ensure that there can be no collisions when multiple users are accessing the data.

Andy is correct. If you want a ready made table you can use mine:

How do I generate new IDs

http://www.feldstein.net/newid.asp