Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×

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

Recommended Posts

  • Newbies
Posted

I have a database that has sequence numbers, in the field "Sequence", that increment by 1 everytime a new record is created. The number needs to start at 1 each month and go as high as it necessary until the end of the month. When the next month begins, that field needs to be reset to 1 again. IS THERE A WAY TO DO THIS AUTOMATICALLY? Otherwise, I will have to remember to go in and reset the numbers manually, which is an option I do not care for. Please help!

Posted

quote:

Originally posted by hawk02:

I have a database that has sequence numbers, in the field "Sequence", that increment by 1 everytime a new record is created. The number needs to start at 1 each month and go as high as it necessary until the end of the month. When the next month begins, that field needs to be reset to 1 again. IS THERE A WAY TO DO THIS AUTOMATICALLY? Otherwise, I will have to remember to go in and reset the numbers manually, which is an option I do not care for. Please help!

Automatically? Hmm, that could be difficult. You could use a self-relationship by some kind of compound key.

Create a calculation called MonthKey = Date(Month ( CreationDate ), 1, Year ( CreationDate ) ). This assumes that you have a field called CreationDate which is simply an autoentered date created field.

Now create a self-relationship (Self by MonthKey), sorted in descending order by SequenceNumber.

Finally make your SequenceNumber a number field with an auto-entered calculation something like this : If ( IsValid ( Self by MonthKey::SequenceNumber ), Self by MonthKey::SequenceNumber + 1, 1 ).

------------------

=-=-=-=-=-=-=-=-=-=-=-=-=

Kurt Knippel

Consultant

Database Resources

mailto:[email protected]

http://www.database-resources.com

=-=-=-=-=-=-=-=-=-=-=-=-=

This topic is 8800 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.