UniqueID() expression to avoid text, which is equivalent with the Scientific E notation

[Koichi_Tsuji]

UniqueID() will generate the randam text, mixture of character and number with 8 length, but there is a chance we see the strings like for example; -

9026E101

It is comprised of just number and capital E.  However, there is a risk that this value will be saved as exponental value in case we set the cell format to Automatic. See the below image.

** Using uniqueid(UUID) or Uniqueid(PackedID) could avoid this to happen, but this is not what I request here, as majority of the case we and people uses UniquedID() routinely.

To avoid this to happen, we need to explicitly set the format type for all the cells on the Key column to "Plain text", however, practically speaking, we forget to set the format type and leave it "Automatic" which is set by default.  This is a risk we may see the broken REF connection between the tables, unless we realize this could happen before.

To avoid this to happen, the possible solution is not to let UNIQUEID() to generate such values, i.e. the value with the mixture of number and capital E alone. 

@Steve 

@Aleksi 

@takuya_miyai 

[Steve]

I actually thought Development had done this years ago, before Google even.

[Koichi_Tsuji]

@Steve 

Can you ask and check internally?

[yumei]

What do you know--I thought that was the results of two people testing our app at the same time. After a couple times, I started wondering about AppSheet's usability. 

Just to be clear: Is this what you are saying: We should leave the spreadsheet's ID column blank (other than the "ID" header) and make it "Plain Text"; then in the app's definition, put "UNIQUEID()", and let the app take care of the rest?

[Koichi_Tsuji]

Exactly.　7.46E+13 is wrongly formatted as key value, I simply assume you set this column format type to "automatic".  I reckon this is REF type column, isnt it ? Then you ref connection for this record is broken now. Yes, change the column format type to "Plain text", then this concern could be gone.

[yumei]

Yes to both. That one record (highlighted in the screenshot) displayed a totally wrong connection--no gibberish in the display, though. All the fields, in the UI, displayed properly formatted names in the right place. I only spotted the problems because there were only a handful of records that I was testing with and Bugs Bunny couldn't have been student of some guy who I just enrolled in another course.

[Jonathon]

SUBSTITUTE(
  UNIQUEID(),
  "e",
  MID("0123456789abcdf", RANDBETWEEN(1, 15), 1)
)

[Koichi_Tsuji]

I m not discussing the solutions actually...

This must be natively avoided. (not my intenstion to discuss what sort of the workaround would be avail)

[Koichi_Tsuji]

I stay with UNIQUEID() alone, other than using any possible workaround, such as complex expressions. 

IF you wish to discuss about the any possible workaround, please open up you own post for disucssion, thanks.  (for example, tips and tricks category, as I wont disturb)

[MultiTech]

@pravse I remember this from back in the day....

[Koichi_Tsuji]

@Steve 

Sorry Steve, any news on this?  We checked some of our production apps and we actually observed quite a lots of error because of this. 

[Steve]

@Koichi_Tsuji Asked, awaiting answer.

[Koichi_Tsuji]

@Steve  Thank you Steve, hope someone is Dev will respond. ( but I m not actually not much expecting, as we never get the right answer and response for those critical issues back from Appsheet, sorry Google Devs recently.)