New Bug Encountered: UNIQUEID() changed to scientific notation by Google Sheets' auto-formatting

A few weeks ago I had an app with just one record that was behaving differently than the others. It kept losing references to child records. When I viewed the Google Sheet, the UNIQUEID() value had been auto-formatted in scientific notation, because it just happened to meet the format of [number] E [number], like 421e24.
Screen Shot 2019-12-12 at 11.50.47 AM

I manually changed that one ID, and updated references in child tables. I figured the chances another key meeting that format were low enough that it wouldn’t be an issue. Then it happened again today.

This could be corrected by appending the UNIQUEID() with an &“x”, or some other character. Or you could turn off the default auto-formatting option in the Google Sheet. However, both these fixes require the developer to deploy the work-around in every app.

Since the default Google Sheet settings will interpret AppSheet’s UNIQUEID() improperly, I recommend that this be considered a bug. It will affect many new users who won’t think to deploy either of the fixes mentioned above.

Please consider updating the UNIQUEID() function to always end in a letter, or some other method of avoiding text strings that can be interpreted as scientific notation-- suddenly breaking the app due to the value UNIQUEID() returned.

2 Likes

Great points! I’ll relay this issue to our engineers, I’d also encourage you to repost the above in the Feature Requests / Bug Report sections of the community.

Solution from @Steve

Of course, I think the task that this expression performs (that is, placing a letter from the alphabet before what is generated by uniqueid() ) should be a default feature of the expression – we shouldn’t have to learn this workaround.

2 Likes

I’d also encourage you to repost the above in the Feature Requests / Bug Report sections of the community.

Actually, I thought I had posted to the Bug Report section, because that’s where I started the draft. Turns out, that’s another bug!
https://community.appsheet.com/t/tags-on-the-forum-pages-introducing-this-forum/17155/10

Thanks for pointing this out, @Kirk_Masden!

1 Like

Yes I also saw that it’s deleting the Bug Label automatically.

1 Like

I have received the bug report. I am investigating how best to fix this problem.

3 Likes

A fix for UNIQUEID has been put in place where any values it produces that look like scientific notation will be regenerated. One other case was also found and fixed where values could have leading zeros (such as 00001111) and Google Sheets or Excel would remove the leading zeros in those cases. That case has also been fixed. This fix should roll out early next week.

6 Likes

Great! Thanks!!

Thanks @hugheshilton!

Unfortunately, I’m just finding that I have this issue in my data that was being collected prior to the fix being put in place in December. How should I go about finding correcting those records that were affected?
a) It is easy enough to FIND “E+” in the data. But how to I find where leading zeros were dropped?
b) Once I’ve found the “E+” scientific notation, how to I correct the data to ensure that my cross-reference (foreign-key) records in other tabs in my spreadsheet?
c) Once I’ve found where my IDs have missing zeros, is there a way to know how many missing zeros should be added back?

A UNIQUEID() value is eight characters long, so add enough leading zeros so the value is that long.