Workaround for "if false do nothing" + simile field change listeners + field instrospection

Showcase Tips and Hacks
1

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.

Created: thisRow.Created()
Completed: 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:

Started: If(thisRow.Stage != "To Do" AND thisRow.Stage != "Done", thisRow.Stage.Modified(), If(thisRow.Stage = "Done" AND thisRow.[_Started Backup].IsNotBlank(), thisRow.[_Started Backup], ""))

_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.

1 Like
2

…or you use the SwitchIf function. There you just have an “ifTrue” action :slight_smile:

3 Likes
3

SwitchIf seems to just be a wrapper for If(condition, ifTrue, Blank), as the optional final value is taken if no condition matches, and this seems to implicitly be Blank if none is provided.

1 Like
4

Update:

These formulae seem to work better:

Started: If(thisRow.Stage = "To Do", "", If(thisRow.[_Started Mirror].IsNotBlank(), thisRow.[_Started Mirror], thisRow.Stage.Modified()))

_Started Mirror: If(thisRow.Started.IsBlank(), _DELAY("", 500), thisRow.Started)

The change to the Started formula ensures the field is reset to Blank if the Stage is set back to “To Do”, e.g. this allows a task to go from “To Do” → “Doing” → “To Do” → “Doing” and have its Started field set to the datetime at which its stage was set to “Doing” the second time. It also ensures the Started field for a row is not changed if the row’s Stage is changed between “Doing”, “Paused” or other stages that correspond to the state where the task is started but not completed, e.g. this allows a task to go from “To Do” → “Doing” → “Paused” → “Doing” and have its Started field set to the datetime at which its stage was first set to “Doing” (i.e. the datetime it was set to “Doing” after being set as “To Do”).

These changes better reflect the behaviour you would expect from such a tracking column.

I also renamed _Started Backup to _Started Mirror as it seemed more fitting (the leading underscore is also a programming convention that indicates a variable, or in this case a column, is internal and should not be accessed by the client of the system, or in this case the table consumer).

Lastly, this workaround does have 4 main drawbacks:

  • the _DELAY function may be removed at any time, and the way timers are implemented internally is somewhat flaky and seems to not be a perfectly isolated system, rather the timer events seem to be queued after the system toasts (“this change has affected views…”) and other internal features.
  • extra row to maintain and deal with in views of this table
  • not robust against race-condition scenarios, especially in cases where the “observed” column (here the Stage column) is changed rapidly (e.g. faster than the delay time)
  • this one is more of a Warning to those who would want to play around with this in their docs, but making changes to/debugging this formula is not obvious and error prone. For example, since the formulas are indeed circularly dependant in the sense that they mirror each other with a delay, it could be that a stale value that was outputted by the previous version of the formula remains captured in these interdependent fields, despite neither of the formulae being able to produce this value at this later time. So this stale value can be difficult to identify or debug, and also difficult to clear without wiping both columns (this happened to me and I ended up adding a check in the _Started Backup column to identify this stale value and reset the field to Blank).