Get all children of an item in a hierarchy

Ask for Help
1

Hey Gliders,

I have a somehow complex app that I want to develop and being a noob in Glide I’m facing some decission paralisis. I was doing quite some reading in the forum and watched all the videos from Robert Petitto that sounded somehow related to my topic but didn’t find a way to build what I want.

Let’s consider the following hierarchy

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

I have modelled this hierarchy simply as a table with rowID, Name and ParentID.

Users and tasks will be assigned to different levels of the hierarchy for different reasons, but at the end what my main problem boils down to is to identify if a hierarchy item is a child of another hierarchy item. So Team 3 is a child of department B and company, but not of department A.

In turn I guess this boils down to creating a helper field in the hierarchy table to display all children (including also the children of the children of the children, etc…). Could you give me a hint on how to achieve this?

Thanks for your help!

Pablo

2

Is there a reason for building the hierarchy all in one table? Would it make more sense to have separate Company, Department, and Team tables, and then link them together using relations? I feel like long term it would be better because it would be easier to manage, you could add columns specific to only company, department, or team, and it would make it easier to build screens since everything wouldn’t be sharing the same data source.

3 Likes
3

Thanks for your answer @Jeff_Hager

The hierarchy itself is not that important other than assigning tasks. I’m not planning to store much more info or build screens on it.

To give more context and simplifying the use-case. There are users requesting tasks at different levels of the hierarchy and others serving them at other levels, and the way to determine to whom should the task be assigned is that the hierarchy item to which the requester is assigned should be a children of server’s.

For example for a task type there are 2 servers, one working for the department A and the other working for the department B. If a requester from Team 1 or 2 creates a task it should go to the server at A, and not at the server at B. Does that make sense?

I’m not sure if splitting the hierarchy in multiple tables would make my problem easier. How would you approach it?

4

I mean your way could work if you stick with a static number of levels in the hierarchy. In that case you would need a Relation linking RowID to ParentID, followed by a Lookup to get an array or RowID’s (This would be an array of second or third level ID’s). Then create a second Relation linking the Lookup array to ParentID, followed by a Lookup to get an array of RowID’s (This would only contain third level ID’s, and only if the array above contain second level IDs). If you need to join lookup arrays together, you can use a Make Array column. Then do whatever you need to do from there. To me that’s just messy and hard to visualize.

Makes much more sense to me to separate each level into separate rows. You can still have your ParentID in each table.

  • Create a relation in the Company table linking RowID to ParentID in the Department table.
  • If you want teams under company, you just need to add a Lookup to retrieve Department RowID’s from the relation, followed by a relation linking that lookup array to the ParentID column in the Team table.
  • Create a relation in the Department table linking RowID to ParentID in the Team table.
  • Add any other columns… Lookup, Joined List, etc. as necessary.

To me, that separation of tables is much easier to visualize and maintain. Plus it future proofs you if you end up expanding the use of each table.

3 Likes
5

Hi @Jeff_Hager,

Thanks for your answer! I now realise that I oversimplified too much my example, because my use-case actually requires a ragged hierarchy: IBM Documentation

That means, that there can be a ‘team 4’ working directly for a company, without being part of any department.

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

that’s why I originally went for the single table approach when I built this use-case in another platform. Following the multiple tables approach I would need to build several relations per table, and my actual use-case has 5-levels, so that would be a lot of realtions… What’s your take on this?

Really appreciate your help!

Pablo

6

I don’t like it that much, but here’s my approach.

image
image1184×390 57.1 KB

From the original ID, Name and Parent ID, I configured a JSON object.

image
image1173×428 50.8 KB

Then do a lookup to retrieve all JSON elements from all rows of that table.

image
image1172×408 52.8 KB

Next, I have a JSON Template to convert that to valid JSON.

image
image1196×407 57.7 KB

Finally, JavaScript to get all children of an ID.

image
image1189×913 106 KB

function getAllChildrenIds(jsonString, parentId) {
    const parsedData = JSON.parse(jsonString);
    const data = parsedData.final;
    parentId = parentId.toString();
    
    function findChildrenIds(id) {
        let childrenIds = [];
        let immediateChildren = data.filter(item => item["Parent ID"] && item["Parent ID"].toString() === id);
        childrenIds = childrenIds.concat(immediateChildren.map(child => child.ID));
        
        for (let child of immediateChildren) {
            childrenIds = childrenIds.concat(findChildrenIds(child.ID.toString()));
        }
        
        return childrenIds;
    }
    
    return findChildrenIds(parentId).join(', ');
}

return getAllChildrenIds(p1, p2)