OneRedmouse Posted March 18, 2024 Posted March 18, 2024 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 Posted March 18, 2024 Posted March 18, 2024 (edited) 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, 2024 by comment
OneRedmouse Posted March 18, 2024 Author Posted March 18, 2024 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
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now