Yikes! I'll have to go tell all my MS SQL and Access dbs that they don't work...
> -----Original Message----- > From: Rick Sanders [mailto:[EMAIL PROTECTED] > Sent: Friday, February 04, 2005 3:05 PM > To: [email protected] > Subject: Re: Witango-Talk: Unique Purchase number > > > > >> Auto Incementing numbers are good, but don't work if you want to do a > >> relational query between 2 tables (Customers & Orders) and if > you delete > >> an > >> item from the database, it re-numbers all the records. > > > > I don't think so. What flavor database you using? > > I'm talking about the default settings on MS SQL server, MYSQL, > and Access. > As far as I know, Pervasive SQL & Oracle don't re-number the records. > > Regarding relational queries, you can't do a relational query between 2 > database tables that have the primary key as an auto-incrementing number, > unless you really modify the queries and know what you're doing. > I did this > successfully a year ago, but found it was more hassle than just keeping a > numeriv field and assigning unique numbers by using the > application, which > is standard for windows programs and accounting packages. > > Rick Sanders > > > > > > >> > >> Also, if you want to input an order with several items into the same > >> table, > >> you want the order number to be the same for all the items, unless you > >> make > >> 2 tables, one for orders & one for line items. > > > > I'd expect that. In fact, orders, ship_orders, and order_items > > > > So that if an order gets broken into shipping batches, you can > keep tabs > > on > > each batch. But that all depends on what you're selling, I guess. > > > > > > > >> > >> Rick Sanders > >> > >> > >>> Another way to do the same thing is to have the UID in the table an > >>> auto-incrementing integer. > >>> > >>> When an order is created, you enter the new record, retrieve the newly > >>> created UID, and there you are. > >>> > >>> > >>> On 2/4/05 10:28 AM, "Rick Sanders" <[EMAIL PROTECTED]> wrote: > >>> > >>>> > >>>> Hi Steve, > >>>> > >>>> I've used this code, and it's been very rock-solid on creating unique > >>>> numbers: > >>>> > >>>> 1. See if there's any numbers yet in the database, if not assign the > >>>> first > >>>> number, in this case it's 1. > >>>> <@IF EXPR="<@NUMROWS array=resultset><1"> > >>>> > >>>> <@ASSIGN NAME="ordernumber" value="1" scope=user> > >>>> > >>>> <@ELSE> > >>>> > >>>> 2. Sort the resultset in numerical order. > >>>> > >>>> <@SORT ARRAY="resultset" SCOPE="local" COLS="1 NUM"> > >>>> > >>>> 3. Assign the new number, by adding 1 to the largest number > retrieved > >>>> in > >>>> the > >>>> array. > >>>> > >>>> <@ASSIGN NAME="ordernumber" value="<@CALC > >>>> EXPR='@@local$resultset[<@NUMROWS > >>>> array=resultset>,1]+1'>" scope=user> > >>>> > >>>> </@IF> > >>>> > >>>> > >>>> Rick Sanders > >>>> > >>>> > >>>>> I use the following to generate an invoice number for purchases: > >>>>> > >>>>> <@assign user$OrderNumber "<@currentdate > >>>>> format=datetime:%Y%m%d><@tstosecs > >>>>> <@currenttimestamp>>"> > >>>>> > >>>>> I want these numbers to be unique. I should have thought of this > >>>>> before, > >>>>> because I am going to have a problem if 2 orders are entered within > >>>>> the > >>>>> same > >>>>> second. Right now I have an 18 digit invoice number, but I > would like > >>>>> to > >>>>> have it 15 digits or less and I am using 8 for the date. > >>>>> > >>>>> Anyone else using a different strategy related to time, taking into > >>>>> account > >>>>> the parts of a second, that is fifteen digits or less.? > >>>>> > >>>>> Thanks > >>>>> > >>>>> Steve Fogelson > >>>>> Internet Commerce Solutions > >>>>> > ________________________________________________________________________ > >>>>> TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf > >>>>> > >>>>> > >>>> > >>>> > >>>> > ________________________________________________________________________ > >>>> TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf > >>>> > >>> > >>> > >>> ----------------------------------------- > >>> Roland Dumas > >>> Roberts Information Services > >>> 310 W. Bellevue Avenue > >>> San Mateo CA 94402 > >>> 650-347-1373 > >>> 415-412-9300 (cell) > >>> [EMAIL PROTECTED] > >>> SMS: http://new.servqual.com/html/sms.tml > >>> > >>> > >>> > ________________________________________________________________________ > >>> TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf > >>> > >>> > >> > >> > >> > ________________________________________________________________________ > >> TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf > >> > > > > > > ----------------------------------------- > > Roland Dumas > > Roberts Information Services > > 310 W. Bellevue Avenue > > San Mateo CA 94402 > > 650-347-1373 > > 415-412-9300 (cell) > > [EMAIL PROTECTED] > > SMS: http://new.servqual.com/html/sms.tml > > > > > > ________________________________________________________________________ > > TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf > > > > > > > ________________________________________________________________________ > TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf ________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
