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
For my purposes, this is okay.
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.
4 Likes
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.
3 Likes
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,"-")
2 Likes
Riffing on the original post, if you create a column called “Random” with formula If(thisRow.[Target Column].IsBlank(), Random() * Power(10, 17), '') and put the formula below into a button, the button will generate standard UUIDs for blank cells in the specified “Table Name” & “Target Column”. You can rotate a UUID by deleting the value and clicking the button. The isBlank check above avoids constantly updating the random number values for all rows on every doc update.
[Table Name].Filter([Target Column].IsBlank())
.FormulaMap(CurrentValue.ModifyRows([Target Column], Join("-",
ToHexadecimal(RoundUp(CurrentValue.Random * 4294967295), 4).ToText().Lower().Left(8),
ToHexadecimal(RoundUp(CurrentValue.Random * 88612), 4).ToText().Lower().Left(4),
ToHexadecimal(RoundUp(CurrentValue.Random * 61228), 4).ToText().Lower().Left(4),
ToHexadecimal(RoundUp(CurrentValue.Random * 42949), 4).ToText().Lower().Left(4),
ToHexadecimal(RoundUp(CurrentValue.Random * 717232843716126), 4).ToText().Lower().Left(12)
)
))
1 Like
I think Coda must’ve changed the way Random() works. If I recall correctly, when I wrote this post in 2020 all invocations of Random() on an update would return the same value.
Indeed, when I ran the original code above within a single button click, all rows ended up having the same UUID. Thus, it appears that the value of random() within the execution of a single action is fixed.
That said, random() produces a unique value for each row in a table, which is what my code leverages above. Note that modifying the document in any way re-randomizes all rows. Because I want the generated UUIDs to be fixed until I decide to rotate them, I use a button click to assign and/or renew the UUID for each row. Also, to avoid churn in the document snapshot, I limit the random() formula execution to those rows which do not yet have a UUID assigned, and the button only fills in those rows.
Good catch regarding rows each having a separate invocation of Random(). I don’t think this was the case back in 2020, as I believe I tried that and ran into some sort of issue.
If you pass “False” in as an argument to Random(), it won’t automatically update. See this doc for an example:
it may be possible to avoid the need for a button if you use the “value for new row” option on the GUID column.
the formula you provide for the “value for new row” is only executed when a new row is added and never at any other time.
a.f.k. right now so have not been able to test this.
but it is a feature we use a lot for initializing table rows.
max
Great suggestion. I tried the Join(...) clause as a default value, but the Random() formula apparently doesn’t run prior to filling in default values, so the UUID ends up being full of zeros. Same if I tack it onto the end of my button action to create new rows via RunActions(). Too bad, as this is going to bite us in the future if we forget to click the “Generate” button.
Thinking about a way to make it run immediately after the row insertion. What we need is a RunFormulas() in between the row creation and the UUID generation: RunActions(AddRow(...), RunFormulas(), GenerateUUID()). Automation could take up to an hour, which is far too long in our case.