I have an app that keeps track of the clients table, each client has an ID that is the mail, if I open the app simultaneously on 2 devices and at the same time I add the same client with the same mail, the two apps keep that record without importing that it already exists … Can this be prevented from happening?
Have you ensured that both apps in those devices are synced and have the latest app data?
Yes, when you open the apps, the record does not exist in the table. If I keep a record on device A and then save it on device B, the data of device A has not yet been uploaded, so device B realizes that it does not exist and allows you to save the record.
That’s unfortunately a possible outcome. When 2 users are creating a record, duplicates are possible
Do you mean to say that you’re using the email address of your client’s as the key of the record?
What happens when someone makes a typo? Since it’s the key, things are already locked in stone.
You might consider using a different piece of data for the ID of your records, you can read more about what a Key is here:
Your observation is good. regarding the keys generated with Randbetween, how many digits (advisable) should the upper limit have?
First: I would actually advise the use of UNIQUEID() more than the randbetween(), it ensures it works 100% of the time.
For the numbers you use in the randbetween()… you need to ensure that there’s enough digits in the low number that you’ll never have duplicates - then pick a really high number for the high.
I’ve never used RandBetween() for key generation, but if I would it would be something like this:
That would keep the keys restricted to 8 digits while allowing for a lot of options. (I still wouldn’t do this, I worry too much about duplicates.)
I have seen duplicates with RandBetween() few times but never with UniqueID(). Matt is not worrying for nothing.
I should say that RANDBETWEEN() is also not a bullet proof mechanism at all, because though the coincident factor is too small, after a number of iterations it may produce the same number figure at least twice. So I strongly suggest using UNIQUEID() instead.
If I use UPPER(UNIQUEID()), am I more likely to get duplicate or does it not matter?
It just gives you the ID all in uppercase…Doesn’t harm
UNIQUEID() by itself is all you need. I’ve never encountered any problems when using that way, but I have when doing it other ways.
Many months ago I advocated for a new column type: Key. Basically a text, that’s hidden, with an initial value formula of UNIQUEID(). But this way people could just say, make this the key and appsheet would handle it all for you. But I DO see the need/benefit of being able to manually construct your own key, so…
Provided you think that it won’t be safe enough, you can create your own built-in function i.e. GUID() or UUID() in the spreadsheet container from my attached code which creates a 32bit, RFC 4122 version 4 compliant GUID/UUID. The uniqueness of this own generated ID will be 2^122 (approximately 5.3×10^36).
I will try this.
In that PDF, 3 different UDFs are proposed. Each one of them produces a GUID like below. The first 2 strings are RFC 4122 version 4 compliant GUIDs.
If you are eager to learn, you can read about RFC 4122 version 4 compliancy right from here:
A Universally Unique IDentifier (UUID) URN Namespace
Lately version5 is released, based on a SHA-1 hash generated from the URN namespace of UUID which has a collision chance of lower than 1:2^122 but I don’t believe you will have such huge number of records to be able to catch even the collision ratio of version 4
copy the each of the scripts and the B and C work perfectly, A does not work.
I think that many interesting things can be done with google scripts and appsheet. I would like to know more uses of these two tools
I have realized after I have posted. To make option (A) to work, change the last line of the script to this:
Yes! It worked without problem. Thanks a lot
I have a couple of posts in the Tips & Tricks section for that