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

Jason_Nordin · ‎Jun 11, 2019

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.

Justin_Barrett · ‎Jun 11, 2019

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. :slightly_smiling_face:

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.

W_Vann_Hall · ‎Jun 11, 2019

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. :winking_face:

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.

Tucker_Webb · ‎Jan 05, 2020

Hi Airtable community!! I am a contractor running a construction business. I am trying to solve the most basic principle of organization (nesting). It seems absolutely ridiculous that I’ve tried so many apps and I’m either limited to a certain number of levels of hiearchy or I cannot create a hierarchy at all. It’s 2019… Anyways, onto my question.

In order to keep things clean, I would like to categorize my process of the following. I have:

Project
Task
Sub task 1 (child of task^)
Sub task 2 (child of Sub task 1^)

I need to create the above hiearchy and I want to view all of these tasks and task children in one table, so I would think that linking all of these items together and than creating one table for viewing and for ENTRY of anything else. What’s very important is that I need to be able to obviously work out of one space instead of jumping around, I want to create entries from one table or view and view from one table or view. This also has to work on my phone so that when I’m out in the field I can quickly input data. Since Tasks are the main components of every project, I’m fine with viewing a list of tasks for one project, but I would like to be able to than click on any task in an expanded view of a cell and than when I click on that task it takes me to it’s related sub tasks.

To summarize, when I am looking at a task I want to only see the related items to that task, I don’t want to see all sub tasks level 1 and all sub tasks level 2. I’m looking for the easiest way to make this hiearchy so that it will work for mobile and I can quickly see all tasks and jump into the related tasks by clicking on the task record in expanded view upon opening a task record. How can I do this? Oh and I also want to avoid double entering things in, like customer name, type whether it’s a task or sub task, etc, I just want to be able to jump in once this system is created and start making tasks and sub tasks in their respective parents and categories.

Hope to get help with this soon as this is seriously stalling my progress on the logistics of my business.

Thanks!

Tucker

Dwayne_Florant · ‎Feb 18, 2020

Hi @Justin_Barrett, did you ever manage to write up your solution? Are you able to share a base for us to learn from?

Justin_Barrett · ‎Feb 18, 2020

Welcome to the community, @Dwayne_Florant! :grinning_face_with_big_eyes: Sorry, but I haven’t written up the details of the hierarchy system yet, but it’s on my (rather long) list. I broke the system out into its own base if you want to mess around in the meantime:

Dwayne_Florant · ‎Feb 18, 2020

Thanks @Justin_Barrett !

This is really helpful already, much appreciated!