I’m a beginner in SQL and I tried looking for an answer without any luck.
I would like to generate invoice numbers automatically with the following distinctions:
- I have 2 different structures invoicing
- I would like to avoid any numbering issue, for example not having a missing invoice number if something gets altered (ex: 1, 2, 4, 5, 8, 9 …)
- I would like them to be reset each year, so the numbering starts back to 1 every year for each structure :
ex: ENG_2022_1, ENG_2022_2 … ENG_2023_1
ex: FR_2022_1, FR_2022_2, FR_2022_3, … FR_2023_1
I have thought of setting a field with auto-increment, but that wouldn’t work with the structure and year distinctions unless there’s a workaround?
The other solution would be to get the year and structure before invoicing, and comparing it with a SQL MAX of the invoice number, before numbering but i’m not sure how good that solution?
Any help would be greatly appreciated