Updating an auto increment field

[Don Hutchinson]

I need to update certain records in an auto increment field based upon certain criteria, however after defining the update I get the message that I cannot update an auto incremented field. ??? Had no problem with this in V1.02. Is there a workaround for this. It is essential that I be able to do this and then sort on the updated records. Any help would be appreciated.
Don Hutchinson

[CALocklin]

RE: Updating an auto increment field

You will need to go the field rules and change the field to a "user entered" field to run the update then change it back afterward.

Just remember that in A5v4 the autoincrement is based on the last record in the table using record number order. This even includes any deleted records at the end of the table. (They're still there but they aren't visible.) So, if your update causes the highest value to be somewhere other than the end of the table, you may end up creating duplicate values at some point in the future.

[Don Hutchinson]

RE: Updating an auto increment field

Cal,

Thank you for your response. Problem is I cannot go this route. I cannot have my operators going in and mucking about in the field rules and besides it is to slow.

I have come up with another workaround where I duplicate the table (just records and index's) to a dummy table (in this case register2) where the autoincrement field has been changed to user_entered, run the update and then duplicate the table back to the original table (register).

The script is:

saved = table.current().filename_get()
dbh = db_reset("c:\program files\a5v4\files\register.dbf", FILE_RW_SHARED)
dest = ("c:\program files\a5v4\files\register2.dbf", "N")
if dest != ""
db_duplicate(dbh, dest, 5)
file_add_to_db(dest)
end if
dbh = db_reset(saved, FILE_RW_SHARED)
Update.Run("reg2 grid@c:\program files\a5v4\files\register2.dbf")
saved = table.current().filename_get()
dbh = db_reset("c:\program files\a5v4\files\register2.dbf", FILE_RW_SHARED)
dest = ("c:\program files\a5v4\files\register.dbf", "N")
if dest != ""
db_duplicate(dbh, dest, 5)
file_add_to_db(dest)
end if
dbh = db_reset(saved, FILE_RW_SHARED)

This works well behind the scene and is pretty quick.

However, when I try to run this script from a button on a form I get the error message.

"Unable to reset current table from within a card.A table reset may be done from a scrip that is run using the new script_spawn command"

I can find no documentation concerning this command. How and where does it work???

Don

[Tom Cone Jr]

RE: Updating an auto increment field


Don,

For many of us autoincremented fields are used as link fields which hold our sets together. We actively discourage the endusers from editing any link field values, and would really, really, have to be pushed into a corner in order to be persuaded that it was necessary to run saved update operations against them. Frankly, I think it's likely to be a dangerous practice.

If your autoincrement field is not being used to link your tables together into sets, you might consider abandoning it in favor of a default value field rule, tied to a 'last used' figure you save on disk somewhere. You could suppress edits by making your field 'read only' on all data entry forms. You could then run your update against the field without worrying about the autoincrmeent field rule at all. The tradeoff would be adding a bit of code to update the 'last used' figure on disk after each new record is saved.

-- tom