I understand that you can trigger automations based on conditional changes to row data, which pretty much satisfies my use case, but I don’t understand the reasoning behind excluding this functionality from formulas.
It seems like having a column named “Result” with formula If(thisRow.WatchedColumn = "Trigger Value", "is trigger value", "is not trigger value") is already a listener, as changes to the “WatchedColumn” column trigger the conditional statement to be evaluated and set the value in the “Result” column accordingly. The only problem this causes is that you are forced to set the value of the “Result” column to the ifFalse value when the listener condition is false. You cannot have a listener that only updates the “Result” column when the condition is true, which is why automations are needed.
It seems to me like automations aim to satisfy a higher-level use case than simply updating one column of a table upon changes to another column to the table, so it seems like an overcomplication. The other downside is that the “Result” column cannot be a formula column anymore and must be a regular column so that the automation can modify its value (which means users can overwrite the value even though it is supposed to be automated).
The requested change would be having either a new formula, or augmenting the existing If formula to allow listening to changes on a specific field (the value in a column for a row) and only evaluating an expression to a value if a certain condition is met.
A new formula could look something like this:
On(observedValue, condition, ifTrue)
or
On(condition, ifTrue)
for example: On(WatchedColumn = "Trigger Value", "is trigger value at " + Now().toText()) would record the latest time at which the “Watched Column” was equal to the “Trigger Value”, and not change if the “WatchedColumn” was changed to a different value. Under the hood this would just set listeners on all variable tokens in the condition (fields, aggregations, etc.), evaluate the conditions on any change to any of the observed data (exactly the same as the If formula), and set the column to “is trigger value at [timestamp]” if the condition was true, but not make any change if the condition is false, despite the data change.
Augmenting the existing If formula could either be:
If(condition, ifTrue)
or
If(condition, ifTrue, ifFalse) but introduce either a _NoChange() special formula that indicates that if the ifFalse branch is evaluated, no changes should be made to the column, or introduce a reserved originalValue variable which would hold the value in the column in which the formula is being evaluated right before the formula was evaluated. Both would have the same effect.
This is actually already possible with a workaround (see here), but it’s convoluted and seems like it may be costly under the hood (definitely more expensive than registering a single listener on a column).
This would be useful anywhere you would want to modify a column’s value when another expression changes to a specific value, but keep the column’s original value if the other expression changes to any other values.