Allow "circular references" but limit # of recursion levels at run time

[Rey_Crisostomo]

I keep running into this “brick wall” because recursive structures are necessary to many of my bases because many of the data I am managing are naturally hierarchical in nature (XML, JSON, folder/directory paths, org structures, etc).
I understand that endless recursion can kill the platform but I would suggest that the limitation should be done at runtime and detect a maximum # of levels of depth before giving out an error. Otherwise, this tool becomes severely limited for some applications.

Here is a simple example with 1 table “Employee”: https://airtable.com/invite/l?inviteId=invD1UFeDZHdOkhuD&inviteToken=f09215f8c21ddc2de45b656c06b84fd...

The column Manager is a link to the same table (Employee).
The column ManagerPath is a lookup to the Manager link’s Path column.
The Path and ManagerPath columns are currently not correct because I cannot enter the correct formula for Path because of the “circular reference” error.

The formula for the Path column should be: IF(Manager != "", ManagerPath & "->", "") & Name
Given the above formula, the values of Path and ManagerPath would have been:

I think this should be a valid scenario and that the recursion should be allowed (up to a pre-defined limit of levels to prevent endless recursion/loop).

[Paul_Warren]

Recursion is desperately needed!!

[Justin_Barrett]

I came up with a solution for this type of scenario a while ago, though I haven’t yet written up the details of its inner-workings as it’s a bit complex. Perhaps I should bump that up higher in list. In the meantime, here’s the original thread that prompted me to mess around, where you’ll find a link to a sample base that contains the system I built.

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

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. S…

In the example, I used index numbers instead of names, but names would also work.

[Paul_Warren]

Justin, as always, your work is impressive. However, there are a few major downsides to the available work-arounds:

1. The hierarchy cannot be included in the record ID. This is valuable for readability, especially related to the gantt view.

2. The lookups and formulas used are difficult to reproduce and difficult to understand from an outside perspective.

3. Every record has to be linked to another unnecessary table.

The ability to iterate hierarchy, like in your base, should be manageable with a special formula or unique “iterator” field type based on a self-linking field. One or two superfluous fields makes sense, but more than half a dozen combined with an additional table seems mad to me.

That being said, I should look closer at your solution. My first attempt at creating step numbers in a task list uses 50 extra look-up-fields and I already have the “Task” records linked to a “Projects” table. There may be a way to implement your solution to reduce the need for 50 fields and still not add another table… My biggest problem, though, is the readability by future users. I do not want to be maintaining this database forever. Simplicity and usability are the power of Airtable!

[Justin_Barrett]

Thanks for your thoughts, Paul. I fully accept that the solution I put together isn’t ideal, and have hopes that someday a solution can be assembled that doesn’t lean so heavily into the category of “workaround” as it does now.

[Rey_Crisostomo]

@Justin_Barrett thanks for the link to the thread of a possible workaround.
I had a look at your “Hierarchy Demo” base in that thread. I only had a quick look so I haven’t analyzed it all but it does seem a lot more complex than it would have been if recursion was allowed.
Also, I cloned this base and made a simple update just to test it. I added new row with Name of “aaa” and made the Parent point to “Kumquat”. Unfortunately, the resulting L4 column seems wrong:

Shouldn’t it be “1.2.1.1.1”?
Did I miss something?

[Justin_Barrett]

Rey_Crisostomo:

I cloned this base and made a simple update just to test it. I added new row with Name of “aaa” and made the Parent point to “Kumquat”. Unfortunately, the resulting L4 column seems wrong:

That’s because the {L4} formula is the result when there are a maximum of four levels. For a fifth level in the hierarchy, you would need to add yet another formula field, a {Parent L4} lookup, and at least one other supporting field (currently all of the supporting fields are hidden).

As I said above, I accept that this isn’t ideal. Back when I made it, it was largely created just to see if it were possible. It’s probably one of the most complex setups I’ve ever created, but I’m much more aware now that just because it can be made doesn’t mean that it should be used.

[Mary_Pender]

Hey everyone, I too have been looking for airtable to allow us to designate 1 or more columns in a table as enabling recursive formulas – like a simple counter. Like in Excel or Google: column B1 is the counter column and equals B1+A1 . works after enabling Iterative Calculation - set the number of loops to 1 or whatever – put upper limits on the number of loops if necessary.

Thanks, Mary