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
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?
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"))
PS: make sure to make a backup first for safety.
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!
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)
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.
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.
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.
Hmm... It sort of works but here are the issues I am left with.
See screen shots too.
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').
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.
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.
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.