Help creating Invoice Numbers
I am wanting to set up a unique invoice number that is made up of two elements and I dont know if this can be done.
I am wanting to auto number clients simply each client is a new number but then sessions count up from one.
So I am currently on client no 510 , but the second part of my invoice number is made up of how many invoices have already been sent. So the invoice for the first session would look like (client)510-(invoice)1 , second invoice 510-2 and so on.
My next client would be 511 and first session would create an invoice 511-1 . I am currently using an excel spreadsheet but have just moved all my clients into ninox and its auto number invoicing is good but id like to stick with the method I was using in excel.
Could someone please tell me if this possible?
1) How do I simply get the clients "client no" to auto count on creating of a new client. I simply cant make it Ninox do it.
2) How can I make my invoice no use the first element as "client no" and the second element an increasing number starting at 1 to show how many sessions which in turn invoices they have had as part of the invoice number.
The database I am using is a variation of the Invoice template database.
Many Thanks
12 replies
-
In the invoice table creat a new field "My Invoice No". For the field "Customer" set a trigger "onChange with this formula:
–––
let myCreate := 'Invoice Date';
let myCustomer := Customer.Id;
let myCount := cnt(select Invoice where 'Invoice Date' <= myCreate and Customer.Id = myCustomer) + 1;
'Unique invoice No' := myCustomer.Id + "-" + myCount
–––For existing invoices use "Update multple records" (gear menu) and give this formula to the new field "My Invoice No":
–––
let myCreate := 'Invoice Date';
let myCustomer := Customer.Id;
let myCount := cnt(select Invoice where 'Invoice Date' <= myCreate and Customer.Id = myCustomer) + 1;
myCustomer.Id + "-" + myCount
–––Birger
-
Hi no matter what I try I cannot get the above to work, has anybody got any ideas?
-
I tested the Birger's formulas and they're working fine!
So, please try again and follow the steps exactly...
-
I am a novice with Ninox and have managed to get this working now , although when creating an invoice the first invoice number created starts at ***-2 so instead of created say invoice 518-1 which is what I want to start at ***-1 it automatically starts with 518-2. If the invoice is changed to 518-1 and then another invoice created it display invoice 518-3. Again any help would be apprecitated. Thanks
-
I have changed the formula to below which still works.
let myClient := Client.Id;
let myCount := cnt(select Invoice where Client.Id = myClient) + 1;
'Invoice No' := myClient.Id + "-" + myCountBut first invoice no is ***-2
-
You can try another approach:
Create a formula field 'Count_Inv' -> "count(Invoice.Id)" in Customer table.
and the trigger:
let myClient := Customer.Id;
'Invoice No' := myClient.Id + "-" + Customer.Count_InvNick
-
I would like to do something similar.
I have a system in filemaker that takes our the next quote number in sequence, I have three fields for this database, the Quote# which is the field I want to increase by 1, the project name and the Customer the quote is for.
In Filemaker it is easy, I check a Serial Number box, select a radio button On Creation, tell it to increment by 1 and then fill in the starting field, such as SJQ18000, when I create a new entry it creates SJQ18001
How do I do this and when can Ninox implement something like the filemaker system?
Thanks.
-
Hi,
As I see it the solution from Birger is all the same: Once set the formula at trigger on create, it will do what you want.
Best, Jörg
-
I agree with info@scojet.com above - why can we not have a simple way to insert this as he has described?
-
I agree with marcel@ontdel.com
-
Ditto
-
@mosgo: If you're concerned that the invoice # starts at 2 instead of 1, why not leave out the addition of 1 in the formula?
instead of this
let myClient := Client.Id;
let myCount := cnt(select Invoice where Client.Id = myClient) + 1;
'Invoice No' := myClient.Id + "-" + myCount
use
let myClient := Client.Id;
let myCount := cnt(select Invoice where Client.Id = myClient);
'Invoice No' := myClient.Id + "-" + myCount
I guess the off-by-one-error results from the fact that the formula is used in an onCreate-Trigger. So the record is probably already created, when the cnt(...) function is called. Consequently, the result of this function call includes the just created invoice, which means that it is 1 when you create the first invoice for this client. Adding 1 to it, gives you an invoice # of 2.
Just my guess
Content aside
- 4 yrs agoLast active
- 12Replies
- 6546Views