Hi. Amazing resource here, I hope someone could point me in the right direction, if i can explain my problem correctly.

I have been asked to convert out company DB from Access to FM.

Each of our stock items are unique but there are essentially 2 types of stock, type A and type B. In the old database the stock number ran from 2000 for type A and 6000 for type B. A year code was also appended to the front of the stock number (Y1, Y2 etc ).

When you created a new record if you selected Type B then the stock number would be created by combining year code and the next number available, Y16001, Y16002 ,Y16003 and so on, Type A would result in Y12001, Y12002 etc.

At the beginning of the year the year code is changed and the numbers reset to 2000 and 6000.

(....this is the way is has always been done : )

I have set up the new stock table in FM using an auto serial field called StockID.pk and am using it in my relationships, I believe this is best practice? I have created Stock_number as a separate field and am now stuck as to the best method to generate each new stock number.

I hope I have explained this well enough to make sense, I would probably prefer to use the SockID.pk as the stock number but everyone else is very entrenched in the old system!

Thanks.

It's not hard to combine elements and include your Stock_pk as part of your visible key: Set your serials to start at 2000 and 6000, and have a separate calc field that would add your "Y1" to your pk field.

However, because you'll be restarting the 2000/6000 sequence each year, I don't think this is a good idea. For development purposes, it is a best practice to have a unique key for each record. If you reset your key serial to 2000/6000 each year, you'd certainly run into problems there.

You can still use the same technique (serial plus calc), but I recommend maintaining a separate, unique primary key field.

How many of these items will you have anyway? Perhaps you could simply add either 2000 or 6000 to the serial number. It will run out of numbers somewhat sooner than your current method, but that too cannot last forever.

Thanks for the replies.

There is never more than a few hundred items each year.

I guess what I am not sure about is the mechanism used to create the stock number ( a script? ). If I create a new stock record I have a new incrimental StockID.pk number , great I can keep that in the background? next the user selects either Type A or Type B then I then have to generate the stock number from that.

what I need if for each Type to have sequential stock numbers if that makes sense?

StockID.pk=1 Stocknumber=Y12001

StockID.pk=2 Stocknumber=Y12002

StockID.pk=3 Stocknumber=Y16001

StockID.pk=4 Stocknumber=Y12003

StockID.pk=5 Stocknumber=Y16002

then for instance after new year after reset ( Y2 ) and with 200 stock records

StockID.pk=201 Stocknumber=Y22001

StockID.pk=202 Stocknumber=Y22002

StockID.pk=203 Stocknumber=Y26001

StockID.pk=204 Stocknumber=Y22003

StockID.pk=205 Stocknumber=Y26002

It is very difficult to maintain two separate sequential series in the same table, and to do so reliably (and that's even without considering the yearly reset part). If I had to do it, I would use separate tables as "numerators".

However, I doubt anyone cares if there are gaps in the series, e.g.:

StockID.pk=1 Stocknumber=Y12001

StockID.pk=2 Stocknumber=Y12002

StockID.pk=3 Stocknumber=Y16003

StockID.pk=4 Stocknumber=Y12004

StockID.pk=5 Stocknumber=Y16005

Thanks for your help. It has enabled me to convince those who needed convincing to alter the system. One sequential Stock number for all the stock records, not 2 separate ones.

The speed and helpfulness of this forum is very impressive. Sometimes its very very useful to be able to run something by a fresh set of ears.