There are many scenarios where I build applications with data structures which do not require custom identifiers, but I need to ensure that the ID of new records will always be unique and easy to sort through. I know this has been a common request, as the workaround for creating this effect is complex and fragile.
In these scenarios, I currently just use RANDOMSTRING(), but this can be tricky to keep track of. I really have enjoyed that in the Quickbase platform all records have an incremental ID starting at 1.
Obviously, best case scenario would be a feature that allows users to custom define table identifiers to create incremental ids such as LOT-0000001. But I do think that a simpler feature-- could be just a checkbox-- to switch a table to just use an index as ID would be very valuable and easy to use vs. the current system of aggregations.
The only tricky part is that these tables would not be able to have access to the trigger “Create or Load Record”, so the trigger builder would need to detect when one of these is tables is being used and only offer “Create Record” or “Load Record”.
I want to add, that the workaround of finding the highest ID and then creating the next one could cause issues as well. Since there is some logic in between, it takes some time and when two stations start a new record at roughly the same time, they might get a conflict by getting the same next ID.
This would also be solved with a build in solution.
However, I would suggest a slightly different approach.
I’d like something close to the logic behind creating variables.
“Create Record with ID Pattern” #-sample would find the latest ID with “-sample” and creates the next one 17-sample test-## would lead to “test-04” and test-###-sample creates “test-008-sample”
Simply ## counts a two digit number (08,09,10,11…,99,100,…)
This way you can still put in some logic ( and the pattern input should be as an expression editor, where you can place variables as well…)
This way you can also build something like: order nr.-measurement-counter
I agree that there are a lot of improvement opportunities here, but these come with a lot of edge cases and UI to develop. I was suggesting something simpler to start which would go a long way.
In fact, if formula columns ever become a thing then these unique IDs could be automatically calculated from the index.
@danielpomeranz Can you elaborate a bit more what you mean by “easy to sort through”?
@thorsten.langner With your example, can you say more how you would later use those “concatenated” IDs? Are they relevant for human readability? Or for accessing via ID?
How does this compare to a random / incremented ID and having the other information (order number, measurement, counter) as separate data that can individually be accessed / filtered on?
I think just being able to reference record IDs in conversation and being able to easily identify them in order of date created is a huge benefit for developers and users. While gaining the added benefit of guaranteeing uniqueness and even being able to identify when records were deleted.
In my head, the record id counter would never go down, even if records were deleted. So this would provide an easy way to know which records had been admin deleted.
@danielpomeranz I very much agree that an auto-indexing ID would be super useful. It’s very common for Operations folks to discuss Orders, Batches, Unit #'s, Serial Numbers, ect. at the ID level. Doing this level of incrementing is at best difficult, and at worse prone to errors as @thorsten.langner points out.
What about a simple selection right in the trigger? Table Records → Create Record → ID: (Variable, Expression, Index Number)