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.

@hugheshilton It looks like this bug is back.

Screen Shot 2020-04-20 at 11.27.02 AM

This table was generated using the ‘Start with an idea’ app generator, if it makes a difference.

1 Like

Hmm if the table was generated by the Start with an Idea tool, then it probably is an unrelated bug. I don’t think the data the tool generates goes through the same code path as the actual UNIQUEID function although I could be wrong. Probably @tony would know.

1 Like

I never bothered to remove the following formula:

mid(“abcdefghijklmnopqrstuvwxyz”, randbetween(1, 26), 1) & uniqueid()

Perhaps that was a good thing – just as insurance. As long as there’s a letter at the beginning, the error @GreenFlux pointed our won’t occur.

4 Likes

Hi. @hugheshilton is correct. This is because those unique ids are being generated from Spec, which uses a different method for creating the unique ids. The output of UNIQUEID() should be fixed.

2 Likes

I’m coming into this a little late.

I had noticed quite a while back the issue of scientific notated values in the ID columns. I fixed it by setting the data type of the column to “General Text” in the sheet. This is analogous to defined data types in a database.

Wouldn’t it be a best practice to simply set the data types of the columns in the sheets instead of coding around it?

Am I missing something?

4 Likes

Yes!

3 Likes

Coding around it will ensure the value is stored correctly across all sheets, in other columns where the key is stored as a Ref. I preferred this approach because you only have to apply it in one place.

Whereas, adjusting the column setting would be required in the main sheet and every Ref column, and would require making the same adjustments on every new sheet or Ref that’s added along the way.

Neither approach should be needed now, though. I guess this bug is only in the ‘Start with an idea’ app generator.

1 Like

My understanding is the workaround WAS implemented in the UNIQUEID() function. That’s why its no longer an issue there.

I do understand why the workaround was made. It’s just typically considered bad practice to implement code to PREVENT issues caused outside of the code. Except in circumstances when the problem source cannot be fixed/changed.

I’ll spare you the experiences and explanation. :slight_smile:

2 Likes

That’s correct. The bug only exists in the ‘Start from an idea’ generator now.

I wasn’t suggesting the work-around is still needed. I was just stating that the temporary fix was easier to implement in a single place using the modified formula, rather than modifying every sheet involved. In any case, it’s not required now.

I do see what you were getting at, but this was a temporary work-around to a known bug, not a design approach to be used permanently. :slight_smile:

2 Likes