Jan,
One more thing - if you are going to use the date sequence in your number, I'd strongly advise against making the column a computed column, since every time you touch the row, even if it is a reload, the number will change. A trigger (or doing it in code before the INSERT) will avoid this issue. Emmitt Dove Manager, DairyPak Business Systems Blue Ridge Paper Products, Inc. [EMAIL PROTECTED] [EMAIL PROTECTED] (203) 643-8022 From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Emmitt Dove Sent: Monday, January 14, 2008 10:05 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Lot numbering Jan, One more take for your consideration. In our case we have two inventories that we produce bar-coded labels for. In both cases we assign a 10-digit number as a unique identifier for each unit of inventory. The first two characters identify the plant producing the inventory. The third and fourth identify the specific machine number that produced the inventory. The last six are a sequentially-assigned number unique to the inventory (work-in-process or finished goods.) That number is stored on a record in a control table, and before creating the inventory record we retrieve it, increment it one and put it back, all while a lock is placed on the table. We then use the incremented number in our 10-digit sequence. If the 6-digit number hits 999999 we roll it back to 000001. In this way we will likely never have duplicates (the most active plant has been running this number sequence since 1992 and is now up in the 700,000's.) So our people can look at a load tag number and know which plant and which machine it was made on. The rest is just for uniqueness. These tables do not have autonumbers - they are not required. The 10-digit number is type TEXT (10) and keyed. Emmitt Dove Manager, DairyPak Business Systems Blue Ridge Paper Products, Inc. [EMAIL PROTECTED] [EMAIL PROTECTED] (203) 643-8022 From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of jan johansen Sent: Monday, January 14, 2008 9:30 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Lot numbering Bob, Alastair, David, Thank you for your suggestions. The only reason for the YYYYMMDD sequence was to allow a form of secondary identification to a lot i.e. visual using human readable. I had initially proposed just using an autonumber but the client requested a form of secondary identification. The request was for the numbering sequence to begin anew every day hence today = 20080114001 tomorrow = 20080115001 so concatenating an autonumber to the date doesn't quite work Another goal would be to keep the length the same since a 1 character 128code is narrower than a 6 character. So just using an autonumber starting at one would not quite work unless it could be formatted as 0000000001 (formatting an autonumber integer?) or just using 10000000001 and go with that. But again what does that mean to someone looking at it. I'm begining to lean towards 20080114063030 (YYYYMMDDHHMMSS). This format remains consistent (as long as someone doesn't screw with the DATETIME settings and allows it to be stored as INTEGER vs TEXT. Enjoyed the discussion. Jan
