Virtual column formula works; same app formula in regular column does not

[ml16]

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:

Virtual column formula works; same app formula in regular column does not Questions

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

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