Generating a unique consecutive or random code of two letters and numbers

[Gaston_Barcia]

In an app I´m creating I need to generate a random or consecutive (it´s the same) code of two letters, and this to be the initial value on a cell. It must be unique.

The user will be able to change it, so the app must check that the code is not being used. This last thing I know how to check, but any ideas are welcome. 

To be specific... I need this kind of values:

AA, FD, Z1, X9, DG, KK, LO

Thanks

[Rafael_ANEIC-PY]

Hello @Gaston_Barcia, how many rows could that table eventually have? because if your code is made of just a code of two letters, that's only 36 characters to choose from, and that's only 630 combinations.

Regarding the generation of such a code, you can use this:

CONCATENATE(
    MID("QWERTYUIOPASDFGHJKLZXCVBNM0123456789",
        RANDBETWEEN(1, 36), 
        1),
    MID("QWERTYUIOPASDFGHJKLZXCVBNM0123456789", 
    RANDBETWEEN(1, 36),
        1)
)

 
How to automatically check if it already exists when a new one is created is beyond me tho.

Since there are so few combinations, you could just as well create the list of all the combinations and make the app select one from the list that has not been previously selected .

[Suvrutt_Gurjar]

There may or may not be an easy solution for your requirement. However could you please elaborate the following so that the community could help better?

A. What you mean by "a random or consecutive (it´s the same)"

B.   Are 7B or 88 acceptable values, or the first value needs to be an alphabet?

[Gaston_Barcia]

Any combination is acceptable. 

I was thinking on consecutive because sometimes it helps in unique verifications. You have to reach the end of the combinations for a possibility to have a duplicate.

[Steve · Accepted solution]

LEFT(UNIQUEID(), 2)

[Gaston_Barcia]

Thanks! but will this include all letters or just HEX?

[Steve]

Easy enough to try it and find out. Or read the docs.

[Gaston_Barcia]

You´re right. 

I´ll use this as a solution. Thanks!

Just to left a note about this:
The only problem about this is that UNIQUEID() generates a random sequence, not an unique one. And the possibilities of getting an unique code are 100% when using 8 positions. But when using only the two first ones there are more chances that codes are repeated. 

A second check for unique code is needed.

[Gaston_Barcia]

I´m using this to check if the code is already used:

In the Valid If of Data Validity I placed this:

NOT(IN([Audienciaunica], SELECT(Audiencia[Audienciaunica], [ID Audiencia] <> [_ThisRow].[ID Audiencia])))

I was thinking if there is a way to check this in the moment of generating the value. My coding skills are not that deep.

[SkrOYC]

Just playing, but worth trying I guess:

INDEX(
  LIST(
    LEFT(UNIQUEID(), 2),
    LEFT(UNIQUEID(), 2),
    LEFT(UNIQUEID(), 2),
    LEFT(UNIQUEID(), 2),
    LEFT(UNIQUEID(), 2),
    LEFT(UNIQUEID(), 2),
    LEFT(UNIQUEID(), 2),
    LEFT(UNIQUEID(), 2),
    LEFT(UNIQUEID(), 2),
    LEFT(UNIQUEID(), 2),
  )-Table[YourTwoCodesID]
), 1

This should create 10 different codes and then substract from that list the ones from your table and then use the first one of the list.

I think that there is already a posibility that the 10 ones made through this method are on the table, that's why this is not recommended for IDs.

BTW, you don't need to have a Key column different than UNIQUEID() or even CONCATENATE(UNIQUEID(), UNIQUEID()). If you need something else to be seen on a report or something, just use other column and don't mess with the key column

[Suvrutt_Gurjar]

Another perspective on these unique IDs just as a food for thought.

It sounds, the probability that an ID will be repeated or not will depend on combinations provided by the ID expression and dataset size ( in rows numbers)  it is used on.

ID	Maximum Combinations possible	Data Set Size
LEFT(UNIQUEID(), 2)	36 *36=1296 (Each character in ID can be a-z,0-9, so 36 combinations)	Even if it is used on a data set of 300 rows, the repeat probability will be 25%
LEFT(UNIQUEID(), 4)	1679616 (1.6 Million) (1296 *1296)	Even if used on a 10K row dataset , the repeat probability will be around 0.5 %
UNIUQEID()	2.82  million million (1.6 million *1.6 million)	Even if used on a data set of 1 million rows, the repeat probability will be 1 in 2.8  millions

In summary, for the ID not to repeat, the UNIQUEID() length will need to be in proportion to the expected dataset row size or vice versa.

RANDBETWEEN(), just being digit based, has obviously much lesser combinations (10 per character)  for the same length. 

Hi@Joseph_Seddik : In one other post we had discussed, if UNIQUEID() in AppSheet uses only hex letters or all alphabets. I have seen in certain apps the UNIQUEID() using all alphabets but majority of the times it uses hex letters. I am unaware when AppSheet brings in additional letters. One guess is, it may be based on subscription plan.  @Steve may have more guiding inputs.

[Joseph_Seddik]

Thank you @Suvrutt_Gurjar !

I haven't seen UNIQUEID() using characters outside the hex range, but it I haven't seen all 🙂

[Steve]

UNIQUEID("UUID")

https://help.appsheet.com/en/articles/2357317-uniqueid