Base 36 (Alphanumeric) Serial Numbers variation?
I have an Orders table (basically the quote / invoice portion of the database) and a Work Orders table. The work orders table is used for the manufacturing plant to produce products. The work order table does have a unique auto enter serial number that the user does not see. What they see is a field that carries across the Order number. On occasion we require releasing multiple work orders against a single order. We would like to take the strictly numeric order ID and increment it with A, B, C...for the multiple work orders as needed. For example order ID 47000, requires multiple work orders, which should look like 47000A, 47000B, 47000C. How would I set this up to be auto generated serial number?
Also, on occasion we need to redo an order due to an error on our part. In this case we would like take the order ID and suffix it with "SOS" in the work order table. How would we do this so that it still relates back to the order ID table?