Max value +1

Help Center Get Answers
1

Hi, I have a table with field ID, now there are random values, how can automatically fill ID, when somebody add new record?

ID:=select max(ID) + 1; 

1 Like
2

Hi Brad, 

This worked perfectly. It took some thinking so I'll just expand on your answer with the steps I took.

1. Created an NextID object - 2 fields:   lastID (number), nextID (formula last Id + 1)

2. Add one record to to the NextID object and set the lastID field

3. Add a connection from the Main object to the NextId object (many to one)

4. Set the default on the connection field to the first option

5. On the "add form" place the connection field on the form. Untick allow users to add new options

6. I updated the form rules to first set the Main object ID to NextID.nextID 

6.  the I set another form rule to set NextID.lastID to NextID.nextID

 

One thing I have found was the Main object connection field has to be show on the add form otherwise it does not seem to work. I can't seem to set the field to read only, so its not a elegant layout but it works.

1 Like
3

We do this quite a lot to retrieve a next invoice number, or for estimates - relies on having your table in question connected to a parent object.

  1. In the parent, add a Number calculation field for Maximum on ID.
  2. In the parent, add a Number calculation for Max+1
  3. In your form to add a new record in your table, set ID to the connected field created in No.2

This might not work if you don't have a parent connection though.

4

But the auto increment will mess up the previous ID's which are random.

Is there a way to save the auto increment to another field when saving a new record?

5

You can do it with auto increment field, all the information for this reference:

https://learn.knack.com/article/08gqoa7kqn-start-an-auto-increment-field-from-any-number

 

6

+1

7

This link is broken / outdated

8

Thanks for calling that out! I updated the link on behalf of that user. You can find this article and more in our new knowledge base here: Start an Auto Increment Field from Any Number - Knack Knowledge Base.