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
Replace doesn't work
David Searles
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.
Amritha Chand
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.
syhzaidi
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