Count lookup fields of lookup fields until there is non

[nsastef]

Im trying to make a task management system, where task 1 would have multiple subtasks and subtasks would have their own subtasks etc. 

The issue i'm having is im trying to make a function where if a task has no "task parent", then the "Task Tier" would be 0. If the "Task Parent" has a "Task Parent" of its own, to start counting how many steps up the tree it had to go to find no value, and count how many steps it took. 

Any other way that would not force me to have additional tables, or limit the number of Task Tiers, would still be fine 

Thanks in advance

[TheTimeSavingCo]

Try:
1. Make a formula field that helps you calculate the tier of the next child task ("Next Tier" in the gif below)
2. Create a lookup field to display the value of the field from step 1 ("Next Tier (From Parent)")
3. Create a formula field that'll check whether "Next Tier" value matches "Next Tier (From Parent)"
4. Create an automation that'll update the triggering record when the formula field from step 3 indicates that the values don't match

Should do what you want I think!

Link to base

[Menashkes]

The platform does not natively support recursive relationships or direct querying of a hierarchy's depth in a straightforward manner. However, you can achieve your goal by using a combination of linked records and formula field to track the hierarchy level ("Task Tier") of each task based on its relationship to a parent task.

Tier 1 - Formula that checks if there is a parent, if there is it return 1, otherwise, 0.

Tier 2 - Lookup of Tier 1

Tier 3 - Lookup of Tier 2

...

Tier X - Lookup of Tier X-1

Task Tier - sum of Tier 1 + Tier 2 + Tier 3 .... + Tier X

It is not elegant but this is the only way I can think of bypassing Airtable's data structure limitations.

Hope that helps.