Problem whit ID

Hello everyone,
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?

0 29 1,650
29 REPLIES 29

LeventK
Participant V

@FREDY_ORTEGON
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.

@FREDY_ORTEGON
Thatโ€™s unfortunately a possible outcome. When 2 users are creating a record, duplicates are possible

MultiTech
Participant V

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.
2X_d_d418f9a863ae4824f0b7013ee99a502365b1e743.png
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:
RandBetween(10000000, 99999999)

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 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โ€ฆ

I added the uniqueid() in the initial value section but I keep getting this error
Can anyone help this noob?

I have nothing filled in, in the sheet formula section

Hi Steve, thank you for your answer. I tried the formula to add uuids to the existing data. For some reason it did not compute.

I tried also to copy paste from an uuid generator, but appsheet saw it as a formula?

Now I just manually typed in letters and numbers for the 120 existing rows.

And set the initial value to uniqueid() for new data. .
This finally worked.

Greetings,
Pros

@FREDY_ORTEGON
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.

@FREDY_ORTEGON
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

@FREDY_ORTEGON
I have realized after I have posted. To make option (A) to work, change the last line of the script to this:

return newId;

2X_1_13da502e1826ce6e2d7ac1ca21a549b2243bf671.png

Yes! It worked without problem. Thanks a lot

I have a couple of posts in the Tips & Tricks section for that


@LeventK you sir are amazing.

I have seen duplicates with RandBetween() few times but never with UniqueID(). Matt is not worrying for nothing.

This is an excellent Post about Keys. Thank you for all your input.

In the documentation we read about UNIQUEID():
The chance of a duplicate ID being generated is virtually zero: just 1 in over 28 trillion.

Does anyone know how this would be with RANDBETWEEN(100000,999999) ?
I never understood how to calculate probability

Hi @Fabian,

You are very right. This is indeed an excellent post thread with very educating inputs from all senior members.

This just about probability calculation part, if I may add a few points. As per my understanding, the probability of UNIQUEID() is based on the calculation as follows-

It is a UNIQUEID() of 8 alphanumeric digits long. Each digit can have 26 alphabets and 10 (0-9) numeric combinations, that is total of 36 combinations for a single digit. So for 8 digits , it will be 36 to the power of 8 which as per my understanding is 2.8 Trillion ( Calculator gives results as 2.8211099 E+12) @Steve may give more insights into this. I might have missed one possible combination, giving result as 2.8 rather than 28, but I believe the calculation basis is as described.

With only numeric digits as in RANDBETWEEN(), the probability calculation is simpler. Since it is only numeric, 2 digits 0-100 will give just 100 possible combinations, for example. In case of RANDBETWEEN(100000,999999) , the probability will be (999999-100000)=899999 or just 0.89 Million , very much lower from that with UNIQUEID()
However RANDBETWEEN(10000000,99999999) will give much better (99999999-10000000)=89999999 or 89 million.

Thank you very much @Suvrutt_Gurjar
That means, one would need to use RANDBETWEEN(1000000000000,9999999999999) to get 8.99 Trillion possible combinations? That would be a little bit more than with UNIQUEID().

Hi @Fabian,

Technically , yes a larger digit base with RANDBETWEEN() will increase the possible combinations. However, there could be certain internal limit with how many maximum digits can be included in RANDBETWEEN() function.

Lynn
Participant V

Hi @Pros_De_Zutter
If you are manually inserting unique Ids using a formula you need to copy paste as values to remove the formula when it is done.

Steve
Participant V

Perhaps useful:

Top Labels in this Space