Need help with producing a client number

[Kimberley Hoffman]

Hi Brendan and all who are fit in producing great forms.

I am working on forms from a databank to produce an invoice and a delivery notice to bill for portraits I have drawn.

German financial laws state that I must give each person an individual client number and unique invoice numbers to identify where the money is coming from.

My usual way of assigning client numbers and invoices is the date that they became my client backwards ie 20181002 + the three important initials of the business. That is the client number. The project number is the number of the project that I am doing for the client. A first time project gets the number 1, the following project the number 2, the third project the number 3 and so on. If more than one invoice is necessary for a project than I add a ‘.’ and the sequential number of the invoice. The reason I use this method is because I can identify how long I have had the client, but also it is nobody’s business how many invoices I have actually written to other clients.

In short, my invoice number might look like this (using my own name as client name):
20181002KAH-1 Projectname
or for sequential invoices
20181002KAH-1.1 Projectname
20181002KAH-1.2 Projectname

Because of the amount of B2C invoices I will be generating, I am hoping to simplify this. I realize it is a calculation, but how do I write it? I can put in the date backwards, but how do I generate the initials adn the sequential invoice numbers? Or would it simply be more effective to manually assign all?

Thanks
Kimberley

[Brendan]

Hi Kimberly,

What you can do is first create a Number field and enable the Auto-Increment function on it. That could be your Project Number field. That’ll give you your incrementing number as each record is created.

Then use a Calculation field with a formula like this:

DATE(Client Enroll Date, “yyyyMMdd”) + Client Initials + Project Number + Project Name

Make sure you set the Result Type to Text since you’re concatenating the values from those different fields together.

That should do it hopefully.

Thanks!

Brendan

[Author]

Posts