If you’re managing documents, invoices, or records in AppSheet, you may need a custom numbering system that resets at the beginning of each month. For instance, you might want your document numbers to follow a format like KKM/YYYY/MM/1 and automatically reset to 1 when the next month arrives. In this guide, we’ll walk you through the steps of creating a custom document numbering system that resets monthly in AppSheet, along with troubleshooting common issues.
Why Use a Monthly Reset Document Numbering System?
A monthly reset document numbering system can be highly beneficial when you:
- Manage a high volume of records or transactions.
- Want easy tracking of documents on a month-by-month basis.
- Need to comply with regulatory or internal requirements that specify a custom numbering format.
By setting this up in AppSheet, you’ll automate your workflow and eliminate the need to manually track document numbers, saving time and reducing errors.
First, you’ll need to have a data table in AppSheet where each document entry is stored. For this example, we’ll assume the table has the following columns:
- Document_ID (Text): The full document number like
KKM/2024/9/1. - Creation_Date (Date): The date the document was created.
- Year (Number): Extracted from
Creation_Date. - Month (Number): Extracted from
Creation_Date. - Document_No (Number): The running document number that resets each month.
Here’s the formula for the Document_ID:
Now that you have the running Document_No for each month, you can format your Document_ID column to include the year, month, and number in the correct format.
Here’s the expression for Document_No:
