View Full Version : Auto-increment


Peeter Gruner
02-09-2005, 08:35 PM
I have a simple six-field table for the family photo collection. The first two fields are a 2-character prefix denoting the type of picture (eg BW, DP-digital picture, SL-scanned slide etc.), and a five-character number. All the prefixes start with 00001 and are checked for uniqueness against a Prefix+Number combination. As I understand it, the auto-increment function is supposed to work from the last manual input, eg. if I have already entered SL01001 to SL01100, if I now want to enter SL00901 or BW00001, I should be able to do that, and it should auto-increment from there, until it reaches an already existing combination. For the life of me, I haven't been able to figure out how to accomplish that, since the number always tries to increment from the highest number already in that field, regardless of how I set the field rule for auto-increment. Is this a bug in the program? Or am I just missing something? Heeeelp!!!

Tom Cone Jr
02-10-2005, 12:22 AM
The field rule autoincrement process in vers 5 & 6 of Alpha Five increments the "highest" value in the autoinc field, based on an ascending index of field values. In earlier versions it incremented the last physical record's field value.

If you manually enter a value that is lower (in the ascending sort sequence for the field) than another record's field value, the other record's field value will continue to be used as the seed for the next incremented value.

I think you're going to have to roll your own custom autoincrement sequence, or manually enter all field values. There are several threads dealing with custom autoincrement scripts in the Code Archive forum. You might also check the articles list at www.learnalpha.com, and the Newsletter Archive for additional ideas, scripts and suggestions.

-- tom

Al Buchholz
02-10-2005, 08:35 AM

The autoincrement comcept is to have a simple id field that is unique.

When you start building in additional intelligence and meaning, you have gone beyond the simple approach.

It can be argued that the functionality that you are seeking should be done in seperate fields.

Like Tom said, if you want this type of incrementing, you'll need customized code and tables to accomplish it.

You may want to reconsider your design and purpose of that field.

Peeter Gruner
02-10-2005, 08:19 PM
Thank you very much for your explanation - I suspected as much. I've been entering the numbers manually, but for the life of me, I cannot understand why Alpha Five would regress like that - their documentation still says that the incrementation will start with the last input. I don't think I am able to program much in Alpha5, I find Alpha4 much easier to use, except for the printing of reports. Again, thanks for your trouble.

Peeter Gruner
02-10-2005, 08:28 PM
Thank you for taking the trouble to respond. The Alpha5 documentation explicitly says that incrementation can start with an input value, yet the program itself works in the "simple incrementation" principle. This is very misleading, and I've wasted hours trying everything possible to make it work. My knowledge does not go far enough to program something like that myself. Maybe Alpha could rethink this "simple incrementation" principle and go back to what their documentation says can be done.

Edward Larrabee
02-11-2005, 06:42 AM
Please advise where in the documentation there is misleading information about auto-increment fields.

Have you looked at How Auto-Increment Fields Work?

Tom Cone Jr
02-11-2005, 07:28 AM

Visit the page that is "Overview of Field Rules".

Then click the link labelled "Auto-Increment".

It takes you to a page which contains this text:

Auto-Increment Fields

When you define a character, numeric, or date field with an auto-Increment rule, an incremented value is automatically filled in when you create a new record, which you can accept or overwrite. If you overwrite the default value, successive records are incremented from the value you enter.

Auto-Increment fields are useful for assigning unique identification numbers for invoices, parts, products, and customers. By default, character fields are incremented using number characters (like "0001", "0002"). If you do not enter a value for the first record, Alpha Five automatically starts a character field at "1".

When a field is incremented, it follows the previous record's field conventions. For example, the value "A1" will be "A2" in the following field, then "A3" and so on. Additionally, you can increment both letter and number field values. The auto-increment field rule takes into account deleted records.

Note: New auto-increment field values always have the same number of characters as the previous value. The length of the auto-increment field does not affect the length of the values that are generated.

When you create an auto-increment field rule, Alpha Five creates an index on the field. This ensures that auto-increment field values can never be duplicated.

It's this text, which emphasizes that the "seed" for incrementing purposes is the 'previous' record's field value rather than the 'highest field value' ever entered, that's seems a bit off. Don't you think?

-- tom

Peeter Gruner
02-11-2005, 10:50 PM
Thanks for responding to my problem, and you have hit it right on the nail. The documentation is very misleading, and has caused me hours of frustration and hair loss, until I thought there was some bug in the program. I still can't figure out if there is a bug in the program or the documentation.

Cheryl Lemire
02-12-2005, 02:21 AM
The documentation does not say it uses the previous field value, but conventions. Perhaps the terminology is confusing for somebody that is new to database design or Alpha.

'previous record's field conventions'

In the beginning of the help file it states:

If there are existing records in the table, then when the user starts to enter a new record, Alpha Five shows a "likely" value for the auto-increment field. This "likely" value is the previous high value in the field plus one.

It then clearly adds: The user if free to change this "likely" value as long as he enters a number that is higher than any existing value. If the user breaks the sequence and enters a higher value than the likely value, then the sequence for all subsequent records starts with the value entered by the user.

There are several places where it states" previously recorded high value for the field

Perhaps you could suggest a different wording that might have made it more clear for you so that you could understand it better?

The portion that Tom has quoted comes from:

Field Rules: Field Types

Perhaps more clarity can be provided on that page to clear up any confusion?

Good luck

Peeter Gruner
02-15-2005, 11:41 AM
Thank you for your clarification. I had only read the part in the field rules section.

Richard Froncek
02-21-2005, 08:41 AM
Cheryl; I have a table that had auto-incremented 334 records (a weekly entry). Two weeks ago a "00" appeared, for some unknown reason. The last two weeks now have "00" in them. When the first "00" record is clicked on, it shows a record number of 334 (which makes a duplicate 334 record ID). The last record, the second "00" record shows a record number of 335? They should be 335 and 336 respectively.
Any ideas on how to get the numbers back on track? When it first happened. I created another auto-increment field and deleted the discombobulated one. However, the nemesis appears to have returned. The field is used for identification purposes only, no other reason.
Thank you for any solution(s) you may provide.

Al Buchholz
02-21-2005, 09:16 AM

To fix a problem like that, I've found that you need to:

1. Temporarily stop access to the system.

2. Change the field type from autoincrement to user entered.

3. Have an update operation reassign the proper values to the field.

4. Switch the type back to autoincrement.

5. Put the users back on the system.

Richard Froncek
02-21-2005, 10:34 AM
Al; Sounds reasonable, will give it a shot.

Richard Froncek
02-25-2005, 10:11 AM
Al, followed your instructions to the letter.
However, this weeks autoincrement number is once again "00".
I've no idea why this is happening.

Al Buchholz
02-25-2005, 11:44 AM

You'll need to post your table - probably don't need the whole database.

Or email it directly.

I'm guessing that it's primarily a data issue with a perhaps a rule misunderstanding.

We'll need all of the files that support the table.

Peter Hall
03-17-2005, 03:28 PM
Hi Ed;
I too have spent hours trying to get auto-increment to work. It worked fine on A5V1 so when I converted to V5 I expected the same. When it didn't work I went to the PDF User Guide which said:
When you define a character, numeric, or date field with an auto-Increment rule, an incremented value is automatically filled in when you create a new record, which you can ACCEPT or OVERWRITE. If you overwrite the default value, successive records are incremented
from the value you enter.
In your defense further down it does say:
When you create an auto-increment field rule, Alpha Five creates an index on the field. This ensures that auto-increment field values can never be duplicated.
By the time I got this far my brain was too scrambled to pick up on that.
I think there are a lot of people out there that would wish that there was an option in auto-increment that would allow you to manually change the value.
I was using the auto-increment to enter the track # of the tracks in my CD collection. Every CD would start at track 1 and then would fill in automatically. That may not seem like a big deal to you, but when you have close to 1500 CD's with and average of 10 - 20 tracks each that's a lot of extra key strokes.
Thanks anyway,
Frustrated Peter.

Peeter Gruner
03-17-2005, 09:29 PM
Amen! Could Alpha5 please, please, program a patch to accomplish this, eg. allow to overwrite the incremented value, and restart the increment from there!

03-31-2005, 02:06 PM
I have the exact same problem as Mr. Froncek. An auto-increment field that has worked for a long time has now stopped functioning. The "solution" described by Mr. Buchholz is exactly correct and has worked for me in the past - but it does NOT work now. What has changed?
Auto-increment fields are a very common and useful and fundamental database function. Why is Alpha failing in this area?

Al Buchholz
03-31-2005, 02:28 PM

If you are looking for a reset-able value and you are not concerned about duplicates, you may want to use the field rule default value of increment_value(previous("table","index-tagname")).

While I agree that the functionality has changed from previous versions, I look at it that the new way is there to correct the improper way that it was done before. I have fixed many A4 and A5 (pre v5) systems when duplicates have appeared using the old autoincrement approach.