Is this difference Between Virtual and Normal Column True? or am I getting it wrong?

[sorin_mihai]

Hy,

So I have a question/observation, if you could please correct or complete me

Is it true that:

• Normal Appsheet column formulas, don’t update their calculations and their results based on updates other then through the Form of their Table.
• Virtual columns do .

What I mean is this:

I have a Table called Objects , a table called Tasks and a Table called Times . Users add Times to Tasks so Tasks are Referenced in the Times Table. And to create Objects you complete Tasks. So Objects have many Tasks, Tasks have many Times.

I have a column in the Tasks Table - Task Progress - that can be Not started - when there are no Times Related to that Task, Started , when there are Times Related to it and Completed , when a User gives it that status via an Action Button.

I want to have a column - Object Progress in the Object Table that does this:

• when an Object is just created, meaning, it has no Tasks Related to it yet - it says “Incomplete”;
• when Tasks have Times, so they are Started, but not all Tasks are Completed - then it says still “Incomplete” ,
• when an Object has all of it’s Related Tasks Completed, than it becomes as well "Completed"

Now I have done so in a Virtual Column and it works perfectly, Using this formula:

IF(
OR(
COUNT([Related Tasks])=0,
OR(
IN(“Not Started”,[Related Tasks][Task - Progress]),
IN(“Started”,[Related Tasks][Task - Progress])
)
),
“Incomplete”,
“Completed”
)

But curiously, if this formula is in a Virtual Column, if I make a new Object (it is now Incomplete), then add a Task to it and then add Times to that Task (still incomplete) and then mark that Task done, the Column, upon sync updates to Completed.
This is not the case for a normal Column. After it takes it’s first value at the creation of an Object - (incomplete) it keeps it, no matter what I do with it’s related Tasks and it Related Times.

So if this is true, then the conclusion is that, for column that need to update based on updates in other Tables then their own (i’m not sure I’m getting this right, please correct me), you will need to work either with a Virtual Column, or a formula in the Spreadsheet Column.

---

So are there any other more elegant ways of solving the above example? Or should it be working but I’ve done something wrong …

Thank you,
Sorin

[Aleksi · Accepted solution]

@sorin_mihai Yes, your assumption is correct. Appformula in normal column is only recalculated when you open & save the record. Virtual column’s appformula is in generally recalculated when you sync the app.

If you want to update a value without virtual column, you can do that with actions or Workflow/Data change. Please check for example an app called EventAction from here www.appsheet.com/portfolio/531778

[sorin_mihai]

And which option would have in your experience, in the given example, the least impact on the sync time or better said, on the load time when navigating inbetween views in the app?

Which option do you recommand in the given example?

• Virtual Column Formula
• Spreadsheet formula
• Workflow Formula

[Grant_Stead]

@Aleksi I thought that appformulas would also recalculate if the row was “manipulated” at all. So like an action changing a value in a trigger column on that record…

[Aleksi]

I would use virtual column in your case because you already have the related virtual list.

[sorin_mihai]

Ok, thank you

[Aleksi]

@Grant_Stead Yes that’s true. It’s like opened and saved in the background:)