serial number reset each year

[mcbradford]

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 :-( :-| :-)

[Ender]

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?

[mcbradford]

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

[J Wenmeekers]

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

prepDate

itemID = item& Year(prepDate)

previousSerial

serial

 

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.

[FileMakin' Tom]

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:

 

12345-2003

12346-2003

12347-2003

12348-2004

 

etc.,etc.

 

Tom