Jump to content

Hierarchical query to find all lowest-level children


Farren
 Share

This topic is 5650 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by Guest
Found a bug
Link to comment
Share on other sites

  • Newbies

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 5650 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.