0

Create tables with autonumbering with ease.

Since i see a lot of questions passing by on this forum about autonumbering I desided to make an template with a little different approach.

Just posted it in the Webinar EN 2019 team. Check it out in 98_1_AutoNumber

25 replies

null
    • Ninox partner
    • RoSoft_Steven.1
    • 4 yrs ago
    • Reported - view

    For those who don't have an cloud account, here's the link to download it manually:

    https://www.dropbox.com/s/387bqz4vwt1itck/AutoNumber.ninox?dl=0

    enjoy 😉

    • Sean
    • 4 yrs ago
    • Reported - view

    Thanks Steven. Now, if users would kindly use the "Search forum..." function before posting questions that have already been answered! 😏

    • Agassi
    • 4 yrs ago
    • Reported - view

    Hello!

    Thanks for the code and sample, its super helpful!!!

    It works fine when a new record is created. 

     

    But i tried to implement it to fill the autonumber field in multiple existing records, but when i use that code in "Update multiple records... " screen, it gives

    "This formula may not modify data".

    The code is:

    'Numero Factura' := text(record(Counters,1).Result);

    let c := record(Counters,1).Counter + 1;

    record(Counters,1).(Counter := c);

    When i remove the line: record(Counters,1).(Counter := c); the message does not appear, but obviously it will not increase the number in Counters table. 

    How can i achieve filling the field of existing records with incrementing numbers? 

    • Ninox partner
    • RoSoft_Steven.1
    • 4 yrs ago
    • Reported - view

    Hi,

    The best way to do this is to make use of a temporary button and after the execution you can delete this button again.

    in this example i have a table called Articles where i put a field Number.

    for i in select Articles do
    record(Articles,i).(Number := "ArtNo: " + format(number(i), "000"))
    end

    PS: make sure to make a backup first for safety.

    Steven

    • Agassi
    • 4 yrs ago
    • Reported - view

    Steven thank you very much for your answer!

    But I got some questions, please:

    1) where ArtNo comes from?

    2) I don't understand if this solution is independent from your first solution in the first post, or it is an addition?

    3) And because I thought that there is a way to do it through  "Update multiple records... " screen, i did not explain in detail that i need to autonumber only records filtered by date range. So how to apply this or another solution to filtered records?

    Sorry for many questions and thanks again!

    • Ninox partner
    • RoSoft_Steven.1
    • 4 yrs ago
    • Reported - view

    1) Art No is a pefix for the number in Number field eg "Art No : 001"

    2) its an addition to use one time to populate the emty fields. Once the field is populated the trigger on create field does the job

    3) Not sure if 'for i in select Articles order by date do' ... will work(I'm not in office now to try) 

    Steven

    • Nick
    • 4 yrs ago
    • Reported - view

    Thanks Steven!

    Any workaround for when we duplicate records?

    • Ninox partner
    • RoSoft_Steven.1
    • 4 yrs ago
    • Reported - view

    Not yet, do you have an idea Nick? Maybe with a button to duplicate a record and then trigger the counter? That could work I think.

    • Nick
    • 4 yrs ago
    • Reported - view

    Hi Steven,

    I'm looking for something automated.
    I work with the mac application most of the time using Command + D and I hate that you have to do the most work with the mouse ... 🤔

    • Ninox partner
    • RoSoft_Steven.1
    • 4 yrs ago
    • Reported - view

    Well, maybe it's time to buy a windows computer with touchscreen and subscribe for a cloud account. No mouse needed...

    just kidding hey... Honesty, i have no idea right now Nick, sorry I can't help you with that.

    Maybe it could be a CR...

    Maybe someone else?

    • PAGE
    • Bill_Page
    • 3 yrs ago
    • Reported - view

    Newbie to Ninox so on trial cloud version to try out. 

    Used to be an FMP developer back in the early 2000's and a decade before but gave that up to focus on UX/UI on web-apps so very rusty and a bit lost. 

    So question is, how to I add an auto (unique) incremental numbering to records upon creation? I have tried and failed, read this thread and tried again but am feeling very useless so can someone help me do this very simple task, thank you.

    • Dorich
    • 3 yrs ago
    • Reported - view

    Not sure if this is what you want but Page 4 of the download manual gives the following:

    Option 1: Create a trigger (“on create”) on table level like this:

    'Invoice Number' := max((select Invoice).'Invoice Number') + 1

    I'm a 5 day old neophyte so I may have this wrong but I beleive the following can be explained as

    First the basic structure is the usual programming structure of count=count+1 

    In addition you have an instruction to look for the maximum value of the field in the specified table, ie in the table "Invoice" what is the highest vale of the field. The calculation then adds one on the creation of a new record.

    • PAGE
    • Bill_Page
    • 3 yrs ago
    • Reported - view

    Thanks, however I am getting increments of 10 and the Suffix is lost too :(

    On Creation I have a trigger: 'Customer ID' := max((select 'Customer Data').'Customer ID') + 1

    So I am doing something very wrong I guess. Any suggestions execpt go back to FMP v11x :)

    Screen Shot 2020-06-09 at 14.24.54

    • Dorich
    • 3 yrs ago
    • Reported - view

    I have experienced the same issue, incrementing by 10 that is. However, I don't recall the solution. Give me another day and I'll retrace my steps to see if I can recreate the symptom.

    • PAGE
    • Bill_Page
    • 3 yrs ago
    • Reported - view

    OK - Changed Customer ID from Text to Number field and now it works. 

    Just have to work out how to add the suffix GP-00 before each number now :)

    • Dorich
    • 3 yrs ago
    • Reported - view

    Try this

    Select a record

    Open the "Edit Column' Window

    Click on the appropriate field

    Click into the formual field

    Edit the formula so you get something like but using your parameters

    "Small Box" + " - " + text('Small Box Number')

    This will result in "Small Box - 1"

    Hopefully I've remembered the steps correctly.

    • PAGE
    • Bill_Page
    • 3 yrs ago
    • Reported - view

    Hmm... It sort of works but here are the issues I am left with.

    See screen shots too.

    Screen Shot 2020-06-09 at 16.04.38

    Right click on Customer ID colum header (field) and the above displays.

    Then I click on the formula icon and the place to enter it opens up, so I enter: "GP" + " - " + text('Customer ID').

    Screen Shot 2020-06-09 at 16.04.30

    Next I get a new column with the name of the column header the same as the formula (Oops!)

    But, I do get GP- in front of the  number.

    Any idea how to show the results but not the strange column header name with formula.

    Thanks very much. 

    • Dorich
    • 3 yrs ago
    • Reported - view

    I think this will work:

    Don't click on the panel header.

    Go back to the pane where you entered the formula. In the line above the formula there is a field for "Name".

    In that field type in the name you want to use for the column header.

    • Dorich
    • 3 yrs ago
    • Reported - view

    Apparently I haven't mastered uploading images, but if the instructions are not clear let me know and i'll master uploading images. :-)

    • PAGE
    • Bill_Page
    • 3 yrs ago
    • Reported - view

    Thanks but I have totally broken it now... Time to go back to the drawing board or Excel :(

    • PAGE
    • Bill_Page
    • 3 yrs ago
    • Reported - view

    Think I have done it (Hooray!)

    Trigger: 'Customer ID' := max((select 'Customer Data').'Customer ID') + 1

    Then Field 'Customer ID' is a NUMBER with Prefix 'GP-'

    This returns a new record with Customer ID [GP-1] [GP-2] etc.

    Still cannot get a couple of leading 0's after the '-' but it will do for now.

    Thanks for all your help.

    • Choices_Software_Dean
    • 3 yrs ago
    • Reported - view

    Try it with a formula field and a number field:

    Customer ID Formula

    Customer ID Number

    Trigger on Create:

    'Customer ID Number' := max((select Table1).'Customer ID Number') + 1

    Formula in 'Customer ID Formula' field:

    "GP" + " - 00" + text('Customer ID Number')

    When it is working the way you want, you can go back and change the field names to whatever you want.

    • Dorich
    • 3 yrs ago
    • Reported - view

    @ Bill Page - glad you got it sorted.

    @ Choices Software - Thanks I had the same question about leading zeros. Thats twice in two days you've solved my problem 

    • Choices_Software_Dean
    • 3 yrs ago
    • Reported - view

    Oops. I should have typed:

    "GP-00" + text('Customer ID Number')

    • PAGE
    • Bill_Page
    • 3 yrs ago
    • Reported - view

    Thanks for this Dean adn Websearcher. I am sure I am going to have more questions later as I realign myself with a bit of DB dev again.

Content aside

  • 3 yrs agoLast active
  • 25Replies
  • 5027Views