Auto Increment Problem

[wlewis7961]

I have a problem with a Auto Increment field in database that I've converted from Alpha 4 to Alpha 5.

The Auto Increment filed is used to build a unique item # of YYMMSSSS where the SSSS is the sequence number. The A4 database "rolled over" like an odometer from 9999 to 0001 long ago & was working properly. When converted to A5, it doesn't work right.

The field has been character 7 but its also been 4 & 8 digits as I've tried to work around the problem. As 7 char, it defaulted to 9990000; it would not take a simnple default expression. I tried converting it to numeric or 4 & 8 characters thinking auto increments may be better as numerics in A5. As num 4 it said the value was too big. As num 8, it defaults to 10000.

I think A5 may have corrupted something along the way. I'm trying to synch with the old sequence number & there is no way I can get it to synch. Any ideas would be appreciated.

Wayne
[email protected]

[Tom Cone Jr]

Re: Auto Increment Problem

Background: In the context of the auto-increment field rule the "default" field value is useful for the first record in the table, only. Thereafter, Alpha increments the highest field value in any record of the table, regardless of what the default values field rule might otherwise specify.

Recommendation: Don't try to embed additional information in the autoincrement field. Use each field in your table for a single purpose. The autoincrement field s/b a primary key or unique identifier, only. Resist the temptation to embed other "meaningful" information in it. Put that stuff in a separate field. i.e. if you need to know the year or date the record was entered store that in a different field.

[DaveM]

Re: Auto Increment Problem

Wayne,

Tom gave real good advise.

Mine is to use a numeric value in the field. I fought the character a long time because others said it was better. It was not for me. I have had no problems since I went numeric. Also, if you ever wish to convert to sql, you need numeric.

If you are getting a huge numeric value somewhere( like you have 500 records and ae comes up 9000), then you already have something way high somewhere. That may take a record search.

If you are working with one table, it is easy to change the whole table, but if you have a set and it connects by that field you have set as ae, that is harder to do.

Hope it helped a little


.

[wlewis7961]

Re: Auto Increment Problem

I got past the original problem by retiring the original auto increment field & creating another. The 1st auto increment field is numeric, its then converted to string & parsed in a 2nd calc field, then used the 3rd calc field as the ID I need: YYMMSSSS where SSSS is the sequence #.

However my displayed field values are funky when a new record is entered. The very first original YYMMSSSS string & 2 intermediates created after the above modifications are the default values. These are displayed until I enter a couple of values in other user entered fields. At that time the correct YYMMSSSS magically pops up along with the other 2 correct intermediate values.

I've set the 3 sequence fields to be first in the tab order on the forms. Doesn't help. Is very misleading at first until the correct values pop up.

[Tom Cone Jr]

Re: Auto Increment Problem

The actual value assigned to the autoincrement field is not known until the record is committed to disk. That's when the "highest" existing value is grabbed and incremented. If these calc fields are needed for other purposes, but the user cannot edit them (obviously) you might consider removing them from the form altogether.

[wlewis7961]

Re: Auto Increment Problem

Two of the 3 fields are displayed just for troubleshooting. The main one is needed on the form.

In preliminary versions of the DB in A5, I think(??) the calculated was correct from the start. In A4 it was correct at the start.

Any work arounds to force it sooner rather than later?

[Tom Cone Jr]

Re: Auto Increment Problem

You could roll your own autoincrement functionality; examples are available to get you started. Check the Code Archive forum of this message board. These involve using a separate file or table to hold the "most recent" field value. During data entry in your main form you run a script that grabs that external value and increments it for use in the new record you are entering. The script that does this stores the newly incremented value back in the external table. A downside to this approach is that by grabbing the value "early" you begin to have awkward gaps if the user cancels the new record before saving it. Then you either have to undo the changes to the external table or you wind up with an unexplained gap in the numbering sequence. Factor in the complexities of coding this for a multi-user networked scenario and you begin to appreciate why the rule works the way it does now.

Another alternative that many have elected to use is to never let the user work on a "new" record. Instead, as soon as the user clicks a button to enter a new record the button push script begins the new record but then immediately commits the record to disk. This locks in the autoincrement field value. The user then completes edits in "CHANGE MODE", not in "ENTER MODE". This works well in many scenarios. However, if the user decides to cancel the new record you have to actually run a separate script to delete the partially completed record. Not a big deal, but something to chew on as you contemplate your design.

One fine note. Alpha Four was a DOS application and did not have to accommodate Windows multi-tasking event driven model. We could do some things in DOS that have proven very very difficult to replicate in Windows, but there's no sense reminiscing (sp). Alpha Four was amazing and ordinary users could construct surprisingly sophisticated systems. However, the reality is that those days are gone. Now the task is to build the most effective systems we can in the Windows' environment. It's a mistake to think that all of the original functionality in Alpha Four was carried forward into Alpha Five. They are two different products and share nothing in common except for the data format by which they each store records in tables.

[robert]

Re: Auto Increment Problem

In the field rules for a autoincrement field, you can tell it to set the default value at the start of data entry. Would this help?