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?
@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
1 Like
FREDY_ORTEGON:
each client has an ID that is the mail
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:
1 Like
Your observation is good. regarding the keys generated with Randbetween, how many digits (advisable) should the upper limit have?
FREDY_ORTEGON:
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:
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.)
1 Like
I have seen duplicates with RandBetween() few times but never with UniqueID(). Matt is not worrying for nothing.
3 Likes
MultiTech_Visions:
I’ve never used RandBetween() for key generation, but if I would it would be something like this:> RandBetween(10000000, 99999999)
FREDY_ORTEGON:
Your observation is good. regarding the keys generated with Randbetween, how many digits (advisable) should the upper limit have?
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.
2 Likes
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
1 Like
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…
1 Like
@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).
3 Likes
I will try this.
1 Like
@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
1 Like
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
1 Like
@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;
1 Like
Yes! It worked without problem. Thanks a lot
1 Like
FREDY_ORTEGON:
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 a couple of posts in the Tips & Tricks section for that
[How to create an automated gSheet backup within your app](https://community.appsheet.com/t/how-to-create-an-automated-gsheet-backup-within-your-app/10473) Tips & Tricks ?
Dear valuable members of the AppSheet Community, I would like to share and explain, how it’s possible to create an automatic backup feature for your app’s back-end. This post will cover the details of a “HOW-TO” and include: Google Sheet Structuring Google Drive Structuring Google Apps Scripting Google Apps Script - Installable Trigger Setting Part 1 | Google Sheet Structuring Step1: Add an extra tab to your back end and name it as you like i.e. Backup, Backup Settings etc. Step2: Struct…
[Executing a DeepLink action from an Email Silently](https://community.appsheet.com/t/executing-a-deeplink-action-from-an-email-silently/10992) Tips & Tricks ?
Hello Everyone, Anyone interested with executing a deeplink action from the inside of an workflow email body without launching the app, can see it in action below. Possible cases might cover subscribe/unsubscribe, approve/reject etc. without the need for the user to interact with the app or with any user set who are not authorized to use the app but users action might be needed. The solution covers: **1. **Use of Google Apps Scripting **2. **Use of AppSheet API For sure, rather than using Ap…
[How to Integrate Google Apps Script & Trigger with an AppSheet App](https://community.appsheet.com/t/how-to-integrate-google-apps-script-trigger-with-an-appsheet-app/11805) Tips & Tricks ?
1 - INTRODUCTION Dear valuable members of the community, This post is my personal long-time promise to @praveen that I’ll write about nearly a year ago but couldn’t be able to keep my promise and spare some time because of our work load as an AppSheet Partner and Developer. Now I have found some time, I decided to write it down. Hope you can find a benefit of this post for your apps and/or projects. Before starting and diving any deeper, I should admit that this requires at least a beginner…
3 Likes
LeventK:
The uniqueness of this own generated ID will be 2^122 ( approximately 5.3×10^36 ).
@LeventK you sir are amazing.