Jump to content

This topic is 4424 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 4424 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

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