When selecting “Apply on changes to” it triggers even if the value stay the same.
For example, I have a cell (for reference column2) that change text to lowercase ($name.lower()) and other modifications to the text. That cell is triggered by another cell (column1). In my case I need another cell (column3) that is only triggered if the value of that cell is modified.
column1 → column2 → column3
So If I modify the cell in column1 changing just one uppercase to lowercase and that modification don’t actually change the value in the cell column2 (stay the same technically) the cell in column3 is actually triggered.
I have two question regarding:
Shouldn’t be that “Apply on changes” only is activated if the value actually changes? Note: I know, that technically since the cell in the second column is triggered the value “changed” even if it stay the same, but the question remain from a user interface experience
If that’s not the case, and AFAIK, there is no way to check for previous values, because any attempt of using variables in the same cell ends up with circular reference problem. In that regard it would be nice to be able to check the value with a formula before it changes
You are right about the triggering: if column C is triggered by B, which is triggered by A, then a change to A will trigger a recalculation of C even if B hasn’t changed. I am not sure that’s intended, but I can confirm that I see this behavior too.
As for working around it, there is in fact a way to avoid the circular reference problem. There is a PEEK function just for this purpose (an example is linked in the June newsletter).
Using PEEK to work around this update problem is far from obvious, but should be possible. Here’s an example that can help: it’s a way to keep a column (C in the example), which maintains the previous value of another column (B):
column C is a simple trigger formula, triggered by B, with the formula PEEK($B).
column B can be a usual formula or a trigger formula, but in addition to its usual work, it uses$C (it ignores the value, just needs to mention it). In the example, the formula is:
$C
$A.upper()
This ensures that before B is evaluated, because it needs a value of $C, the data engine updates the C column. The C column gets B's current value (using PEEK()) at that time, and only after that does B get its new value.
If you find a way to work around the “unwanted triggering” problem using this, it would be great if you could share the recipe!