Miller/Cascading Columns from Graph Table

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

Untitled design (2)

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
  • 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

  1. 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.
  2. Depth: Add a formula column that calculates each node’s depth in the hierarchy
  3. Relation Columns: Add formula columns to calculate children, siblings, and ancestors (calculations below)
  4. ‘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)
  5. Copy Card View: Make a copy of your Graph/Nodes table. Display it in Card view.
  6. 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).
  7. 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
    )
6 Likes

Extremely useful framework indeed, and a very elegant implementation.

And thank you for sharing so generously the formulas involved.

Take a bow!
Most impressive!

Max

(modified to reflect changes by the OP)

1 Like

Heya @Agile_Dynamics - thanks for catching those. Just updated the description with clearer instructions.

The select button was wrapped in a runactions() that I omitted! Whoopsies. Back in now.

Cheers,
Jon

1 Like

Love it!
And came right in time for a case application.
Thanks for sharing.

2 Likes

Hey @Fabien_Hammerer - Would love to see your usecase!