ID Field

[Jed69]

I am trying to create an autogenerated Id number for an invoice but this number must be prefixed by the year for example 2012/1 however at the start of each new year these number need to go back to 1 so for example the first invoice next year will be numbered 2013/1. can anyone suggest the best way for this to be done.

John

[dansmith65]

First off, "2012/1" isn't a number, it's text so you will need to use a text field to hold the final invoice #. Unless you were to use a decimal separator, then it could be a numeric field: 2012.1

You might need to use two fields to generate this: one to hold the auto-incremented number, the other to combine the year with the auto-incremented number.

I'm not sure how you would re-set the auto-incremented number at the beginning of each new year though. You could manually do it easily enough, but I'm not sure how to automate it.

[myopus]

You must create a relationship from your table to itself. Matching on the year and the numeric field.

Then a script can increment your numeric field with a "max(MyRelationship::numerique) +1.

So, the numeric field will start with "1" when appear a new year.

(I hope my english spoken is understandable enough)

Gilles

[comment]

at the start of each new year these number need to go back to 1 so for example the first invoice next year will be numbered 2013/1. can anyone suggest the best way for this to be done.

The best way is not to do it at all. It only complicates things and opens an opportunity for a duplicate to be generated. You can easily prefix the serial number with the year while maintaining the sequence across the years, e.g. 2011/845, 2012/846, etc.

If you absolutely must reset the numbers, run a script that compares the current year with a stored year value. If they do not match, reset the serial and update the stored year value. You must also make sure the script locks out other users from doing the same while it's running.

You must create a relationship from your table to itself. Matching on the year and the numeric field.

Then a script can increment your numeric field with a "max(MyRelationship::numerique) +1.

This is not a good method, because two users creating a new record at about the same time will end with duplicate serials.