Hierarchical query to find all lowest-level children

[Farren]

I have a table of organizations, each with an OrgID and also a ParentID. If an Org has a Parent, the ParenID field is filled in with the OrgID of it's Parent. Thus, I can model an ownership hierarchy.

What I need to be able to do is perform a query that, given an OrgID, will return all of the lowest-level children of that Org. i.e., it needs to follow every possible branch down the tree until it hits the bottom. It then needs to list all of the Org records that it finds from that lowest-level. Not every branch will be the same lenght. i.e., the number of records down to the bottom child will vary on each path.

I assume this will require some scripting, right? Any pointers to docs, old threads, etc. will be helpful. I did some searching here, but didn't find quite what I was looking for. Perhaps I'm searching on the wrong terms.

Thanks!!

-Farren

[Genx]

Hey Farren welcome to the forums,

Maybe it's just me but i'm not exactly understanding what your doing. Could you possibly post a sample file (in zip format) or an image?

Cheers,

~Genx

[Mikhail Edoshin]

You can do it with a field and a custom function. The field "Lowest Descendant ID", unstored text calc:

If( IsEmpty( Child::ID ), ID,

Get Lowest Descendant ID( Count( Child::ID ) ) )

Get Lowest Descendant ID is a custom function:

Get Lowest Descendant ID( number of children )

If( number of children > 1,

Get Lowest Descendant ID( number of children - 1 ) & "¶" )

& GetNthRecord( Child::Lowest Descendant ID, number of children )

This isn't tested exactly as it is, so please check for errors. You can be certain the principle works; I use lots of similar formulas.

Edited June 16, 2006 by Guest
Found a bug

[Farren]

Let me try to explain again:

Imagine an org chart for a rather large company. Now, given any person in the company I want to drill down on the org chart and find all of the bottom-line employees under that person. (A bottom-line employee being anyone who has no other employees under them.) Some of the bottom-line employeees may be directly under the person in question, and others may be many levels down the chart.

If, for example, you started at the very top of this org chart and did this query, you would return every employee at the bottom of every possible path.

Or, in more computer science terms, given any node in the tree, I need to find all of the leaf nodes below that node and return them as a list.

Reference the diagram at http://en.wikipedia.org/wiki/Leaf_node. If I were starting at 12, my query needs to find 9 and 14. If I were starting at 17, my query needs to find 9, 14, and 19. If I were starting at 72, my query needs to find 67 and 76. etc.

-Farren

[Mikhail Edoshin]

This is exactly what the technique does. The Lowest Descendant ID field will contain the list of all leafs below the node; you can then make the list a key in a self-relationship to record ID and use the Go to Related Record script step over this relationship to find these records.

You can see that the field first checks whether the current node is a leaf or not. If it is [ IsEmpty( Child::ID ) ], it returns itself; if it isn't it calls a custom function that works on its child nodes. The function simply gets the same field of every child node. This means that if the child node is not a leaf, it will call the function again. At the end you'll get the list of all the leafs.

[Farren]

Sorry, I misunderstood. I took it to mean that it returned the one leaf node that was the "lowest" from the start node. I'll give it a try.

Thanks!

-Farren