Rick Sanders wrote:
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.
Neither do MSSQL or MySQL. Forgive my bluntness, but I don't know what you're talking about...
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.
Again, I don't know what you're talking about. Joining tables that use auto-increment or identity is no different than joining tables without those properties. All identity/auto_increment does is fill in the value for the field automatically when you do an insert.
There must be some miscommunication here... and I think Roland would agree.
/John
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
