How to create a multi-user unique key across time and place

I’m throwing this out there for some validation. I searched the community for an answer but didn’t quite find what I was looking for, so… wondering if folks can poke a hole in my logic here as I am new to appsheet.

The goal is to allow a truly unique key across time, place and devices. Here’s an expression I came up with, the field in the data source should be marked as a “key”, and “hidden”, and “editable” and with the following “initial value”:

CONCATENATE(SUBSTITUTE([GeoKey], ‘, -’,’-’),’-’,
(HOUR(DATE(NOW()) - “01/01/1970”))*3600 + (MOD(HOUR(NOW() - “01/01/1970”),HOUR(DATE(NOW()) - “01/01/1970”)))*3600 +
MINUTE(NOW() - “01/01/1970”)*60 +
SECOND(NOW() - “01/01/1970”)
,’-’,UniqueID())

This expression also refers to a second column called “GeoKey” which is simply the Here() function.

in plain english, the unique key is thus “lat-long-UnixTimestamp-UniqueID”

This results in values such as:

47.690744-122.294005-1571311932-d60d3a21
47.690851-122.293919-1571312029-ea1f73eb
47.690773-122.293914-1571312184-9ba424c3

So, that’s pretty darn unique if you ask me, but I’m wondering what other folks think? This expression might solve for the “offline device” vs. “unique key” concerns?

Thanks for reading.

3 Likes

Thanks for the interesting post. I’m following to see what discussion develops. Personally, I’ve wondered if “UniqueID()” always produces a unique ID or if the chance of an identical ID being produced is just very, very low.

hi there, even expression of uniqueid() would carry a risk to generate the duplicated values, although chance is quite slim or almost it never gonna happen. unique value should be mechanically created,so they (machine) may generate the same value on the different occasion.
Probably we are talking about how it is likely and possible.
For me, 'Who knows!" (^.^)/

anyway, I used to use expression like yours to generate the so called ‘highly likely unique value’ on the certain app.
As a part of expression, I used simply expression like this

‘number(now())’

Just make mil sec to numbers.

Also I combined uniqueID() expression and contac.

If I believe around 50 people of users of this app might generate a new ‘row’ with the time stamp in term of mil sec world, then I won’t use those expression, but I don’t think so!

2 Likes

Consider this Post:

1 Like

I also noticed that you can string multiple unique ID’s together, so an “initial value” marked as “editable” but “not displayed” and with this calculation:

CONCATENATE(UniqueID(),'-',UniqueID(),'-',UniqueID(),'-',UniqueID())

would result in values like so:

e0404810-f9c1ceba-cd2acd77-bda8b518

Which simulates a UUID. Cheers.

3 Likes

Out of curiosity, I wonder what the applications would be here?

To the best of my understanding, if the row is marked as the Key row, AppSheet theoretically won’t allow a duplicate key anyway. So, even if UniqueID generates a duplicate, won’t it not allow it and just generate another? Or does AppSheet just error out until you fix it? If the former, then the only way you could ever have issues is if you have a table with some 2.8+ trillion entries. If the latter, well, then you guys have extremely valid points.

Perhaps, an update to UniqueID to take in an optional parameter to define a length would be of benefit to people.

very very smart

Thread resurrection.
And… Security filters.