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!

4

Hi, thanks for raising the above question… does the =$id function no contains an option to avoid re-use?
Best
Peter

5

Answering my own question: I just converted a prototype database with 20 tables using UUID() as keys. I did the following.

  1. add a new temporary column, called “temp”
  2. convert primary key column to “data colum” if not already done
  3. add trigger formula UUID and tick the field “Apply on record changes” and choose field “temp”
  4. goto column temp and enter “=1” into the first row - this triggers a record change for all rows and hence an automative update of UUID
  5. delete column temp - nice feature in Grist: the existing trigger point is set to “apply to new records”

PS: Thanks for the great work @dmitry-grist

1 Like
6

Hi, I actually want to reuse the ID because I don’t want them to keep getting bigger and bigger while old entries were deleted. But I don’t see the reuse now. Has that been changed? Any way I can still use it that way?