GUIDs automatically added to new rows

1

I’ve gotten pretty proficient at using Coda, but I’m stumped on this one… Hopefully someone is looking for a good challenge!

I want to have a text column in an existing table that automatically gets filled in with a new GUID each time a row is added. Has anyone figured out how to do something like this?

First, I tried putting this as the value for new rows:

=Join("-",
  ToHexadecimal(RoundUp(Random() * 4294967295), 4),
  ToHexadecimal(RoundUp(Random() * 42949), 4),
  ToHexadecimal(RoundUp(Random() * 42949), 4),
  ToHexadecimal(RoundUp(Random() * 42949), 4),
  ToHexadecimal(RoundUp(Random() * 281474976710655), 4)
)

Unfortunately, Coda appears to use the same value for all invocations of Random() within a single calculation. Furthermore, if you add multiple rows at the same time via a copy-paste, each row ends up with the same value.

I figured out a workaround involving buttons, a table of random numbers, and an automation, but does anyone know of a less kludgy way to do this?

Pre-emptive answers to a few questions:

  • Why do I even need a GUID in Coda? For backwards compatibility with an older system from which my team needs to move data into and out of Coda.
  • I’m aware that the above code does not generate a v4-compliant GUID :wink: For my purposes, this is okay.
2

Hey Matthew, here’s a pseudo-UUID implementation that’s based not on Random but on Coda row UID:

It takes some liberties with reducing entropy (e.g. lowercasing UID characters and leaving out non-base36 characters), but I guess it should be random enough.

3 Likes
3

Piggy-backing on the work that Paul has done (thanks Paul). Here is the formula that produces the same result with hyphens that he has – but in one single function:

Concatenate(thisRow + “”).Lower().RegexReplace("[^0-9a-z]","").Split("").FormulaMap(CurrentValue.ToNumber(36).ToHexadecimal(2)).Concatenate().Right(32).LeftPad(32).Replace(21,0,"-").Replace(17,0,"-").Replace(13,0,"-").Replace(9,0,"-")

We use this as a default value for a text field to help identify rows that are syncing between two systems (using the API) where the other system is based on SQL Server and uses the uniqueidentifier column type.

As a side note, I just don’t get why tools such as Coda and Airtable (and as far as I can tell, Notion) don’t offer a function such as UUID() where this is easily called when a unique identifier is needed. Using the rowId is not a complete solution considering possible use cases of a UUID. Thankfully this function is possible in Coda. Though our attempts weren’t exhaustive, we were not able to create such a function in Airtable.

1 Like
4

Joey this is super useful.

There are problems with copy pasting this formula, so I’m adding it here so future people can easily use it (I think it was just the quotes were of the wrong type)

Concatenate(thisRow + "").Lower().RegexReplace("[^0-9a-z]","").Split("").FormulaMap(
  CurrentValue.ToNumber(36).ToHexadecimal(2)
).Concatenate().Right(32).LeftPad(32).Replace(21,0,"-").Replace(17,0,"-").Replace(13,0,"-").Replace(9,0,"-")
1 Like