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.