Auto Increment problem

[Mark Pearson]

I have searched the thread but there are answers that skirt this problem but none definitive. I have a field rule to auto increment. I want it to start at 1. For some reason it always sets the first record to 0. I have the simple default set to 1 and the skip to .t.. Is there something else that I am missing here.

[Garry Flanigan]

Re: Auto Increment problem

You should make all autoincrement fields character not numeric.
If you think you will never have more than 1000 records, I would set it to character 5 with field rules on 'Data-entry' tab 'Default mode' value set to 'Simple default expression' and 'Default value' set to '00001" and Skip set to true.
Numeric fields are not indexed and also numeric fields should only be specified when you plan to do maths, add subtract, etc..
Also note that the rule will not update existing records.
Hope this helps.

[Mark Pearson]

Re: Auto Increment problem

Interesting. I have only ever used numeric and kept it totally in the background where it cannot be touched

[Garry Flanigan]

Re: Auto Increment problem

Access defaults to numeric. Unfortunately Alpha lets you do some things that you later find can be problematic.

[Raymond Lyons]

Re: Auto Increment problem

You should make all autoincrement fields character not numeric....

This is highly debatable. Before ruling out using numeric fields you should peruse Bill Parker's post # 10 in this thread:

http://msgboard.alphasoftware.com/al...ad.php?t=77116

As I have said elsewhere, I have used both character and numeric fields for ID's and auto-increments in different apps for more than a decade and as near as I can remember (!) have never had a problem when I used numeric. And as the above thread indicates, as we move more into the SQL world there may be good reason to stay away from character ID's. Which I suppose may make your statement

Unfortunately Alpha lets you do some things that you later find can be problematic.

true but hardly in the way you were thinking.

Raymond Lyons

[Garry Flanigan]

Re: Auto Increment problem

Maybe it is worth saying that I have not had a problem with autoincrement but do always use character and have no first hand experience with numeric.
When I first started using Alpha, I read many posts and decided that the more knowledgeable members recommended character, so rather than re-invent the wheel I followed their advice.
It is a pity that Alpha rarely provides input on these discussions. I spoke with Jerry Brightbill a couple of years ago about some design issues I had and it seems in some cases there is no definite answer.

[Raymond Lyons]

Re: Auto Increment problem

Maybe it is worth saying that I have not had a problem with autoincrement but do always use character and have no first hand experience with numeric.
When I first started using Alpha, I read many posts and decided that the more knowledgeable members recommended character, so rather than re-invent the wheel I followed their advice....

Yeah, I read those posts too after I had already used numeric in a major app and I thought maybe some day I'll have some problem or another and will have to change to character. But after 10 years of heavy use in a networked, multi-user environment there never was a single issue. It's almost ironic that we will be switching to numeric as we move away from DBF to SQL tables (see the thread I referred to above).

BTW, you certainly can index a numeric field.

Raymond Lyons

[Garry Flanigan]

Re: Auto Increment problem

BTW, you certainly can index a numeric field.

You are correct Raymond.
I should have been more specific.
From the Help re LQO:

For Lightning Query Optimization to use a pre-existing index, the index must:
Use at least one of the same fields used in the query.
If you use multiple fields with different data types, the first field used must be type character,
The data type of the data in the index key must match the data type of the field you are searching.

So, as I understand it, if a field is numeric autoincrement and is concatenated in an index it is not used in LQO and must be separately indexed.

[Garry Flanigan]

Re: Auto Increment problem

I want it to start at 1. For some reason it always sets the first record to 0. I have the simple default set to 1 and the skip to .t.. Is there something else that I am missing here.

If you choose "set default at start of data-entry" it starts at 1, otherwise I also see that it starts as 0 - which it should not.

[MikeC]

Re: Auto Increment problem

Ray,
I do not wish to spark yet another debate on character vs numeric, but here is a couple more specifics that should be considered.

The web only uses character so if you are not needing to use SQL, character may be the better choice.

Numeric auto-increment fields also have a slight problem with the default starting value whereas Character auto-increment do not--see thread from this link where this was discussed. http://msgboard.alphasoftware.com/al...ent%2C+numeric

Like most things, much depends upon how a database is to be used.

[Raymond Lyons]

Re: Auto Increment problem

Ray,
I do not wish to spark yet another debate on character vs numeric, but here is a couple more specifics that should be considered.

The web only uses character so if you are not needing to use SQL, character may be the better choice.

Numeric auto-increment fields also have a slight problem with the default starting value whereas Character auto-increment do not--see thread from this link where this was discussed. http://msgboard.alphasoftware.com/al...ent%2C+numeric

Like most things, much depends upon how a database is to be used.

Hi Mike,

It's not really a "debate" as far as I am concerned, except when someone states something like "make it this way or the sky will fall" when in fact the sky will not fall. I like having a choice and have used both character and numeric with no problems with either one. I vaguely remember one time having to turn off a numeric auto-increment and manually inputting a desired start number and then turning auto-increment back on--but I do not consider that to be a big problem even though there may be a bug as mentioned in the thread you referred to.

Good point about the web, just as the point about SQL is good to keep in mind. I have used SQL tables in an A5 app but have done nothing with A5 on the web. So here is a question: Are you saying that a SQL database that makes used of auto-incremented numeric fields could not be used in a web application? Any web application or just an A5 web app?

Ray Lyons

[MikeC]

Re: Auto Increment problem

Hi Ray,

Are you saying that a SQL database that makes used of auto-incremented numeric fields could not be used in a web application?

Not at all....except for the initial default glitch noted in the thread I gave a link for, it is just a matter of how much converting from character to numeric (and vise-versa) a person wishes to do, or more aptly, avoid.

[Raymond Lyons]

Re: Auto Increment problem

.... it is just a matter of how much converting from character to numeric (and vise-versa) a person wishes to do, or more aptly, avoid.

Mike,

Being ignorant, I am still not sure I understand: Forgetting A5 for a moment, and given that almost all databases these days are SQL, are you saying all types of web app developers have to convert each and every numeric field in a SQL database to character before it can be used in a web app? If so, isn't that a bit lame? Or maybe not, as I guess web apps are all essentially text based documents, kind of like sticking a numeric in an RTF box. Hey, I really am ignorant when it comes to web apps. May have to do something about that--or die!

Ray Lyons

[MikeC]

Re: Auto Increment problem

Ray,
I am probably as ignorant as you are regarding the Web side of things...but have been learning of the many nuances so that I can jump in when the time is right.....this thread link pretty much covers it http://msgboard.alphasoftware.com/al...racter+numeric where Steve Wood states in post 2:

I think numeric is fine for hidden keys provided you are not going to take your application to the web. In the web EVERYTHING is a character, even your numeric fields. That means you constantly have to convert them in code when they are used on the web. That rule is not everywhere, so that makes it worse, having to figure out when you need to convert, when you don't.

[Raymond Lyons]

Re: Auto Increment problem

Thanks Mike.

I was involved in that thread so I should have remembered the contribution you quoted (Steve's), not just Bill Parker's (which I did remember).

Ray