Re: Virtual column formula works; same app formula...

ml16 · 01-06-2022 04:30 PM

Hi there, I have a table with a virtual column that flags rows I want to delete later with a formula:
OR((and([AnalystComplete]=“Yes”, [AnalystMatch]<>“NoMatch”, ISNOTBLANK([MatchID]))), ([Std Appears in Match]=“Y”))

Works fine. Realized I wanted to have it write also to the underlying Google sheet so created a new (non-virtual) column with exact same formula but nothing gets written there. If I click “Test” for the app formula it calculates just fine. But nothing in the Google sheet. I checked the things the documentation says to check for: blank rows, filters in Google sheet, errors on sync, etc. Nothing.

Any other ideas of what I am missing?

Fernando_Lopez1

Remember that a formula in a non-virtual column just updates when any data of the row is updated.

ml16

Thanks. But I guess that’s my problem - it isn’t. Users are entering and saving data (e.g. field of “AnalystComplete” and nothing happens in the sheet. Am I misunderstanding what should happen?

Steve

Your expression should be the App formula for the normal (non-virtual) column you’ve added. Alternatively, make it the Initial value expression and enable Reset on edit?.

ml16

Yes, it is in the App formula field for the normal (non-virtual column).

I tried the suggested solution of making it the Initial value and enabling reset on edit - nothing. When I press “test” in the initial value field, it does show up correctly. Just doesn’t put anything in the sheet.

Also tried just making it equal to the virtual column value - nothing there either.

Any other thoughts?

Marc_Dillon

I think you should provide screenshots of exactly what is going on here.

Steve

Go back and reread this comment:

For a normal, non-virtual column, column values are recomputed only when the row is updated by saving it from a form view or making an update to the row using an action. You are using a normal, non-virtual column. To update the value, open the row in a form view and save, or use an action to update the row.

ml16

Yes, thanks. That is what I did. I opened up the row in form view and saved. So then it should compute my flag and put it in the sheet, correct? It doesn’t.

Steve

Your column is of type Enum with a base type of Text. Your App formula expression produces a Yes/No value. I recommend changing the column type to Yes/No rather than Enum.

ml16

Thanks, that is what I had to begin with. I had a Yes/No column with the App formula filled in. That doesn’t work.

Then there was a suggestion to try the formula as initial value and reset on edit. That doesn’t work.

Then there was a suggestion to try an Enum column, so I did. That doesn’t work either.

None of these approaches puts any value in the Google sheet when I edit the row using a form. I’m sure it’s something small I’m missing but I don’t know what it is. I think I give up.

ml16

Ok, here is an attempt:
Here is my column formula. I used to have it in App Formula but I put it in Initial value at Steve’s suggestion; either way the result is the same.

It seems to compute correctly when a row is edited - I get a green check mark that my formula is ok and the red circled row was just edited.

So I would expect to see a “Y” in the underlying Google sheet in this column for the row I just edited, but nothing is there.

Joseph_Seddik

You might want to change the column type to Enum (instead of Yes/No), and explicitly put the values to textual “Yes” and “No”, and see if it gets now reflected in the sheet.

ml16

Thanks, gave that a try but no joy. Again, seems to compute correctly but nothing written to sheet. I guess I give up. Thanks for help.

AGAEMPRESA

I am having a similar problem.

"FACS detalle" (detail of invoices) is a child table where each line of a invoice is listed. The details table is linked to "FACS" (invoices).

There are several possible Base values for VATs 0%,4%,10% and 21% (IVA in Spanish) that are input at "FACS detalle".

"FACs" has several fields to accumulate each base VAT, for 21 % the real field is "Base 21%" it is a Price type field and calculation is "sum(SELECT ([FACS Detalle][Base 21%],[ID FAC] = [_THISROW].[ID FAC]))".

I have setup a virtual List field to list all base values with VAT = 21% --> "Lista 21" with calculation: SELECT ([FACS Detalle][Base 21%],[ID FAC] = [_THISROW].[ID FAC]).

And a virtual Price field "Suma 21 Virtual" with calculation: "SUM(SELECT ([FACS Detalle][Base 21%],[ID FAC] = [_THISROW].[ID FAC]))" that is the same used for the real field.

When editing or adding a Detail Line, every virtual field is updated, but not the real one.

I have been reading above comments for this topic, and none worked for me.

Any Clues?
Thanks in advance

SkrOYC

Base 21%, Lista 21 y Suma 21 Virtual" son todas columnas virtuales que no existen en tu hoja de cálculo, solo en AppSheet