Decrease Field Calculated Serial Value by 1

[OneRedmouse]

Hi All

 

I have what should be a simple issue but what I am doing does not work

 

Scenario

I have an Invoices database with a button to create a new Invoice. Creating a new Invoice increments the Invoice number by one

 

Problem

Sometimes a new Invoice is created by mistake so the Invoice has to be deleted and then the user needs to manage the database, find the invoice number field and manually decrease the number by one. So I would like to automate this task

 

Solutions tried

I have created a simple one line script to decrease the value of the auto entered field serial number with odd (& incorrect) results. I have tried

Set Next Serial Value [Invoices::Invoice No ; (Invoices:Invoice No) -1]

and...

Set Next Serial Value [Invoices::Invoice No ; Max ( Invoices::Invoice No ) -1]

and...

Set Next Serial Value [Invoices:Invoice No ; -1]

 

Can anyone point out where I am going wrong please. Many thanks as always

[comment]

The best solution, IMHO, would be to avoid the problem. You can defer the issue of serial number until the record is committed. And you can defer the commit until the user is sure they want to post the invoice.

You also need to consider the legal aspects: in some jurisdictions, deleting a posted invoice and rolling the invoice numerator back would be illegal. And it is certainly not accepted accounting practice anywhere else. The proper action would be to cancel the invoice by issuing a credit note (i.e. a "counter-invoice" with negative amount). 

If all that doesn't put you off, consider the following script step:

Set Next Serial Value [ Invoices:Invoice No ; GetNextSerialValue ( "" ; GetFieldName ( Invoices:Invoice No ) ) - 1 ]

But this should be at least accompanied by some safeguards against accidental misuse; if your impetuous user presses the button twice, they will cause a duplicate invoice number to be issued. And also: what if meanwhile another user has issued the next invoice?

Proceed with care.

 

Edited March 18 by comment

[OneRedmouse]

Many thanks for the swift reply.

What you have suggested works. I obviously forgot to get the value to subtract from

I understand and take note of all your warnings. The database in question is integrated with Xero so an invoice is not committed until  posted to Xero. Once it is posted it is locked

This is really for the odd occasion when someone creates an invoice by mistake and then just needs to quickly delete it

But thanks again it is appreciated