Reset Invoice Number for Month (script issue)

[ADJUDD1402]

HI all, I have a script (below) which puts in an invoice number. This is then formatted YYMM## into a calculation field.

I want the numbers to rest each month. I have done what I think is the hard bit in setting this up. In terms of the numbers reset, they get added in the right order etc. However, as I want to user to have control over the invoice date, I have set the script trigger for my invoice number to run when the date is entered. Thus bringing everything together. The problem is...

When a user clicks the date the script runs fine, if the user has entered the wrong date, or wants to change it then the script runs again, thus adding a new number to the same invoice. It all gets out of order then.

Could someone let me know what an "If" situation might be to stop this happening?

Thank you!

 

[Ocean West]

based your graph this number is a "reference" and not used a primary key, which is a good thing. 

-

Try this - it basically only runs when no invoice number has been established.

If [  isEmpty ( invoices::invoice number ) ] 
Set Field [ your logic ] 
Commit Records/Requests [no dialog]
End If

-

alternatively you could also probably do much of this with a SQL Select without needing the the extra fields or table occurrences.

-

another option is that many invoice systems may already contain a table that is used for month end summary totals a table which essentially a record for each YYYYMM and contains number fields set via a script which includes your month end sales totals, makes for faster totals & reports ( depends on quantity of items to subtotal ) which can be faster then using summary fields and sub summary reports.  this table could contain the "sequence" number, however when incrementing the number proper transactional method in your script is needed so that multiple users don't collide with each other. 

-

there are other options...

Using the script step  Set Next Serial Value you could run a script on the server at 12:00 AM the every day testing to see if is the 1st of any given month then just reset the value to 1. 

 

hope this helps.

 

[comment]

I want the numbers to rest each month.

I would advise you to abandon this idea. It is quite easy to get duplicates when you're using s_max_invoice +1 to number your invoices. All it takes is two users creating a new invoice roughly at the same time. And that's before you allow users to backdate new invoices or change the dates of existing ones.