I have an order file that has one key and that key is a sequential counter. Many users can create an order.

Problem: I had two users obtain the same sequential counter and the one user overwrote the other user's order resulting, of course, in the first user's order not being filled. (How could it, it was overwritten.)

Does anyone have any idea how I can prevent this from happening again?

Thanks

Andy Becker

IL State Lottery

Well .. do a search on sequential counter / keys. The AREV / OI way of doing this is totally flawed and, in general is pretty worthless (witness the problem you've had). The only satisfactory way to handle this is to roll your own sequential key routine. The way you construct this depends on whether your sequential key assignment will allow holes in the sequence. For example, if it's being used to assign check #'s, then it would be a no-no. However, if you're simply trying to assign a unique transaction number for new records then the logic is different.

The code necessary to do this is, I think, beyond the scope of this post .. besides that's how developers make their living anyway. Suffice it to say, the new key number should only be assigned and updated when the new record is written and not when the screen opens. Most assuredly, don't use it as a "default".

HTH

Don Miller

C3 Inc.

Thanks Don - if I would had realized that when I designed the system, I would have had more than one key on the orders file.

Oh well - I'll figure something out

Thanks

Andy Becker

IL State Lottery

Try this

http://www.revelation.com/__8525656100571cbb.nsf/0/57CA94CE7F8F2DAD85256994002379FF?OpenDocument

The Sprezzatura Group

World Leaders in all things RevSoft

I use sequential counters (I call them next availables). The steps I use are as follows…

NOTE: At data entry of the record (row) id, if it is NULL then get the next sequential counter. I store the next sequential counter in a sequential counter file (table). The record (row) id is the file (table) name and field 1 in the record (row) is the last used sequential counter.

1) Lock the sequential counter record (row). Read the sequential counter record (row).

2) Lock the file record (row) using the sequential counter as the ID. If the lock fails skip to step 4. Otherwise read the file record (row) using the sequential counter as the ID. If the record (row) already exists then skip to step 4.

3) Write the sequential counter record (row). Unlock the sequential counter record (row). All done!

4) Unlock the file record (row). Increment the sequential counter and then go back to step 2.

This method will probably fail the first time almost always because the record (row) should already exist. Doing it this way should almost guarantee not skipping in the sequence (well about 99% of the time). I guess if user A is working on record (row) 1, and at the same time, user B is working on record (row) 2, and user A chooses to abort the entry, then record (row) 1 will never exist sequentially.

]] You said ..

(1) Lock the sequential counter record (row). Read the sequential counter record (row).

2) Lock the file record (row) using the sequential counter as the ID. If the lock fails skip to step 4. Otherwise read the file record (row) using the sequential counter as the ID. If the record (row) already exists then skip to step 4.

3) Write the sequential counter record (row). Unlock the sequential counter record (row). All done!

4) Unlock the file record (row). Increment the sequential counter and then go back to step 2.

This method will probably fail the first time almost always because the record (row) should already exist. Doing it this way should almost guarantee not skipping in the sequence (well about 99% of the time). I guess if user A is working on record (row) 1, and at the same time, user B is working on record (row) 2, and user A chooses to abort the entry, then record (row) 1 will never exist sequentially

This is ok if you don't mind holes in the numbering scheme. As you pointed out, if a record is aborted then possible holes. Another possibility is deleting an erroneous entry after saving. If holes are not allowed, then the delete cannot be allowed. Instead a "void" record needs to be created (I usually date/time/user stamp this too). The better way is to do the task at write time rather than when the record is begun.

Don Miller

C3 Inc.

View this thread on the forum...