major crisis

[martinwcole]

my auto increment field rule is failing to assign unique numbers. i discovered irregular data, changed to user enter - corrected - changed back - entered sample data - is assigining numbers that already exist - since the integrity of the data depends on this field rule - please advise what could possibly be wrong - 4.03 - build 230

[Dan Blank]

RE: major crisis

Hi Martin:

I had a similar problem, the last record no in my table was not the hightest auto incremented number. So in browse mode with the Auto increment turned off, I made the last record the highest value that was next in line with the auto increment mode. Saved the record. Then went back and turned the auto increment on.

That worked for me. Hope that helps.

Dan

[Anthony Tampone]

RE: major crisis

Does anyone have any Ideas on what might cause this problem, I never realy thought about it until I read this, but If this ever happened to me I would be screwed!

[Bill Warner]

RE: major crisis

Another solution that I've used (I got from Learn Alpha) is to define your own incrementing. Basically, you have a separate table (i.e., INCREMENT.DBF) with one record, and the field in INCREMENT has the value for your incremented field.

When you create a new record, you first retrieve the value from INCREMENT (so it can be used in your app), and then increment the value in INCREMENT so it's ready for the next new record.

This has solved two problems for me. The first was getting duplicate values when two people on a network started a new record at about the same time (keeping in mind that it takes 15-30 seconds to finish the record and then save it). Since the script for creating the new record retrieves the value and then immediately increments it, the chances of two people creating a record at exactly the same time are very remote.

The second problem that I solved was with one customer where, for some unexplained reason, a blank record was created. The auto-increment then started the next new record at 00000000. This new method took care of that, too.

If you need more specifics, please e-mail me for the script.

Bill

[martinwcole]

RE: major crisis

Dan - thank's ten million times. I tried the following unsuccessfully:

1. change the field rule to user
---changed uniquesn from the highest (277) to 278
---saved, changed field rules back - tested, failed.
2. copied the files back and forth to a new dummy file to see if something was corrupted, unsuccessful.
3. thought of a lot of bad things, comtemplated self destructive behavior, pulled a few of my few remaining hairs out - all unsuccessful!!!!!!!!!

I tried the following successfully:

1.changed the field rule to user, entered a new record and assigned that field a higher value - saved, changed back to autoincrement, tested, SUCCESSFUL!!!!!!

what caused this, I think, is that I temporarily changed the value and forgot to change it back one night, leaving the users with the rule the next day.

PS: I use my own "autoincrement" generator in other files - has never failed me. But in this file I use the field rule

thanks all

crisis over

[martinwcole]

RE: major crisis

thank's Bill
I do the same thing in another file using more or less the same script from the same source - works like a charm

[Jim Chapman]

RE: major crisis

Hello,

I believe that A5 autoincrements based on the last previously save record. In other words if the last saved record in the auto increment field contained 10 the next record will be 11, but if you remove the autoincrement rule, then save a record with a value of 2 in the field, now go back and put back the autoincrement rule, the next record will have a value of 3. Even though a value of 3 exists in another record. It's incrementing from the previous record.

The autoincrement rule is not necessarly a unique rule.

Jim

[Jose de Leon]

RE: major crisis

Hello Martin,

I'd recommend Bill's suggestion. It is what I do. I had the same problem too but what was happening is that when more than one person was entering records, the field would have dups like what you are having.

Jose

[martinwcole]

RE: major crisis

I'm glad you told me that - although I am not currently on a network anywhere, I'll have to be on one at two locations soon coming up. i guess you would have to lock the file used to supply the number while one user got his number, increment it by one, and then release it, as it would be typical for two users to be entering customers at the same time. I'm surprised networking doesn't handle that for an autoincrement field. thank'd for the advice.

[martinwcole]

RE: major crisis

Jim, do you use autoincrement on any networks? In fact, if autoincrement will not work on a network, i.e, guarantee a unique field, I will have to reevaluate field rules; especially since I have a lot of code in field rules. In general, what field rules would fail in a multi-user environment.?

[Jim Chapman]

RE: major crisis

Hi Martin,

Yes, but pretty small networks. It's my understanding that the auto increment number is arbitrated on record save. This means that if two users are entering a record at the same time, the first to save gets the next incremented number. The only problem with this is that the incremented number a user was looking at when entering the record would not necessarily be the one the record ended up with. I haven't had any problems with auto incrementing.

Jim

[Jim Rowlen]

RE: major crisis

I've caused it to happen during an append operation. The table being appended FROM was manually created for test data and I had changed one of the lower RECORD NUMBER's unique IDs. ie
rec #...ID #
1...1001
2...1002
3...1003
4...1005
5...1004
Even tho I set the index to the unique ID, A5 apparently appends records in record number order. As a result, record order number 5 ("last" in the sequence) became the highest id # and a duplicate autoincrement occured (rec # 4, ID 1005) when entering a new record after the append.

[martinwcole]

RE: major crisis

yes - the only way i could get it to reset correctly was to enter another record - changing the value of an existing record didn't work

however

since i have to start rewriting my program for a network, drat the hassel but hurray for the billed time, i'm going to use the "make your own autoincrement rule" opening the increment file as read/write exclusive until it is reset - thereby hopefully eliminating two or three birds with one beebee.

[nick marodis]

RE: major crisis

Martin, I had the same problem raise its ugly head on one of my clients networks the other day. I think I sloved the problem by creating a unique index using the auto incrementing field. Will see.

Nick

[drgarytraub]

RE: major crisis

I too have had problems using the autoincrement field rule. However, I have been using the following method without any problems:

dim tbl as p
tbl=table.open("patients")
query.filter="idnumber>=''"
query.order="idnumber"
query.options=""
tbl.query_create()
tbl.fetch_last()
dim nidn as n
nidn=val(tbl.idnumber)
nidn=nidn+1

This seems reliable. Any comments would be appreciated.