Auto-increment

[Peeter Gruner]

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]

RE: Auto-increment

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.learn alpha.com, and the Newsletter Archive for additional ideas, scripts and suggestions.

-- tom

[Al Buchholz]

RE: Auto-increment

Peeter

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]

RE: Auto-increment

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]

RE: Auto-increment

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]

RE: Auto-increment

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]

RE: Auto-increment

Ed,

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]

RE: Auto-increment

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]

RE: Auto-increment

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
Cheryl

[Peeter Gruner]

RE: Auto-increment

Thank you for your clarification. I had only read the part in the field rules section.

[Richard Froncek]

RE: Auto-increment

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.
Dick

[Al Buchholz]

RE: Auto-increment

Dick

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]

RE: Auto-increment

Al; Sounds reasonable, will give it a shot.
Thanks
Dick

[Richard Froncek]

RE: Auto-increment

Al, followed your instructions to the letter.
However, this weeks autoincrement number is once again "00".
I've no idea why this is happening.
Dick

[Al Buchholz]

RE: Auto-increment

Richard

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.