How to reset invoice number at the beginning of financial year

Create a table to store the current invoice number. This table should have at least two columns: one for the financial year (e.g., 2021, 2022) and one for the current invoice number.

CREATE TABLE InvoiceNumbers (
  Year INT NOT NULL,
  Number INT NOT NULL
);

Insert a row into the table for the current financial year, with the invoice number set to 1.

INSERT INTO InvoiceNumbers (Year, Number) VALUES (2022, 1);

When you need to generate a new invoice number, select the current number from the table and increment it by 1. Then, update the table with the new number.

UPDATE InvoiceNumbers SET Number = Number + 1 WHERE Year = 2022;

This method allows you to reset the invoice number to 1 at the beginning of each financial year by simply inserting a new row into the table with the current year and a number of 1.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty3y ago
Create a table to store the current invoice number. This table should have at least two columns: one for the financial year (e.g., 2021, 2022) and one for the current invoice number.
```
CREATE TABLE InvoiceNumbers (
  Year INT NOT NULL,
  Number INT NOT NULL
);
```
Insert a row into the table for the current financial year, with the invoice number set to 1.
```
INSERT INTO InvoiceNumbers (Year, Number) VALUES (2022, 1);
```
When you need to generate a new invoice number, select the current number from the table and increment it by 1. Then, update the table with the new number.
```
UPDATE InvoiceNumbers SET Number = Number + 1 WHERE Year = 2022;
```
This method allows you to reset the invoice number to 1 at the beginning of each financial year by simply inserting a new row into the table with the current year and a number of 1.
+1