Generating sequenced invoice-numbers in base

[5tefan]

hello,

I’m currently working on a small database for my invoices. For legal reasons my invoices have to be numbered sequentially (1, 2, 3, 4…) and also I’d like to start from 1 for each new year. My invoices are stored in a table named “rechnungen”. I cannot simply use the automatically created ID of a new entry as they don’t reflect my year-scheme and also, if I delete an invoice, the next entry (a new invoice) won’t get the index of the previously deleted as the index will be auto-incremented, regardless from the fact that the previous entry has been deleted. So far, so good.

As a solution I was thinking the following: Let the invoice-number be created from the sum of invoices for a certain year + 1 (invoice-numbering should start at 1). The following sql-statement basically gave me the result that I was looking for but I have no idea how to apply it to a form so the result gets inserted into a form-field and afterwards stored in the database:

SELECT COUNT("ID") FROM "rechnungen" WHERE YEAR("date") = YEAR(CURRENT_TIMESTAMP)

That statement would calculate a number to which I would have to add 1 to get me the number of the invoice that I’m currently writing. “date” is a column in my db where the dates of my invoices get stored. Ideally I’d like to have the condition based on the date that I’m entering in the date-field of the invoice-form that I’m using to write my current invoice but I don’t know how to achieve that in an sql-statement.

However, my approach didn’t work so far. No invoice-nr. gets displayed if I add the statement under the data-tab of the combo-box that supposed to display the invoice-nr. and no number gets stored in the database. I fail to understand how and where to write an INSERT statement that would store the record in my database.

I hope my question is understandable to someone and advice is highly appreciated.

[5tefan]

Update: I have “kind of” found a solution using a macro. However, my problem isn’t totally solved: dynamically set value of a form-field not inserted into database

[EasyTrieve]

@AlexKemp should have not closed this I think. But since he did, here is my answer in a brief comment: Create a simple table of year and invoice offsets. For the first year the offset is 0. For the 2nd year the offset is the total of invoices in the first year, etc. Then allow auto_increment to give you ID numbers, but when you report the Invoice # use ID minus the looked up offset for the given year.

[iplaw67]

It would be interesting to see how the (I am assuming German) tax authorities would react to a numbering system for invoices where you are allowed to delete them at will. Most regulated or approved billing software packages do not allow this, even if you can cancel the invoice. In France, if the tax inspector knew I could delete and renumber my invoices at will, I would have some serious explaining to do. Anyway, it seems to me that you are looking for a trigger. Unfortunately, these are not supported via the UI or even via update queries directly in the default hsqldb engine that comes with LO Base, so currently the easiest way around this is via macro programming. Alternatively, you could try to create a trigger within a Java class and have that loaded and bound to your ODB file via an event that would fire when you did a certain action. Whether or not this is actually practically feasible is another matter.

Even better, switch to a db backend that allows you to set triggers via SQL on the server side.