As of today, all EU, US, and AP region apps have received an update to their Auto Increment fields. This update will greatly lessen the instances where gaps in the auto increment ID values occur.
As a reminder, auto increment fields are unique, ascending, and immutable.
For example, if three records have the ID’s of “1”, “2”, and “3”, and the record with the value of “3” is deleted, the next record created is expected to have the ID of “4”. The ID of “3” will not be reused.
If you experience issues with the values skipping unexpectedly again, please reach out to the support team via the chat widget in the Builder or by submitting this form.
Think of it this way. If this were a straight SQL autoincrement field it would work correctly with 100% certainty. That’s because SQL is A.C.I.D. compliant. Whatever they’re doing is not simply a straight SQL autoincrement. There’s something else going on peculiar to Knack.
Thank you for sharing. I apologize, the way that I phrased that could have been better! I used “greatly lessen” out of an abundance of caution in case someone runs into any further issues, but the previous issue with auto increment fields has been fixed!
Please let us know if you have any issues going forward by reaching out the support team.
Can we clarify this post? When I first read it, it seemed like the bug was that the Auto-Increment will not replace deleted values, and now it does. But after a quick test, I see that is not the case. Deleted values are not renumbered. So if you have 1, 2, 3, and delete #2, then add a number 4 record, you are still only left with records 1, 3, 4. This is my bad for not reading a little closer.
It would be nice if Auto-Numbering had a setting you could turn “on” that would Re-Number the records to keep them 1-x with no missing numbers in between (I assume you’d need to also select a sort-field in the settings so it knows what the numbers should be assigned by). So in the above example, removing one and adding another would still show the auto-number as 1, 2, 3. I’m not sure I can accurately describe all the scenarios that this is needed, because there’s many…but here’s some examples where it’s important to maintain 1-x integrity: priority lists, queueing systems, leaderboard/donor boards, and anything where order needs to be maintained after data changes. Thanks!
Correct, deleted values are not re-used and this update was not to correct that. This update corrected larger gaps that were occasionally occurring, for example, occasional random 30 record skips in auto increment values.
Thank you for sharing your request, and I have shared that with the team for consideration!
Please let the support team know if you experience any issues with your auto increment values going forward.
Hi Alex @RealEyesDisplays - hope you’re keeping well.
To avoid having missing Auto Increments I don’t allow records to be deleted, simply archived. This may not be a suitable option for you but it would avoid missing consecutive numbers.
Hey @CarlHolmes , interesting idea, I’d like to learn more.
By archiving them, are you referring to tagging them into a different table or list? Wouldn’t that also remove their number from the sequence?
To me, this would still leave open issues when records change values. For example a leaderboard or donation board. If someone donates more money that would increase their ‘rank’ in the data set, the numbering sequence would still need to be re-applied.
This is good to help me further clarify my suggestion…the second function of the auto-numbering should allow:
Numbering values to be assigned based on the selected field for sorting. For example, in the donations example, autonumbering field is added and I would select “donation amount” “highest to lowest” as the sort field/order.
Numbering would not be static on the records if the field selected receives changes. The number sequence should always follow the sorting sequence of the chosen field…therefore the number assigned to each record may change when the data set has records added, deleted, or updated.
This would allow some interesting filtering and scenarios for rules. You could supposedly find the 11th highest record for something, or create rules for when 10 entries received have values greater than X, then something happens, or in a queuing system, someone could be part of multiple queues, while maintaining a separate order for each one.
This would mean that that autonumber field can be used either as an ID assignment, OR a ranking/priority system…or both if you add the field multiple times.
Nice video, easy and informative. Though it’s not exactly what I’m getting at regarding the autocrement, it’s a helpful tool to keep in my back pocket. I use that functionality for a client to schedule food menu items, keeping a history of previous menus that they can reactivate. Thanks!