Use existing numbers in a field and then auto increment new entries

I am wanting to import data that has a key field (Sub_Number) - this field is used as a connection to another table (Transactions)

Ideally I would then want new entries in Knack to start at a number higher than the largest current entry and retain existing data

The only example I can see for using a starting number renumbers all the existing records (Which would break the connection)

If I use a new auto increment field to create a new key this wont relate to the connection for existing data

Browsing through other entries I have found an alternative method which seems to work and does not use autoincrement fileds

In our case MainObjectID is Subscription_Number (and is a number filed not auto increment)and we started with NextID at 180000

I have tested this in a test DB and it works the firt new record becomes 180001 etc

Paul November 22, 2019 21:52 (Edited 2 months ago)

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.