Using a Parent - Child hierarchy in Airtable


#1

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.


#2

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


#3

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.


#4

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