Differing results from same expression

[dbaum]

I was transitioning a spreadsheet column to be instead a virtual column, and noticed that the same expression yields a different result between the two columns. I also noticed that for the virtual column, the expression’s result changes a few moments after saving the row.

The relevant portions of the expression involve math expressions nested within a TEXT() function. Each math expression divides one number type column by another number type column to obtain a proportion, which is then multiplied by 100 to render as a percentage.

Here’s the expression:

CONCATENATE(
	"Invited: " & TEXT([Invited]), 
  	" | Accepted: " & TEXT([Accepted]) & " (" & TEXT(([Accepted] / [Invited]) * 100) & "%)", 
    " | Declined: " & TEXT([Declined]) & " (" & TEXT(([Declined] / [Invited]) * 100) & "%)"
  )

See in the following screenshots that the percentages are always 0% in the virtual column despite appearing accurate in the spreadsheet column. Also, when I edit and then save a record, the percentages briefly appear accurate in both columns, but then change to 0% in the virtual column (perhaps related to the sync completing?); that changing result in a single column is not depicted in these static screenshots.

As an aside: I realized that I needed to first convert the values from the number type columns to decimal values since I’m calculating a proportion. With that change, the virtual column now calculates reliably. Here’s the corrected expression:

CONCATENATE(
	"Invited: " & TEXT([Invited]), 
  	" | Accepted: " & TEXT([Accepted]) & " (" & TEXT(NUMBER((DECIMAL([Accepted]) / DECIMAL([Invited])) * 100)) & "%)", 
    " | Declined: " & TEXT([Declined]) & " (" & TEXT(NUMBER((DECIMAL([Declined]) / DECIMAL([Invited])) * 100)) & "%)"
  )

So, I think I’ve got the right expression for my app and it’s working as expected. Again, my post is to report that the following behaviors that I observed along the way while getting to the right expression seem like bugs. Nonetheless, as always, I’ll be glad to know if there’s something I’m misunderstanding.

• An identical expression yields different results in a spreadsheet column vs. a virtual column.
• In a virtual column, an expression briefly yields one result that then changes to another result.

[Steve]

Fix for expression giving 0% or missing decimal pa… - Google Cloud Community

[dbaum]

Right–I had figured out that I need the DECIMAL() function. Thanks for pointing me to your handy post–If I had found that in my searching, it would have saved me some troubleshooting time.

As noted in my post, my other questions remain regarding why the same expression’s results appears different in different situations.

[Steve]

Virtual columns are computed locally by the app when in a form view or the row is updated by a direct action (versus an action performed by Automation). Those changes are then synced with the server. During a sync, the server recomputes all virtual columns and sends those recomputed values back to the app. Sometimes, the server may have data the app didn’t (such as data recently synced by another user), which may affect the recomputed virtual columns. In some cases, the server performs the computations differently because the server’s software is a completely separate implementation from that in the app itself, and there are variances in their behaviors. These variances are bugs and should be reported.

[dbaum]

Thanks for the explanation. Reported via Contact Support link from app editor.