When you create an auto number column the system will prompt you for the
format (see syntax). This information is stored in the SYS_DEFAULTS table
as:
SYS_NEXT Next number in the sequence
SYS_INCREMENT The increment for the auto number; it can be more that one
SYS_DEFAULT The format of the auto number column
The following statement will allow you to retrieve this information for any
column name (your_column_name) in any table (you_table_name):
SELECT sys_next, sys_increment, sys_default INTO v_next, v_inc, v_format
FROM +
SYS_DEFAULTS T1, SYS_COLUMNS T2, SYS_TABLES T3 WHERE +
T3.sys_table_name = 'your_table_name' AND +
T3.sys_table_id = T2.sys_table_id AND +
T2.sys_column_name = 'your_column_name' AND +
T2.sys_column_id = T1.sys_column_id
With this information you can change the next value, increment and format
for your auto number column. I the application I have, I run the code above
and then open a form that displays this information, the user has the option
of changing any of this parameters and then displaying the new format and
updating the auto number column (via EEPs) with the new parameters.
Let me know if you need assistance with the rest of the code.
Javier Valencia, PE
President
Valencia Technology Group, L.L.C.
14315 S. Twilight Ln., Suite #14
Olathe, KS 66062-4571
(913)829-0888
(913)649-2904 FAX
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
Behalf Of Tom Eldred
Sent: Thursday, July 11, 2002 6:52 AM
To: [EMAIL PROTECTED]
Subject: Re: Edited autonumber
Good morning...
Javier, would you mind providing a bit of insight on how that could be done?
We recently encountered a situation that has us looking for a new primary
key for our database and this idea would solve a couple of items on my list.
thanks in advance
tom eldred
----- Original Message -----
From: "Javier Valencia" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, July 10, 2002 10:51 PM
Subject: RE: Edited autonumber
> This is not necessarily true. The auto number column can generate numbers
> that have a user-defined format. I have an application in which the
computer
> generates sequential numbers that are assigned to work order numbers with
> format 02-00001 and so on, where the first 2 numbers represent the last 2
> digit of the year and the last numbers are sequential (and unique) number
> assigned by the system. I have housekeeping software that allows the user
to
> go back and forth between years by changing the format of the first 2
digits
> of the auto number. This is particularly handy at the beginning of a new
> year when users sometimes have to enter old work orders that have the
> previous year prefix, the system determines what the next sequential
number
> should be and assigns it to the new record and then goes back to the next
> sequential number for the current year. This is an ideal application of
the
> auto number feature.
>
> Javier Valencia, PE
> President
> Valencia Technology Group, L.L.C.
> 14315 S. Twilight Ln., Suite #14
> Olathe, KS 66062-4571
> (913)829-0888
> (913)649-2904 FAX
>
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
> Behalf Of MikeB
> Sent: Wednesday, July 10, 2002 7:15 PM
> To: [EMAIL PROTECTED]
> Subject: Re: Edited autonumber
>
> Autonumbering should never be used in the context to have any real world
> meaning to the user.
> If you need a number that is to hold a meaning, you should develop your
own
> numbering scheme.
>
>
> ----- Original Message -----
> From: "Nicky Avery" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Wednesday, July 10, 2002 5:12 PM
> Subject: Re: Edited autonumber
>
>
> > David Ebert wrote:
> >
> > >What is the effect on autonumbered columns if a value is manually
edited?
> > >
> > >
> > >
> > David,
> >
> > The autonumbering depends on its own housekeeping to know the value of
> > the last number it assigned. If you allow renumbering to a lower number,
> > you may end up with gaps in the run (not necessarily an issue) and, if
> > someone renumbers the records incorrectly, mismatches. If the numbers
> > are higher, you can end up with a mess since the autonumbering will not
> > know a number is already used and will plough on regardless so you then
> > have plenty of opportunites for mismatches. If the numbers are higher
> > and you define the autonum column as unique, you will likely break your
> > app when the attempt to reissue the number is rejected.
> >
> > Nicky
> >
> >
> > ================================================
> > TO SEE MESSAGE POSTING GUIDELINES:
> > Send a plain text email to [EMAIL PROTECTED]
> > In the message body, put just two words: INTRO rbase-l
> > ================================================
> > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
> > In the message body, put just two words: UNSUBSCRIBE rbase-l
> > ================================================
> > TO SEARCH ARCHIVES:
> > http://www.mail-archive.com/rbase-l%40sonetmail.com/
> >
>
> ================================================
> TO SEE MESSAGE POSTING GUIDELINES:
> Send a plain text email to [EMAIL PROTECTED]
> In the message body, put just two words: INTRO rbase-l
> ================================================
> TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
> In the message body, put just two words: UNSUBSCRIBE rbase-l
> ================================================
> TO SEARCH ARCHIVES:
> http://www.mail-archive.com/rbase-l%40sonetmail.com/
>
> ================================================
> TO SEE MESSAGE POSTING GUIDELINES:
> Send a plain text email to [EMAIL PROTECTED]
> In the message body, put just two words: INTRO rbase-l
> ================================================
> TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
> In the message body, put just two words: UNSUBSCRIBE rbase-l
> ================================================
> TO SEARCH ARCHIVES:
> http://www.mail-archive.com/rbase-l%40sonetmail.com/
>
>
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: INTRO rbase-l
================================================
TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: UNSUBSCRIBE rbase-l
================================================
TO SEARCH ARCHIVES:
http://www.mail-archive.com/rbase-l%40sonetmail.com/
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: INTRO rbase-l
================================================
TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: UNSUBSCRIBE rbase-l
================================================
TO SEARCH ARCHIVES:
http://www.mail-archive.com/rbase-l%40sonetmail.com/
