Create tables with autonumbering with ease.

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

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? 

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!

Thanks Steven!

Any workaround for when we duplicate records?

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 ...

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.

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 :)

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.

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 :)

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.

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. 

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.

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

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

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.

@ 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 

Oops. I should have typed:

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

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.