Row ID as Column Field with Prefix

[red-sand-robot]

Is there a way to access the "_id" field for rows created in a Database? Notion has a feature for showing the unique row number (generally starting at 1) with an optional prefix. I tried to create a Function-type column to manually create it (concatenate a text string prefix with _id), but "_id" was not accessible.

[mayneyao]

Make sense. It should be supported in the next version.

[red-sand-robot]

Great! With the _id field accessible, the only other thing would be an alternate numeric display or conversion of the unique ID field to mimic Notion - such that instead of 'my_prefix_' || _id displaying the prefix and the full string of the uuid, it would display my_prefix_001.

i am unsure if that would make more sense as something achieved via scripting by an end-user or as a built-in, but just having access to the _id field alone is better than a majority of similar software.

[mayneyao]

If the data size is not large, you can consider using the last few digits of the _id, for example, "TASK-" || SUBSTR(_id, -4) which will result in a random string like "TASK-9abc".

I guess what you actually want might be an auto-incrementing number, in which case we need to support a new field type.

[red-sand-robot]

Using the last few digits of _id would probably work and is similar to what I'm aiming for.

Auto-incrementing is part of it, yes - but the uuid is related/part of it. From what I can tell in Notion and some other low-coda databases, if a row is deleted, that number is never repeated again, even in the case of having rows such as REQ-1, REQ-2, REQ-3; if one were to delete REQ-3, the next row would populate as REQ-4.

[mayneyao]

The Eidos backend uses SQLite tables, where each table's records have an auto-incrementing rowid that is not reused after deletion, as described in SQLite's rowid documentation. Due to certain low-level design choices, we opted to use UUIDs as unique identifiers. The rowid does not work well with formulas (generated columns), making it impossible to use patterns like "TASK" || rowid in formulas for simple processing. This is determined by SQLite's underlying architecture.
If we provide an auto-incrementing field at the user level, it can abstract away some technical details. At the backend, we can use a trigger to update the auto-incrementing column's value to match the rowid each time a user creates a new record. This is technically feasible. Given the current development progress, exposing _id for formulas is a straightforward approach. Advanced features like auto-incrementing fields will be provided later.