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:
IN(“Not Started”,[Related Tasks][Task - Progress]),
IN(“Started”,[Related Tasks][Task - Progress])
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 …