0

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

null
    • Birger_H
    • 6 yrs ago
    • Reported - view

    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

    • mosgo
    • 6 yrs ago
    • Reported - view

    Hi no matter what I try I cannot get the above to work, has anybody got any ideas?

    • Nick
    • 6 yrs ago
    • Reported - view

    I tested the Birger's formulas and they're working fine!

    So, please try again and follow the steps exactly...

    • mosgo
    • 6 yrs ago
    • Reported - view

    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

    • mosgo
    • 6 yrs ago
    • Reported - view

    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 + "-" + myCount

    But first invoice no is ***-2 

    • Nick
    • 6 yrs ago
    • Reported - view

    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_Inv

     

    Nick

    • info.8
    • 6 yrs ago
    • Reported - view

    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.

    • Support
    • 6 yrs ago
    • Reported - view

    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

    • marcel
    • 6 yrs ago
    • Reported - view

    I agree with info@scojet.com above - why can we not have a simple way to insert this as he has described?

    • CET di Casara Manuel
    • ManuelllX
    • 5 yrs ago
    • Reported - view

    I agree with marcel@ontdel.com

    • darrenjosborne
    • 4 yrs ago
    • Reported - view

    Ditto

    • Christian.1
    • 4 yrs ago
    • Reported - view

    @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

Login to reply

Content aside

  • 4 yrs agoLast active
  • 12Replies
  • 6553Views