Recursively follow a relation inside a table

BLUF: how can I recursively navigate relationships in a self-referencing table with an implied but not strictly enforced hierarchy?

I have three tables with relations to each other: Regions, Settlements, and Points of Interest (POIs). One of them follows the One Big Table schema. It lists all the “regions” in my world. There are many types of regions but they all have similar information about them I need to capture, so they’re in one table. But settlements have very different data from the various scopes of regions, so they get their own table. And Points of Interest (POIs) are different still, so they also get their own table.

Here’s what I mean by types of regions:

And here’s part of the Regions table:
Regions in a One Big Table
You can see I have a self-referencing relationship in the table that says whether a row is contained in another row and vice versa. That creates an implied hierarchy but it’s not enforced strictly.

The problem happens when in Worldbuilding sometimes a POI is in a settlement (i.e. a forge, and sometimes it’s just along the road in a region, like a roadside inn:

In the example above, you can se that A Tavern is in the settlement Zarza which is in the Hexweave Valley. But Roadside Inn is not in a city. Its just on a road in the Hexweave Valley, so it has no relationship to a settlement. It’s related directly to an Area in the Regions table. And Loot Tavern is a tavern literally floating in the Astral Sea, so it isn’t in a settlement, an area, a region…none of that. It relates all the way to a Plane of Existence.

I am interested in being able to understand the full context of any POI (if it’s in a settlement, the settlement, if not, whatever region it’s in all the way up the implied hierarchy until it hits World) so that I can do things like provide a rich prompt to the AI asking it to help me flesh out the details of a POI. But the irregular nature of my jumps is stumping me.

I was able to use a formula to get to the region’s table regardless of if there was a hop into the settlements table first:
Combining the first step

But there are so many possible combinations of jumps after that…I just don’t know what to do. What’s the best way to recursively query the “Contained in” relation until I’m not contained in anything?

Coda has great support for recursion!
I’m on mobile right now so a little tricky, but have a look at this experiment, I hope it helps, it should be in playmode so you can look at the formula

Ok I played around with this for a bit, and it does seem to sum recursively! How can I get the actual elements though, and not a value, to be passed. Like, A would have BCD listed in a column?

In other words, I’m trying to avoid having to do something like this:
thisRow.Area.[Contained in]
thisRow.Area.[Contained in].[Contained in]
thisRow.Area.[Contained in].[Contained in].[Contained in]

…until I hit a blank contained on. I want to recursively gather all the contained ins, if that makes sense. and I need them as references to rows, not concatenated text, so I can pull details about each reference.

Ah back to a computer now! Yeah that wasn’t very obvious how to retrieve the rows, sorry about that.
So the formula is:

ListCombine(thisRow.Children, thisRow.Children.Children).Filter(CurrentValue.IsNotBlank())

ListCombine flattens all nested elements to a single list.
The filter is to get rid of blank results.
I added a column to the embed above :slight_smile:

Loving the world building, seems like so much fun!

1 Like

ListCombine is really helpful! Unfortunately the formula stops at 2 recursions deep. For example, I added E as a child of D and it doesn’t flow up as an a child of A, just of B. I wonder if there’'s some kind of for or while loop that could do this. Like, while Children is not empty, go to the Child and and get its child kind of thing?

I can force it like this:ListCombine(thisRow.[Contained in], thisRow.[Contained in].[Contained in], thisRow.[Contained in].[Contained in].[Contained in], thisRow.[Contained in].[Contained in].[Contained in].[Contained in],thisRow.[Contained in].[Contained in].[Contained in].[Contained in].[Contained in]).Filter(CurrentValue.IsNotBlank())

but obviously that’s brittle WRT to the number of levels of contains.

Oh, and yes, worldbuilding a blast!

Ohh sorry again, made a mistake last night, was a hectic day flying :sweat_smile:

ListCombine(thisRow.Children, thisRow.Children.[All Children]).Filter(CurrentValue.IsNotBlank())

Third times the charm :crossed_fingers:

1 Like

Works beautifully! Thank you so much!

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.