Auto Reset Serial Number Based On Date

[j4jason83]

I am trying to figure out how to make my script check the year and when the year changes, (ex 2010), it resets the auto incremental number.

I tried the following:

If [Year (Get (CurrentDate)) > Year ( Invoices::Date_Created )]

Set Next Serial Value [invoices:Invoice_ID_Counter; 1001]

End If

But it resets everytime I open up my invoices layout. I want it to only reset when the year changes from 2009 to 2010, 2010 to 2011, etc.

Any help would be greatly appreciated.

[Peter (duksis3)]

Instead of full Date use 1 or 2 year right digits and use "equal":

If (Right (Year(GetCurrentDate);2) = Right(Year(Invoices::Date_Created);2)

or

just compare Years:

If (Year(GetCurrentDate) =Year(Invoices::Date_Created)

But there can be another options you must take into account - like browsing through older records.

Edited September 29, 2009 by Guest

[IdealData]

There's nothing wrong with your test - but you are most likely applying to the wrong record. Don't you want to test against the LAST record in the invoices table?

You must ensure that a new invoice record (using the new year) is created after the test otherwise the next test will also yield a subsequent serial increment.

[j4jason83]

I tried this and it works better than what I was using as now it doesn't reset every time I open up the invoices layout, but it also doesn't reset when I change the computer date to Jan 1 2010.

I dont understand why I would use an "=" sign instead of the ">" sign. Shouldn't it be saying that if the current year is greater than year of Invoice date, reset?

[j4jason83]

I think you are right. I need to some how tell it to grab date and get the year from the last invoice in my db. What is the best way to accomplish this?

You also mentioned that I need to do the test before I create a new record. I have it set up right now to run the script when I switch to that layout. Is this the best way to do this or should I do it another way?

[comment]

I believe doing this as a part of the startup script should be sufficient (assuming you close for New Year).

That is, if you want to do this at all - there are all kinds of unexpected pitfalls to consider, for example: what happens if in January someone backdates an invoice to December? See also:

http://fmforums.com/forum/showtopic.php?tid/183166/post/234219/#234219

[Peter (duksis3)]

Practically I have never used automatic reset to 0 when the new year begin. Typically on the years last working day I go through ALL databases to fix years current situation, make hard backup to the CD and prepare all for next year. And then I reset all necessary data.

But - for automatic reset - put mentioned on startup script.