Jump to content
Salesforce and other SMB Solutions are coming soon. ×

serial number reset each year


Recommended Posts

I want to create a serial number that will start over each year. For example, Toyota-2005-1, Toyota-2005-2; then next year, Toyota-2006-1, etc.


I am doing this in 6, but it uses copying, pasting, searching, etc. I was hoping 7 would make this easier - just learning all the new stuff :-( :-| :-)

Link to comment
Share on other sites

Could you simply reset it on January 1st of each year?


What's the significance of having "Toyota" in the serial number? Are there other car models that are serialized the same way?

Link to comment
Share on other sites

I do not want to manually reset it each year - in case I am not here. I want the system to run year after year without me having to reset. Also - I might be on vacation on the first, etc. I used Toyota as an example - the database has instances for each group, the prefix identifies the groups database - components. This is no big deal if I could get the serial to be reset at the beginning of every year. I can use a calc field to make my unique serial. For example: "Toyota-"&Year(prep_date)&"-"&serial_number

Link to comment
Share on other sites

There are several ways to go.

Basically you need to set up a relationship linking your basic ID (“Toyota” + Year) and use that to set up a conditional link.


Here is a rouch sketch out of memory of one approach.


You need following fields:

item : like Toyota


itemID = item& Year(prepDate)




A self relationship Item is based on itemID = itemID

What you would like is to make serial the max of the related serial + 1.

But FM will yell that that is a circular. Sort the relationship by serial ‘descending’, this will always give you the largest one and that will be the one to look up.

Add a field previousSerial as a lookup of serial via relationship ItemID with default 0.

Now let serial be calculated as previousSerial + 1

Lookup breaks circularity and also allows serial to be indexed and stored.


Better would be to script your creation of new records.

You still need itemID and serial and the relationship but do not need previousID and serial is a regular number field, not calculated.

Simply add to the script :

Set Field (serial, Case(itemID::serial > 0; serial +1; 1), after itemID has been entered.

Link to comment
Share on other sites

I suggest that you simply rethink the numbering scheme. Auto number each record continuously so that numbering integrity remains intact year after year. Then, in your "new record" script, have a SerNo. field set to:


SetField(SerNo.,"Auto No. & "-" & Year(Status(CurrentDate)")


Which would produce record numbers as follows:









Tom smile.gif

Link to comment
Share on other sites


This topic is now archived and is closed to further replies.

  • Create New...

Important Information

Terms of Use