How do you use NocoDB? Inventory system? CRM? Invoicing system? In any case, with these system, you may want to create your own record id in the way you want, example with combination of years and prefix.
Let’s assume that for this number: SO-2023-00001, if we break it up, it will be:
- SO – the prefix
- 2023 – Current year
- 00001 – 5 digit running number with leading zero.
Here is the actions in NocoDB:
1. On the data sheet it self, create a formula column, let’s call it Invoice Number
2. Enter the below formula:
CONCAT("SO-", LEFT(NOW(), 4), "-", REPEAT("0", (5 - LEN({Id}))), {Id})
3. To break it up, it mean
- CONCAT: The function to combine more than 1 strings together
- LEFT (NOW(), 4): NOW() function will return current date in the format of YYYY-MM-DD HH:MM:SS, and LEFT function to return 4 character from left, in this case, it is the year.
- REPEAT: Repeating add characters based on the length
- 5 – LEN({Id}): The maximum length of the running number is 5, I am using this this to deduct of the existing length of the running number, to determine how many leading zero do we need
4. Set the invoice number column to display value, and yup, you are done.
