Hello all,
Why am I not able to add an extra AutoValue integer field in the Invoices table?
I want to create an InvoiceNumber field. Should go up by one on every new invoice, but when starting a new year, it should be reset to zero. I already have the InvoiceID, which is unique, so I don’t see why this doesn’t work.
Thanks.
Hello,
There are multiple problems with your request.
As stated in the question, you start over in a new year. Auto increment fields will not duplicate values. Additionally, an auto increment number is automatically part of the key in a table and there is only one auto increment field allowed in a table.
It is likely you would need to use macros to accomplish what you want. This is complicated by the fact of starting over in a new year. You have not stated the database you are using. The posted sample is HSQLDB embedded v1.8. That database (and many others) has the ability to create SEQUENCE generators. Still will need macros but this can be of help.
See → CREATE SEQUENCE
I never realized it would be so difficult to achieve this.
My first idea was to start with a blank database every year but that way the customers table would be empty which is not what I wanted.
Then I thought to create a button that would create a completely new Invoice table. But with the relations and so I’m afraid my sql knowledge doesn’t reach far enough.
I see if I can find another work around.
My next idea was to start InvoiceID with 2020000 being the year 2020 and maximum 999 invoices each year.
When the macro tries to save InvoiceID into the global integer I get this error:
Inadmissible value or data type.
Overflow.
You can do much with macros providing you understand how to write them using the Uno API in LO. That requires much study and understanding. Some macros are simple but your comment is puzzling. How do I respond to something you never posted? What code are you using? How are you using it? Where is a sample? There are probably more questions depending on the situation.