Parents and children in a hierarchy

Ask for Help
1

Hello,

I have a task management use-case that requires a Ragged Hierarchy like the example below, where teams can belong to a department or also directly to the company, skipping a hierarchy level (in the real use case there are 5 levels).

Company
├── Department A
│ └── Team 1
│ └── Team 2
└── Department B
│ └── Team 3
└──── Team 4

Users can be requesters or supporters and can be assigned to any level of this hierarchy.

I modelled the ‘Hierarchy item’ table simply as a name and a parent field, because of the need of supporting a ragged hierarchy.

The task assignment works hierarchically. For example: If a supporter is assigned to Department A, he/she will only receive requests from Team 1 or 2.

So in order to know who can take over a task I have to check if the hierarchy item of the requester is a child of the hierarchy item of the supporter.

You can break this out in 2 topics:

  • Build the relation between parent-children in the ‘hierarchy item’ table: This is quite trivial to do in Coda or Glide with a filter formula, but have not managed to make it work in Noloco. I thought this could be solved with ‘Automatically link these records’ within the Parent field, but have not been able to make it work.

  • Get all children for a specific hierarchy item: Again trivial to achieve in Coda and harder but doable in Glide using a Javascript formula. Noloco formulas are more limited, so is the only option to use a workflow?

Thanks for your help!

Pablo

2

I tried to edit my post, because I think it was not clear what I was asking for, but I guess it’s too late for edits. So let me rephrase the last part.

What I want to achieve can be broken down in 2 topics:

  • Get the children of ‘hierarchy items’ using the ‘Parent’ field. I thought this could be solved with ‘Automatically link these records’ within the ‘Parent’ field, but have not been able to make it work.
  • Get all the children of children of ‘hierarchy items’, down to the lowest hierarchy level.

Can someone help me or point me in the right direction?

Thanks!

Pablo

3

Hi Pablo. I’m not 100% certain I understand your question, but I do something quite similar with my forms (form has questions, questions have options).

I only link one level up in my hierarchy; so option linked to a question, question linked to a form, form linked to a client etc. Keeps it nice and simple. Then I use look-ups to get the values from higher up in the hierarchy, because you can look-up from a look-up (if you get my drift!). That means from the linked record, I can add references to all the above hierarchies. Then can use roll-ups to get values if needed.

I’m not sure I’m explaining this well at all, but just think roll-ups and look-ups :wink:

EDIT - I see you also want to have different relationships, so Team sometimes in Department, sometimes in Company. So then you just need linked fields for both, and can select either/or. Then roll-ups for each. Then formula field to look at the roll-ups to see which has values. Again, probably not well explained, but certainly all doable.