auto generated duplicate numbers

[eric bellingham]

Can I have a method of ensuring that I never get repeated numbers generated.

Sometimes the numbers in an autogenerated number field will revert back to zero or a random number and increase consecutively from there.

This causes problems with duplicate numbers which muck up everything

any Ideas on how to prevent duplicate numbers from being generated

kind regards

eric bellingham

[Tom Cone Jr]

RE: auto generated duplicate numbers

The autoincrement field rule uses the last record entered in the table to determine what the 'next' value will be. (This is the last physical record, in record number order... not the last logical record determined by the current index).

If your users are permitted to enter or change values in the the field that is to be autoincremented then they can 'restart' the sequence accidentally. In my designs I block access to this field by making it readonly on my forms, and use the 'skip' field rule to prevent the field from ever even getting focus.

If your users are permitted to enter a record with a blank value in the autoincrement field, Alpha Five will 'restart' the sequence, too.

Consider other sources for out of sequence records. Do you ever import records from an external source? Could this be where your 'random' sequences are coming from?

-- tom

[Bill Warner]

RE: auto generated duplicate numbers

I have had problems with autoincrement when used on a network. If two people start a new record at the same time, each record can get the same number.

I solved this (and another problem having to do with inadvertently creating a blank record, and then the autoincrement starts over) by using a technique I saw in Learn Alpha (great site).

Here's the script I play from the ONENTER event in the form:

dim global new as c
dim global old as c

tbl = table.open("menu")

old = lookupc("f",1,"id","menu.dbf","")
new = padl(ltrim(str(val(old)+1,5,0)),5,"0")

tbl.change_begin(HONOR_FIELD_RULES)
tbl.id = new
tbl.change_end(.t.)

:members:tables:members.id = new
:members:id.refresh()
end

MENU is a table that I use to create the Main Menu, and it also has a field called ID in which I store the ID number. Whenever a new record is entered, the first thing that happens is retrieving the current value of ID (the LOOKUPC function), incrementing it by one and padding the left with zeros, and then changing the value in MENU to the new value.

One advantage is that the value is changed immediately (rather than waiting to save the record in MEMBERS), so another user on the network can start a new record immediately after the first user and get a new number. Another advantage is that, if a blank record is created, the incrementing continues as it should, rather than resetting to zero.

One potential disadvantage is that, if you start a new record and decide to cancel it, you will end up with a "hole" in the incremented numbers (i.e., going from 1111 to 1113 because you stared 1112 and then cancelled it).

Overall, I've had much more success with this than with the autoincrementing. Plus, you also have the option (with some more scripting) of incrementing several different ways, depending on starting conditions.

[Mick Magnuson]

RE: auto generated duplicate numbers

Hi Bill
Great idea. I have ran into that same problem with barcode labels. I have a master file that holds the next sequential serial number, but have 5 machines on the floor all accessing the same master serial # file. I have had the problem of 2 users creating labels, and getting the same serial #. Big no-no. Your post will solve that problem! Thanks a heap.