Prevent circular dependencies when adding child-parent relationships

Sometimes there’s a need to build an object hierarchy, e.g. tasks with sub-tasks with sub-sub-tasks etc, with indefinite nesting. To implement this, you’ll add a lookup column Parent to your table, pointing to the same table. However, if you don’t filter the options for selecting a parent, you risk running into a circular dependency and select a parent row that is already a descendant.

To prevent this and narrow down parent choices, do the following:

  1. Construct each item’s path:
If(
  thisRow.[Parent item].IsBlank(),
  thisRow,
  ListCombine(thisRow.[Parent item]._Path, thisRow)
)
  1. In Parent lookup column settings, set up the filter to only display options that don’t have this row as an ancestor already:
thisRow.In(CurrentValue._Path).Not()

Now you’ll only be able to choose items that are safe to choose as parents, without running into an infinite loop and freezing Coda into endless calculation.

5 Likes