Virtual column value disappears on sync

Dave2 · 05-03-2021 03:38 AM

Hi,

I have a virtual column in a parent table that calculates the average of a percentage column from rows of a related child table. The calculated value displays initially in card and detail views on changes to the child table, but disappears after the sync is complete. I found this topic

which seems to be related, but the solution to “delete the reference” is not an option for me. Is there a way to prevent this behavior?

Thanks!

1minManager

So if its disapeering my guess is that the formula result is empty.

Steve

As indicated by @1minManager, this type of behavior commonly occurs because the virtual column’s value becomes blank as a result of the recalculation that comes with a sync. Make sure the spreadsheet cell’s number format is either Plain text or a format specifically suitable for the data the cell is to contain; do not use the Automatic format choice. Also make sure the spreadsheet cell’s format matches that of the app table column’s. For instance, if the app column allows a decimal point, make sure the spreadsheet cell will preserve the decimal point.

Dave2

@Steve I discovered my initial problem was due to a mis-typed column name, so the column is no longer disappearing. However, due to the way my data is structured I realized I’m not able to derive a simple average from the percentage column the way I’d hoped, which led me to this expression:

SUM(SELECT(Daily Trials[Correct Count], [_THISROW].[Benchmark ID]=[Benchmark ID])) / SUM(SELECT(Daily Trials[Total], [_THISROW].[Benchmark ID]=[Benchmark ID]))

I’m trying to calculate the percentage from the raw number columns in the child table. Unfortunately, the result now always resets to zero after the sync. If I plug either of the SUM(SELECT)s in the the VC app formula by itself it generates a correct result, but putting them together returns 0. What am I missing?

Austin

Recommend making your vc a decimal and seeing what that result gives. Most likely its a datatype that is rounding down.

Dave2

I get the same result, but in decimal form.

I can sum the columns of the selected rows no problem, but when I try to divide the sum of [Correct] by the sum of [Total] it outputs 0, after the background sync. I’m stumped.

Something is happening when it syncs because when I open up the form the vc column has the correct result. And it actually will display the correct result for as long as it takes to sync.

Austin

Try making 2 VCs and then do [SUM_Correct]/[SUM_Total] and see what all 3 outputs do. My next guess is that it is calculating your VC before something in the selects is being calculated. So when you make the save before the sync its manually calculating then when it does sync through it changes. I’ve had similar experiences kinda in reverse where it shows the wrong answer then it shows the correct a few seconds later.

Dave2

I’ll give that a shot. Assuming that’s the case, what’s the solution? Is there a way to specify the order in which expressions are calculated?

Austin

For the most part the solutions are pretty varied. Be worst for performance is calculate the whole formula from no VC’s. The other solutions are try to find different ways to write each calculation. So make VC’s in the child tables and grab those, make VC’s in the parent table and then combine those. It’s a ton of trial and error with the only guaranteed solution being to calculate it without VC’s.

Dave2

Create a real column and put the formula in that, you’re saying?

Austin

The final column can be a VC just everything that is used in the formula would be a real column.

Suvrutt_Gurjar

Could you try the below once?

(SUM(SELECT(Daily Trials[Correct Count], [_THISROW].[Benchmark ID]=[Benchmark ID]))) / (SUM(SELECT(Daily Trials[Total], [_THISROW].[Benchmark ID]=[Benchmark ID])))*1.00

Dave2

@Suvrutt_Gurjar same result.

badoia

Just in case anyone gets here form Google as I got.

I had this problem, fixed it by using decimals, in app sheet logic 8/10=0 and 8.0/10.0 = 0.8 this is the problem.

Steve

https://www.googlecloudcommunity.com/gc/Tips-Tricks/Fix-for-expression-giving-0-or-missing-decimal-p...