I ran into the issue recently of wanting to document when task rows in my Tasks tables had been created, started, and completed. My Tasks has a select list column to specify the stage of a task similar to Kanban (To Do, Doing, Paused, Done). The goal was to set the Created column to when the task row was created, to set the Started column to when the task stage was first set to either “Doing” or “Paused”, and set the Completed column to when the task was set to “Done”. The Created and Completed columns are fairly straightforward.
If(thisRow.Stage = "Done", thisRow.Stage.Modified(), "")
However, achieving the same for the Started column was not obvious, because using something like: "
If(thisRow.Stage != "To Do" AND thisRow.Stage != "Done", thisRow.Stage.Modified(), "") would mean that the field would get set to Blank as soon as the task stage was set to “Done”.
I tried to find a formula pattern imitating something like a state machine trigger + condition or an event listener, but the way the event listener is implemented in Coda, as
If(thisRow.ObservedColumn = "triggerValue", trueValue, falseValue), forces you to specify a value the column will take if the trigger is hit but the condition isn’t matched e.g. in the example above if
thisRow.ObservedColumn is changed (trigger) but does not match “triggerValue” (the condition). So if the trigger is hit but the condition isn’t matched, there’s no way of saying “do nothing” or "keep the same value in
thisRow.ObservedColumn", instead the binary nature of the
If function forces you to change the value.
So I made a workaround, using a temporary backup of the Started Column, with these formulae:
If(thisRow.Stage != "To Do" AND thisRow.Stage != "Done", thisRow.Stage.Modified(), If(thisRow.Stage = "Done" AND thisRow.[_Started Backup].IsNotBlank(), thisRow.[_Started Backup], ""))
If(thisRow.Started.IsBlank(), _Delay("", 1000), thisRow.Started)
This _Started Backup column now simply mirrors the Started column, but waits 1s (1000ms) before becoming Blank in the case where Started becomes Blank (using the experimental
_DELAY function). This allows it to persist the value of the Started column for 1s after Started is wiped after the task is changed to “Done”. The Started column formula can then “bootstrap” itself back to its original value that is temporarily persisted in the _Started Backup column (only in the case where the task stage is “Done”, otherwise it was moved back to “To Do” so we want to truly reset the Started field for this row).
It seems to work perfectly for my use case, and allows for “If false do nothing”, truly conditional field change listeners/triggers, and field introspection that would otherwise be forbidden by Circular Reference errors.