0

Need a field formula that inserts a unique and sequential number?

What is the formula to insert or create a unique and sequential number for a record? I need that number to never change by itself, in other words, a customer's account number never changes and is unique. The built-in Id field does not do what I need since Ninox changes those numbers when synced across devices.

34replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Good to know.  Thanks for the update.

    Happy Invoicing.

     

    Tim

    Like
    • Rico
    • Rico
    • 3 yrs ago
    • Reported - view

    Related to the requests for an automatically incremented number, these people seem to make it simple:

    https://support.knack.com/hc/en-us/articles/230231768-Start-an-Auto-Increment-Field-from-Any-Number

    Like
    • Rico
    • Rico
    • 3 yrs ago
    • Reported - view

    By the way, my post wasn't meant to plug that product, just to show how a simple implementation of a sequential number.

    Like
  • Problem is when you make use of the record ID is when you delete a record the numbers wouldn't be in a sequence annymore. So 2019+recordID is not a good idea.

    Like
  • Is it possible to make TFH001 sequential then TFH002, I understand the sequential scriptt but I ideally need it with the suffix TFH

    Like
  • Hi Paul, 

    Please download the template "Invoices" via the button "New database". There you will find the formula in the table properties of the invoice table in the option "Trigger after create". 

    In that formula you can exchange the suffix.

    Kind regards, Jörg

    Like
    • Mats
    • Music
    • Mats
    • 3 yrs ago
    • Reported - view

    I also have som problems with the Invoice numbering. I want number my invoices without the year-prefix (e.g. 120, 121, 122 etc...) and start on a specified number. 

    When inserting the code mintioned here earlier 

    let before := max((Table1).Invoice No.);
    Invoice No. := before + 1 

    To make sure there aren'r anything I've messed up I tried this with a new epty Invoice template.

    When using the code above it results in 1, 11, 111, 1111, 11111 etc. 

    How do I correct that and how I define a start value?

    Like
  • Mats before must be a number     let before := number (max((Table1).Invoice No.));
    Invoice No. := before + 1 

    Like
Like Follow
  • 3 yrs agoLast active
  • 34Replies
  • 13574Views