How can I use a self-referencing table to create an automatic hierarchical naming system?

I’m constantly making tables to organize information into hierarchies. I also want them all to have the same type of information, and to be groupable/etc, so they have to be on the same table. I’ve tried working with cross-tables, but even then I run into formula circular error prevention.

All I want is a sub-sub-item to inherit the ID numbers of both parents in its name, in order, and I can’t for the life of me figure out how to do it.

So if My hierarchy is like this:

  • Item 1
    • Sub-Item 1.1.
    • Sub-Item 1.2.
      * Sub-Sub-Item 1.2.1.
    • Sub-Item 1.3
  • Item 2

Item 1.2.1 to inherit that number in front of the name, so I can see it on linked records.

Thoughts? (I’m sure it’s been done before, but. No dice on the search yet.)

I know that blocks can handle hierarchies now, but I’m too broke to pay for a subscription (yet!!). And I’m not 100% satisfied on how it works anyway, from what I can see. And I guess there’s zapier, but anything useful requires too much edit volume or too complicated to again work on a free account.

Don’t be so sure. Any time you want one record to reference another in the same table, it gets messy very quickly, and there aren’t many people willing to dive into that mess to run tests to see if it can even be done.

Oddly enough, I’m one of those people. :slight_smile:

I was surprised to find that this can be done. However, the deeper you want this hierarchy to go, the messier it gets. More on this later…

Throwing the final ID into the primary field, where it can be seen in linked records, is impossible. Why? Circular references. Because the primary field is what’s used in the trickery to make this ID system work, turning that field into a formula that includes the result of the system itself is circular, and Airtable refuses to allow it (trust me, I’ve tried). The best you can hope for is to have the indexing in an adjacent field.

Here’s what I came up with:

This is actually a variation of the indexing solution I posted to Show and Tell not long ago. {ID (manual)} is what the system should output based on how the items are parented, and {ID (formula)} is the actual result of the system. I have to admit, though, that simply labeling it “formula” in the field header is a gross oversimplification. No single formula will produce that result. To track a max two-level hierarchy (i.e. #.#) requires 9 support fields in this table, all hidden except the final result you see. Each level above that requires another two fields, meaning that this four-level version (#.#.#.#) needs a total of 13 fields. And that’s not counting a “control” table that helps with some rollup and lookup operations.

I won’t have time to detail the setup of this system for a couple days, but I’ll put it on my list, and will likely add it to the Show and Tell category for wider reference.

Actually, IIRC I use the names of the master task and of the subtask in naming subsubtasks in my scheduling framework in Airtable Universe. There’s a lot there that may not be applicable, like all of the code used to generate sub- and subsubtasks based on a template, but take a look at the {TaskName} formula field in the [To Do] table. Again, it might not be applicable, as off the top of my head I don’t recall if I backtrace [ahem] the hierarchy to pick up the names, or if I’m merely extracting them from the previous level.

One thing I do recall is there is a strange bug when using aggregation formulas in rollup fields for self-linked tables. The situation where I found it follows, but it could pop up elsewhere. If you’re doing a lot of manipulation of self-joins, and things keep breaking, make sure you’ve not stumbled into it.


In my [Main]table I have a date field, {Date}. (Clever mnemonic, eh?) The table also contains a self-join I call {Master}. Finally, I have a rollup field, {CalcDate}, that follows the {Master} self-link to reference {Date}.

Instead of an aggregation function, I use an aggregation formula – essentially, a formula entered into the space where you’d ordinarily enter an aggregation function. (They’re not documented but, I am told, are supported.) The formula I use is

IF(
    values,
    DATETIME_PARSE(
        values&''
        ),
    IF(
        {Date},
        {Date}
        )
    )

In other words, if there is a {Master} record, retrieve its {Date}; otherwise, use the record’s own {Date}.

When I first enter the data, everything’s copacetic. However, if I change {Date} in the master record, {CalcDate} for the sub records does not change.

The workaround: I create a {MasterDate} lookup or rollup field that retrieves {Date} from the master record, if one exists, and perform my test against it. {CalcDate} becomes a straight formula field with a simple IF() statement:

IF(
    {MasterDate},
    {MasterDate},
    IF(
        {Date},
        {Date}
        )
    )

I’ve opened a ticket on this with Support — but I can’t imagine it’s very high priority. :wink:


Again, quite likely more than you need to know about something that probably doesn’t affect you — but if you’re getting crafty with self-joins, you may find other instances where such behavior rears its ugly head.