Serial number with year

[Bruno Dini]

Hello all!

What I am trying to do is very simple, but I can't figure out how.

I have this joblist, and every new job creates a new serial number (I'm using an "auto enter serial" for this field), but I need to put the year after this serial number, like this "0345/2007", and if possible, resets the count next year and so on.

Is there any calculation that can be done?

Thanks!

[Dana G]

I can tell you how to get the year. Not sure about resetting it though.

To get the year you will have to add a field. Leave the one that you have with the auto enter serial number. Create a new field and set it as a calculation. In the calc box type the following:

[Your Serial Field] & "/" & Year ( Get ( CurrentDate ) )

Then, on your layout, replace the auto enter serial field with the one that you just created.

[Bruno Dini]

Thanks! It worked, thats exactly what I was trying!

It will work well until december ;-)

Just hoping now someone can come up with a solution for the reset thing.

Thanks for now.-D

[Dana G]

No problem. As far as the serial number reset, you could just go back into the auto-enter options on Jan 1 and set the "next value" box back to 0001.

[Bruno Dini]

Would it get in conflict with the previous numbers?

For example, I already have the 0002 job in my database, if I reset the counter it will overwrite or duplicate this number?

[Dana G]

It won't overwrite but it will duplicate the number. It shouldn't matter though because the field you created to add the year should be the one marked "unique". It's the one that important to you right. To keep people (other than you) from modifying the serial number, just set it so that it's not modifiable or set the field behavior to not allow entry in browse mode. At the beginning of the new year, take a couple seconds to reset the serial number and then turn the modification rules back on.

[bcooney]

:badidea:

Ah! I can't just let this thread go by without screaming, "NO!!!!"

Please don't make any record primary keys meaningful! You job key field should be an auto-enter serial and perhaps not seen by the user. If you want to create a serial number "counter" field and a calc field that concatenates it with the current year--go for it. But these two should NOT be a key field for relationships.

[T-Square]

I second that emotion!

If you REALLY want to have a resetting serial number (I sure wish people would get over that whole "My Serial Number is a Both a Unique ID AND a Dessert Topping" thing), you could create a calculated field that automatically resets each year. You'll need a couple of fields:

1) A record creation date field

2) A CreationYear field that uses #1 to lookup the year (might as well store it in this age of cheap memory).

3) A Global CurrentYear field: Year(Get(CurrentDate))

4) A RecCtr field: Get(RecordNumber)

Use a self-join on the two year fields. Let's call the new copy of your table "ThisYear." Sort the relationship by serial number, and create your new DessertTopping field:

CurrentYear & "/" & Right("00000" & ThisYear::RecCtr, 5)

This should give you a resetting countup of entries. Keep in mind that if you delete an entry, all subsequent calculations will change--so if you delete the first record of 2007, the second record's DessertTopping field will change to "2007/00001".

David