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