Jump to content
Sign in to follow this  
Bruno Dini

Serial number with year

Recommended Posts

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!

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

: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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.