I'm a newbie, so please bear with me as I try to give the best explanation of my problem.
I have a DB that tracks orders from our website. Each new order (record) creates various bits and bobs (invoices, etc) as well as an address label with a tracking ID number and barcode. The tracking ID number consists of various infromation (store ID, area codes, etc) that change out based on the type of order, yet regardless of the other infomation each tracking ID has a unique 7 digit serial number inserted in the middle. This range of numbers are provided by our courier and I must use all of them in a sequential manner. At the moment my unique 7 digit serial number is an Auto-enter Serial (see below) so that each new record creates a sequential Tracking ID number. So far, so good.
Tracking ID/Barcode Example: AL0100065ELOFQRO
Tracking ID/Barcode Field: trackingNumber_barcode_calc = "AL" & trackingUniqueNumber & "E" & order_shippingAreaCode&store
Unique 7 Digit Serial Number: trackingUniqueNumber =Auto-enter Serial (On commit, increment by 1)
My problem is that the product that we sell (women's clothing) sometimes needs to be returned and replaced (i.e. wrong size etc). When this happens I need to create another 'one-off' address label for the replacement garment to be returned. Because the returns information is part of the same record I can't reuse the original Tracking ID/Barcode (because it would be the same number) and I don't know how to 'pull out' a single serial number from my Unique 7 Digit Serial Number.
After doing a bit of reading in this forum it apprears to me that the solution is to use a script to create a unique 7 Digit Serial Number and I think it might work like this.
A script that generates a unique 7 digit serial number and then 2 sperate ways of triggering it. One being on the creation of a new record, and the other by a button when a 'one-off' replacement address label is needed. At this stage I lack the skills and experience to put it together.
Could someone please help me, or point me in the right direction for a solution please.
p.s. I'm all ears if you have a better soloution than the one I have suggested.
Many thanks Paul.