I’ve discovered a cool way to implement Miller/Cascading Columns (like Mac Finder’s column view) from a Graph Table (parent/child nodes). This allows you to click a node (aka row) to reveal its children. You can then click a child to show a grandchild, etc. You can even drag-and-drop a node to move it like a folder (i.e. move it and all its descendants).
What’s Happening
- Every node on the Graph table can be assigned a parent (another node)
- Any node can be selected as a Parent
- Parent nodes can be assigned a parent node (aka grandparent), etc.
- There is no depth limit
- There is a hidden control value called “Selected Node” with a select list of Nodes
- A button on each node can update the control value to this node.
- Each node has additional relationship columns:
- Children, calculated as any node where
parent=thisrow.
- Siblings, calculated as any node where
parent=thisrow.parent
- Ancestors, calculated as
listcombine(parent, parent.Ancestors)
- Depth, calculated as
parent.depth+1
- Children, calculated as any node where
- The Graph table is copied, Filtered to only show t related to the Selected Node (calculations below), displayed in Card view, and Grouped by Parent and Depth.
- Each depth level appears in its own column
- The face of each card only shows a button (calculation below)
- When clicked, updates the value of the Selected Node control value
- The filter automatically updates to show the selected node, its children, its siblings, its ancestors, and its ancestors siblings.
- So clicking a node’s button reveals its children in the next column.
- You can drag and drop nodes between parents to reorganize your hierarchy
How to Build It
- Graph Table Start with a Graph/Nodes table, i.e. a very simple table structure, minimally just with columns name and parent, and every parent is a linked relation to another node (or blank). This allows for infinite hierarchies.
- Depth: Add a formula column that calculates each node’s depth in the hierarchy
- Relation Columns: Add formula columns to calculate children, siblings, and ancestors (calculations below)
- ‘Selected Node’ Control Value: Add a control value dropdown select somewhere in your Doc to track the “Selected Node” (a linked relation to your Nodes/Graph table)
- Copy Card View: Make a copy of your Graph/Nodes table. Display it in Card view.
- Group by Parent + Depth: Set the view to Card Group cards by depth and parent, both grouped on “top” (you’re actually just grouping by depth, and the parent group is serving as a label).
- Filter: Apply a filter to only show the active node, its children, ancestors, and siblings of ancestors
Depth Calculation
If(
thisRow.[parent].isblank(), 0, [parent].depth+1
)
Children Calculation
[Nodes DB].Filter(
Parent.Contains(thisRow)
)
Siblings Calculation
[Nodes DB]
.Filter(
Parent.IsNotBlank() and
Parent.Contains(thisRow.Parent) and
CurrentValue != thisRow
)
Ancestors Calculcation
ListCombine(thisRow.Parent, thisRow.Parent.Ancestors)
.Filter(
CurrentValue.IsNotBlank()
)
.Sort(
true, [Nodes DB].Depth
)
The Select Button
runactions(
// Set the Selected Node control value to the selected row
SetControlValue(
[Selected Node],thisRow
),
// If node is childless, or if same node is selected twice, open it.
If(
thisRow.Children.IsBlank()
or [Selected Node]=thisRow,
OpenRow(thisRow,viewMode: "right"),
""
)
)
The Filter
When you “select” a card, it updates the Active Node control. The filter automatically refreshes to show only the relevant parts of the hierarchy - the selected node, its ancestry, all its ancestors’ siblings, and its children.
// Show active node, its children, ancestors, and siblings of ancestors
// If no node is selected, show all
[Selected Node].IsBlank()
// Show all nodes without parents (i.e. root nodes)
orthisRow.Parent.IsBlank()
//
or thisRow.Contains(
// Selected Node
[Selected Node],
// Siblings of Selected Node
[Selected Node].Siblings,
// Children of Selected Node
[Selected Node].Children,
// Ancestors
[Selected Node].Ancestors,
// Siblings of Ancestors
[Selected Node].Ancestors.FormulaMap(Siblings).ListCombine() // Must use listcombine because the formulaMap() returns nested arrays
)