Auto-increment column

Ask for Help
#1

In my application I’m using an auto-increment numeric column to generate a permanent URL, so I need the values to be unique, immutable, and permanent. Does Grist have a built-in way to do this or can you suggest how I might implement it?

If I create a column with the formula =$id and then delete one or more rows from the end of the table, the deleted id values get reused when new rows are added, which doesn’t meet my requirement. I need something like SQLite’s AUTOINCREMENT keyword (from the docs: "If the AUTOINCREMENT keyword appears after INTEGER PRIMARY KEY, that changes the automatic ROWID assignment algorithm to prevent the reuse of ROWIDs over the lifetime of the database. In other words, the purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted rows. "
Thanks.

#2

We’ve considered changing $id to avoid reuse, but you are right, currently it does get reused.

One simple option for unique identifiers is a trigger formula producing a UUID; there is a built-in function for that: https://support.getgrist.com/functions/#uuid. Make it into a Trigger Formula to trigger for new records only. Then this ID won’t change. This approach is used in some Grist templates.

If you’d like an incrementing numeric identifier, I suggest adding a helper table with a trigger formula to keeps track of the max ID that was seen. It’s a bit tricky, so here’s an example: Permanent-Incrementing-IDs. You can play around with it, try deleting some rows, then adding more.

#3

Yes, I need a numeric identifier and the technique you used in the example seems perfect. Thanks!