Currently i am finetuning the invoice module of my application. One very important thing is the invoice number. Since this has to follow some rules by law, like not skipping numbers (1 —> 3 —> 5, must be 1 —> 2 —> 3), and not having to much gaps. So when a user deletes a invoice, there will be a gap in the invoice numbers sequence.
Most likely some of you have also faced this issue, so i am very curious how you handled this. Please share your ideas and solutions, so that i have an idea. And we fill the community with great information.
I’m not very familiar with the rules and regulations for invoice numbers, so perhaps somebody else can give more effective tips from experience, but my initial thought was: should users be able to delete invoices in the first place? I find that often applications have some data that you don’t want users to remove and it sounds like invoices might be in that category for sake of traceability. In case you don’t want users to see all invoices forever, you could always include an archiving option and create a filter not to show those invoices.Jochem SpronkModerator
If you do not want to skip numbers, then you should have a seperate dataitem with a list where you store which invoice numbers you have and which you haven’t used (on your sent invoice).
In the case that you delete an invoice, you’ll search for the lowest invoicenumber and reuse that number. In this order, you will never skip an invoice number.
So, in steps:
1) Create data item “invoice number”.
2) Make a reference to data item invoice.
3) Create fields: invoicenumber (text), status (picklist) with status open and closed.
4) Create flowpart and search for lowest open invoicenumber.
5) Update this number to your invoicenumber.
6) If you delete an invoice, set the corresponding invoicenumber to open. Be aware: update before deleting the invoice!
Will this help?
Thanks for your response!
The solution you offered Jochem, will give a new problem.
Every month an automation flow will run, and in one batch will create 10 invoices (with number 1-10).
If the supplier checks all invoices, and delete for example number 6 this number will be stored.
But this number will only be filled, when the automation flow will run again. So then invoice number 6 is later then number 7-10.
What actually needs to happen, is when a supplier deletes number 6. All the invoices that haven’t been captured yet need to be updated so that number 6 gets filled again. Then the invoices will only go until number 9 or something like that. The only issue is that when a user deletes number 6, and numbers 7,8,9 are captured already a new issue arises.
I will dive more into this. But maybe you guys have a good solution.
I think you can reshuffle the numbers as you described, but yes, if certain numbers are fixed you’ll end up with non-sequential numbers like in Jochem’s example.
Would it be possible for you to delay adding a number to the invoice? I’m thinking of a case where a user can review an invoice and then either send it or delete it. When the user reviews it, it is still unnumbered. When the user then sends it, after which you probably won’t allow the user to delete it anymore, it is assigned the next number.
Yes, there is a text function called Combine which will show the number without thousand separators and you can cheat a bit with it. It takes two properties of any type as parameters, but for one of the parameters you can just pick ‘Specific value’ and not fill in anything 😉 Make sure though that you pick the number from a data item and not the result of a calculation or a number input of your flow part; if you do that you will end up with a lot of decimal positions. Please let me know if that last part isn’t clear and you can’t get the number without a bunch of decimals.Jesse MeijersKeymaster
I thought I’d weigh in to this conversation, as we have invoicing functionality in our own customers (CRM) application. This supports the way you want to (or rather, have to) handle invoice numbers. As a bonus, it also supports crediting invoices 🙂
Please note that ‘Deleting an invoice’ is considered fraud by law in most (if not all) countries. Once an invoice is generated, it should be credited in order to be removed instead. This also requires some more administrative work in the accounting, which is why we separate orders from invoice generation.
Here’s how it works. First, we have a data item called Sales order which represents any sales order. In our case these are made on the website, then sent through Zapier, but they can be created in any other way too.
We only use a single data item, which represents sales orders and contains the invoice information:
Note that we use the order date as the invoice date. Should you require a separate invoice date you can always create one. These fields are all empty when the item is created. We only fill them when we want to invoice the sales order or credit it.
Let’s start with the invoice number; this is generated by a flow part called Set sales order invoice number. It’s rather intricate, but you could simplify it for your needs most likely:
This flow part takes a Sales order as input and then first determines whether the invoice has already been invoiced (by checking whether the invoice number is empty). If not, it finds the sales order with the highest invoice number by finding the sales order with a rule that says Invoice number is not empty and then sorts them by invoice number from highest to lowest. It searches for 1 sales order.
The calculation after that just adds 1 to the highest invoice number. The other path simply takes the invoice number from the input sales order. We do this to make sure we can use the result of the calculation Invoice number after the decision. We store this number in the sales order.
The next bit is not very necessary perhaps in your case. We calculate the length of the invoice number and then add leading zeros to it to make it always have length 10. For example, invoice number 9 would become 0000000009 and invoice number 1023154 would become 0001023154. After we do this, we add some more text in the action ‘Final invoice number as text’. In our case we make it look like this:
We do need a little trick here since the invoice number will have some periods and/or commas in it. To remove those we use the following rule:
CUT REMOVE . FROM REMOVE , FROM Invoice number as text FROM 1 FOR 10 CHARACTERS
This rule uses a lot of functions in functions, you could also use separate calculations for this.
Finally, we update the field Invoice number as text of the input sales order.
In a separate flow part called Generate invoice document for sales order we then generate a PDF document that represents the invoice and store it in the property Invoice document:
We separate these 2 flows because we want to be able to re-generate the invoice using the same data. This can be handy when you change something in the layout of your invoice. Please note that this does not change any of the values in the order, it just regenerates the document.
To credit an invoice, we use a single flow part called Credit sales order which takes 3 inputs:
The true/false can be ignored really, it’s just used to regenerate the credit note when the design changes. The sales order is the sales order we want to credit and the user is the user who presses the button to credit it. We store this just in case we get a question about it, so we know who to ask.
The flow is rather simple; it creates a credit number which is just the invoice number with an appended -C, so for example:
This is sufficient according to the Dutch tax law and I believe in most other countries too. They simply require invoices and credit notes to be able to be traced (without going into more detail on this for now).
That’s it, a complete set of data item and flow parts to generate invoices and credit them if necessary!
- You must be logged in to reply to this topic.