Using a Parent - Child hierarchy in Airtable

[Ed_Goble]

Hello all. First post to the community so fingers crossed!

I’m trying to use a system of nested tags within Airtable. I would like to use a simple Child - Parent table to represent multiple layers of relationships (which works great with the Org Chart block by the way). For example, first table like this:

CHILD / PARENT
UK / Europe
London / UK
Westminster / London
Downing Street / Westminster
etc.

I then might have a diary table where I list places I’ve visited. For example:

DATE / LOCATION
10th May / Downing Street
etc.

I’d then like to be able to filter by when I’ve been in Europe. Clearly Downing Street should count, as you can see from the Parent/Child table. I’ve been trying various lookups / rollups etc. to figure this out and can’t manage it. Any tips much appreciated! Thank you.

[Ivan_Carlson]

CONTINENTS —< COUNTRIES —< CITIES —< BOROUGHS —< STREETS —< VISITS

Having a separate validation / lookup table for the various one-to-many relationships above, and a VISITS table with the Date and Street field, could be one way to set it up to get what you want. You could then have various lookup fields in the VISITS table and group by various fields in the proper “order by”.

[Ed_Goble]

Thanks a lot Ivan.

On reflection I think my ‘locations’ example may be misleading, as addresses are a specific use case that may be handled better in another way. I don’t want to be limited to tagging a particular level in the hierarchy - I’d like to be able to enter a city or a borough in the ‘Street’ field, but still be able to find any visits to Europe.

Let me try and explain another example. This would be a broader unlimited system of nested tags, similar to a folder structure. Say these are my tags, with the indentations representing the hierarchy:

Work
Personal
—Admin
------Bills
------Passport
—Holidays
------France
---------Flights
---------Hotel
------Brazil

I want to tag records with these tags at any level. Say I use the tag “Flights” from the example above - that record should then be visible when I filter for Holidays.

My idea to achieve this was with two tables:

Table TAGS
Columns TAGNAME, and PARENT
Records:
Work, “n/a”
Personal, “n’a”
Admin, Personal
Bills, Admin
Passport, Admin
Holidays, Personal
France, Holidays
Flights, France
Hotel, France
Brazil, Holidays

(As I mentioned, this works really well in the Org Chart block, it will visualise the structure of the tags perfectly)

Then Table DIARY
Columns TEXT, and TAGS (linked to the TagName column in the Tags table)
Example records:
“text entry”, Hotel
“text entry”, France
“text entry”, Work

The challenge is then how to filter the DIARY table by all records tagged to “Holidays”. We know from the tags hierarchy that this should include the first two entries, because “Hotel” sits under “France”, and “France” sits under “Holidays”.

Any more help much appreciated! Best.

[Andy_Lin1]

I haven’t tried this yet, because linking records within the same table has always been odd (to me at least) in Airtable.

But how about using two fields, a lookup and a formula field, that you can then filter on?
Lookup: Grandparents
{Parent} of {Parent}
Formula: Parent + Grandparents (you’d filter on this one)
{Parent} + {Grandparents}

You might need to tweak the formulas for spacing and error handling, but by using these fields you actually benefit from the fact that Airtable doesn’t create a reciprocal entry when you link records within the same table (i.e. you can link a child to a parent record, and not have the child record show up in the Parent field of the parent record).

[Paul_Good]

Look up “Org Chart Airtable”. It is a block you get with the pro plan. But the video explains how this relationship can be re-created within AIrtable.

I would post the link but they said I cannot post links lol.

[joao_melo]

You could create a single table for all the locations independent of the level. In that table, you create a parent field so each record could point to its up level. Then create a path field to hold the whole item hierarchy. This will allow you to filter and order the items using the path field. Also, you could create hierarchies with different depths

The only way I found to fill out that path field based on parent data was through Airtable API with a script. The gif below is an example of a multi-level project tree.

If you found that promising, I wrote a post detailing that API approach and also open-sourced and provided free access to a hobby app to do that.

[Ed_Goble1]

Wow, thanks a lot! Agreed, creating that ‘path’ field was the hardest part. I’m not currently focused on solving that particular problem right now, but it’s great to know this work-around.

[Robert_Moggach1]

It would be great to have some notion of MPTT (Modified Preorder Tree Traversal) in airtable… along with polymorphic record types.